>>Results:
>>
>>1. When I added an index on REC_ID only, the execution plan didn't change at all. The cost was 33% from PK Index Scan and 67% from the RID Lookup (Heap) (I presume that the index REC_ID was not used in Execution plan)
>>2. When I added index on REC_ID, ORDER_NO. The same results as above. The new index was not in the execution plan.
>>3. When I added index on ORDER_NO, REC_ID. Execution plan shows the cost of 100% from the added index.
>>
>>So I guess that 3 is the winner.
>
>You have to be careful. OrderNo is the primary key, adding rec_id to it, will slow queries that only need the order_no. I often have a list of records query and then a separate query to get the record data. That separate query only wants the orderNo. Adding the rec_id to the index will affect that.
>
>You could add a new index.
>
But I already have an index key on ORDER_NO itself. So hopefully when the program uses queries that need just the ORDER_NO itself, the SQL will use this index key. Also, in my program almost ALL queries against this table, have to include the
REC_ID <> 'D'
.
But the main thing I am trying to accomplish is to speed up the query that finds the vary last ORDER_NO - this one is used quite often in the program.
I appreciate your input and suggestions.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham