Hi,

I have Three columns in a composite index named rec_ctry_code,airbill_nimber,awb_seq on three tables in the order
(rec_ctry_code,airbill_number,awb_seq).

The distinct rows on each columns are as below
awb_split:rec_ctry_code=7
awbprof:rec_ctry_code=7
awb_app:rec_ctry_code=7

awb_split:airbill_number=739326
awbprof:airbill_number=736641
awb_app:airbill_number=736610


awb_split:awb_seq=4
awbprof:awb_seq=4
awb_app:awb_seq=4


Below is the query..

select awb_split.pieces_scanned, awbprof.*, awb_app.*
from awb_split, awbprof, awb_app
where awb_split.rec_ctry_code="NZ" and
awb_split.airbill_number = 4563123552 and awb_split.awb_seq
= 1
and awbprof.rec_ctry_code = awb_split.rec_ctry_code and
awbprof.airbill_number = awb_split.airbill_number and
awbprof.awb_seq = awb_split.awb_seq and
awb_app.rec_ctry_code = awb_split.rec_ctry_code and
awb_app.airbill_number = awb_split.airbill_number and
awb_app.awb_seq = awb_split.awb_seq
~

1) When i update statistics high on the column heading the indexes(rec_ctry_code) on three tables. The cost is around 8460. When i Update high on awb_split,awb_app the cost is 5278.

When i reverse the Order of the indexes that is airbill_number,awb_seq,rec_ctry_code and update statistics high on airbill_number the cost is much higher.


From this try outs, i come to conclude that the column with less distinct number of rows in a composite index should come first. Is it right ?

What would be the best order of columns in a composite index to yield a good cost estimate.

bye.