Using an index for sort order in a query

[JaxtheDog]

Hello, I have a table with two indexes one on load_nbr (indexed as shipment_pk which is the primary key) and one on status code (Status_code_indx - non-unique). My query looks for all loads above a certain value where the status code is within a range. I would like the set returned in order of the shipment_pk primary key (load_nbr). Currently it returns in order of the status code index. I want to avoid using 'order by' because it takes 10 seconds where the query takes miliseconds without it (note: the returned set has thousands of records). Here is and example of the current query:

Select * from shipments where load_nbr>='KT-1234' and status_code>'S' and status_code<'Y'

Is there any expression I can use to return it by shipment_pk (use index(shipment_pk) for example)? I have tried various syntaxes with no luck. I'd appreciate any help you could offer!!

Thank You
Jax

 

[AOLEURO]

Hi Jax

If you want to use a index as a sorting and you (of cause) want to use the same index in the where clause, then you need to make sure that all keys are present in the index and in correct order.

You can't use Index shipment_pk as a sorting index and Status_code_indx as index in where clause.

You need to make one index - and this index must have all important values. So it is not directly possible to use Primary Key as sorting, because Status is the first key.

Now I do not know your situation (the use of the table) so I can't give a full answer of the whole true. But maybe you should change the index Status_code_indx to also have the sort order keys and not just Status column. You still need to use the ORDER BY, but Oracle finds out that the same index is used on both where clause and order by, so it is ok.

You must understand that if you change anything on the table (indexes and so on) then performance on DML is also changed. I can't see if it is important, but it is your job.

If a query of 10 sec. is a long time, I assume that you are using this query many times and with many sessions (users). Then I would try to change the index.

Try to use EXPLAIN PLAN to see how Oracle optimizer is working. And maybe you need to use a hint if not ok.

So..:

1. Change index to look like ORDER BY (Status column first)
2. Make statistics (analyze) the table - execute dbms_stats.gather_table_stats('SCHEMA','TABLE');
3. Use explain plan to control how Oracle optimizer is working.


If you want my help - then you need to give me:

1. make DESC TABLE_NAME;
2. Count of rows on table
3. Count of rows on table in the mention query
4. Desc indexes on the table


Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[JaxtheDog]

Thank you for your prompt reply. I understand the issue and have created the new index in my test system. It worked as you described.

Regards,
Jax