Re: what made the difference?

Eugene wrote:
> Hi all
> I have the table with the following data (sample):
>
> 81 123 1
> 81 123 2
> 81 123 3
> ....
> 81 124 1
> 81 124 2
> 81 124 3
> 81 124 4
> ....
>
> So, first column is always the same, second column is sequence number
> and third column is sub-sequence number. There are multiple
> sub-sequences for the same sequence(always).
>
> We had an index as (col1,col2,col3) and performance was poor.
> After we changed the same index to (col2,col3,col1) performance
problem
> disappeared (actually it became much faster).
>
> My question is: Why changing column order of an index made such
> difference. What is it about changing the order of index columns that
> make a particular index perform better?
>
> Thanks,
> Eugene

Let me pose a question to you:

You have a phone book, and this particular phone book lists no other last name except Jones. There are 36,000 entries in the phone book, all starting with the last name. How easy is it to find Bob Jones, Randall Jones, Euphemia Jones or Jasmine Jones? This would be fairly difficult, as you'd need to search through a number of pages to find any particular name. Since all entries have the last name of Jones, would it not be easier to publish the Jones Phone Book, exclude the last name (as it's on the cover) then index the entries by first name? Essentially this is what you've accomplished with your index restructuring. You've replaced a constant value which every record contains (absolutely the lowest cardinality you can have) and replaced it with a value only few records contain (increasing your cardinality drastically). Now, instead of performing a full table scan (as, I expect, your previous index generated) the optimiser is using an index scan because it actually returns useful information. Indexing a value every row in a table contains is useless, as it provides no purpose except to consume storage. I don't know why you have table data with a constant column; in my opinion that column has no worth, and should be discarded.

All of this is speculation, as you've not provided any data, scripts, etc. to enlighten the masses with respect to your query performance. Provide such (the query, the explain plans) and we'll see if my assessment is correct.

David Fitzjarrell Received on Fri May 06 2005 - 13:12:09 CDT