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

Impossible to tell for sure without seeing the query. And you have left out version number, whether you collected stats for the optimizer, and an explain plan.

I could take a shot at what the problem is and be 99.5% likely to be correct but I don't like guessing so repost with the explain plan and the other missing information.

That said: Personally I would do two things. First remove the first column from any index as indexing it is meaningless. But second, if it is always the same, then it serves no purpose being stored in the table so I'd remove the column from the table.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)