Re: Basic INDEX and SELECT questions

Steve wrote:

> becoming a DBA). If I have a table with fields A, B, and C and it has
> lots of records. Field A is mostly the same value, field B has a mix of
> values and field C is almost unique in every record. Given that, here
> are my questions:
> 1) Is an index that is created in the order A, B, and C better than one
> created in the order C, B, and A?

Almost totally contingent on which of A, B or C is most used in your table accesses. Assuming a vanilla situation (no weird requirements, modern versions of Oracle) it is maybe better to use A,B,C given your data distribution above. That way you can access by a general field - A, then "drill down" with B and C. There is also a "hidden" advantage: this would help to partition the table, should it ever become necessary. But like I said: any deviation from vanilla conditions and you'd have to evaluate each situation on its merits. If I may quote Jonathan Lewis: "TTT" (test!,test!,test!)

> 2) When doing a SELECT, is the WHERE clause better being "A=x AND B=y
> AND C=z" or "C=z AND B=y AND A=x", or no difference?

No difference. Assuming of course you are running a reasonably up-to-date version of Oracle and you are not using some weird and wonderful SQL extension.

> 3) If I had an index on that table of "A,B" and an index just on field
> "A", how does the system know which index to use and would having these
> 2 indexes actually hurt?

It could hurt. An index on "A,B" and another on "A" would probably result in confusing the optimizer: the first index can be used in just about every situation the second one would be. Not to go into unnecessary disk space use issues either. This is BTW a common occurrence in Peoplesoft implementations and can be a killer.

Now, if you had an index on "A,B" and another on "B", *that* could potentially be more useful.

Certainties? There are none when it comes to indexing in a RDBMS: you have to look at each case on its merits and surrounding dependencies.
Indexing is the life and blood of a relational database: there are no "silver bullets" or all-purpose settings. Received on Thu Sep 08 2005 - 00:21:50 CDT