Descending index gotcha

Sometime things just leap out at you when you least expect it.

Let’s say I’ve got a table that I’d like to shrink the space on. No problems there.


SQL> drop table T purge;

Table dropped.

 

SQL> create table T as
  2  select rownum x from dual
  3  connect by level  create index IX on T ( x );

Index created.

 

SQL> alter table T enable row movement;

Table altered.

 

SQL> alter table T shrink space;

Table altered.

So far so good. But then I decide that I’d like to scan this table in descending order through the index column. So I recreate my index as DESCENDING.



SQL> drop index IX;

Index dropped.

 

SQL> create index IX on T ( x desc);

Index created.

 

SQL> alter table T shrink space;

alter table T shrink space

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified for this object

So what happened ? Well, a descending index is implemented as function based index, and shrink space on tables that have function based indexes is prohibited.

As with all things, prudent testing to find the boundary cases in Oracle is important.

Post conference weather

Fellow Ace Debra Lilley stayed a day with us after the conference, and in true local style, we wanted to show off our wonderful Perth spring weather.

It didn’t really pan out as we expected Smile  (Debra is in the middle, basking in the sunshine…)

 

Img_0530

AUSOUG conference–day 2

I didn’t get to see as much content as I liked on day 2, because I had still plenty of slides to complete on my own presentation, so I locked myself away in a room at the casino (the conference venue) and worked on content and delivery.

However, once I was happy with it, I sat on a panel about public speaking which actually went quite well – some good audience participation.

After that, I went and saw Graham’s exadata Live presentation, which always blows me away.  Loading, analyzing, querying a terabyte data warehouse in 30 mins is amazing.

IMG_0527

AUSOUG conference–day 1

The AUSOUG conference program this year was bolstered by a number of international speakers on the tail end of the OTN series of talks held all over the world.  As such, speakers such as Debra Lilley, Graham Wood, Kai Yu, Chris Muir, Tim Hall and many others added some amazing content to the already impressive local content.

I watched Debra speak for the first time – coinciding with my first talk on a Fusion topic Smile

After that, I watched Ray Tindall do a talk on Weblogic tips and tricks.  It was a relief to see someone else talk about all the trials, tribulations, patch levels, certification issues, etc etc with getting the version and configuration “just right”.

Yury from the Sydney user group gave a simple but excellent talk on RMAN backups – not a “how to”, but more of a “things you need to consider” with your backup scripts.  Plenty of useful things in there to improve the robustness of your backup regime.

Then it was Kai’s clusterware talk, and finished up with Graham’s hidden features.  Both a nice insight.

A few beers, and a few drinks to round off the day, then home to work on my own presentation!

Careful in 11.2 with dbms_utility

Its common to have an instrumentation infrastructure where you might be calling DBMS_UTILITY.FORMAT_CALL_STACK to record where in the code path you may be..

But this can bite you in 11.2

A simple demo of how much slower it has become is shown below. Relevant parameters are below:


NAME                                     VALUE
---------------------------------------- -------------
plsql_ccflags
plsql_code_type                          INTERPRETED
plsql_debug                              FALSE
plsql_optimize_level                     2
plsql_v2_compatibility                   FALSE
plsql_warnings                           DISABLE:ALL

SQL> connect scott/tiger@db11107
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

SQL> connect scott/tiger@db11202
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31

Oracle Certification

Is doing the OCP exams worthwhile ?
Yes, but not for the reasons you may be thinking. The OCP exams are a relatively cheap way of identifying possible weaknesses in your knowledge base on Oracle. For example, when I did the OCP, all of the database sites I had worked on did not use MTS and thus the exams revealed an area that could be "swotted up" on.

What does an OCP mean ?
Not much really. The exams could be passed with mediocre understanding of Oracle and some "cramming". (I would claim not be one of these people but you can make your own judgements from this site). Any employer that takes on a DBA purely on the basis of their OCP status probably will (and deserves to) get burnt

A good DBA will pass the OCP, but someone who passes the OCP is not necessarily a good DBA.
A true story…Question posted to a newsgroup:

  "What does ROWNUM mean?
   Signed XXX
   OCP 7, 8, 8i"

Are there any alternatives ?

I heartily recommend downloading the sample exams for the reason mentioned above. You can use them to identify areas upon which to improve. Unfortunately the number of employers who seem to insist upon OCP (and moreover think that OCP is the answer to all of their problems) seems to be on the rise

 

To Oracle’s credit, there has been a recent survey floating around trying to get a better handle on DBA’s do on a “day to day” basis.  Presumably this will lead to better examination material.

Openworld – Tuesday / Wednesday

Tuesday was dedicated to attending sessions.  Most were good, but there is that unfortunate thing when speakers are employees of Oracle.  You can tell by the occasional snippet of information that they are very sharp and very passionate about what they want to talk about, but that’s not necessarily what they’ve been given on the slides to present!

So sometimes these talks come across as diluted in their content, which is a shame.

Sting did a great job with his set, and the organisation of buses etc was amazingly efficient. I didn’t stay around for Tom Petty because it was getting pretty cold out there on treasure island.

 

Last day today….and then its back home to normality 🙂

Openworld – Thursday

Spectacular organisation by the Ace Director team (Lillian and Vikki) meant that my exit from San Fran went just as smoothly as my entrance.  A fantastic week.

So – its all about “c” now, Cloud Cloud Cloud.

On the last day, I saw some great sessions on security and migrating to Exadata.  The main hassle on the last day was the extreme cold in some of the halls.  Someone had (ala Spinal Tap) managed to turn all the knobs up to 11.  I felt a little bad because I had to leave one talk early – not because the content was bad (it was great), but it was just so damn cold!

Also a highlight of the week was a fantastic meal at Fiior D’Italia (www.fior.com) with Oracle friends Melanie and Tom.  If you are ever in San Francisco and looking for a great meal, make sure you give this place a go.

Oracle Openworld – Monday

Welll, today was a bit of a selfish day.  Didn’t really want to spend time on sessions etc, because I had my own session to give, and seeing as its been 7 years since I’ve given a talk at OOW, I wanted it to be “just right”.  So this morning was spent pretty much in the hotel room, doing rehearsals and timings, and then more rehearsals and timings…

But I think it paid off because

a) a good crowd turned up

b) they seemed to enjoy it

c) my timing was spot on, I finished with about 60 seconds left on the block

It was a nice feeling to have several people come up to after the presentation and take the time to say “Thanks”.

 

Other than that, I played “Oracle Tourist” and took the obligatory photos of the America’s Cup, the Oracle Boat and the like…Tomorrow is back to being a normal conference attendee, so time to plan out some sessions

 

amer_cup

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!