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
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.