Hi list, for me it's a mystery, but maybe somebody here can bring light into it. It's about cost calculation for two indexes in database version 11.2.0.3.0 (no interim patches) on Linux x86_64. Changed parameters (all time, not only during this issue): optimizer_index_cost_adj = 50 statistics_level = all We have a dynamic SQL (don't scold me for not using binds, I'm the DBA, not the app designer): SELECT auftragId, clientId, kundeId, arsAnrufNr, arsAuftragsNr, arsQueueId, apothekenIdf, dategAuftragsNr, auftragsTyp, auftragSammelTyp, auftragSammelTourId, knzAuftragSammelnErlaubt, knzDispo, knzBTM, knzDefektmelden, knzRueckRuf, knzHeuteKeinAuftrag, knzZustellungHeute, knzZustellungMorgen, knzSelbstabholer, knzDienst, auftragsErfassungsTyp, lieferDat, startErfassung, endeErfassung, knzKontrolle, knzChargenKontrolle, knzPackungOk, nettoAuftragsWert, mehrwertSteuer, valutaDat, textLieferschein, hinweisText, status, extAuftragsNr, extKundenNr, extPruefziffer, reserved, finDat, finInfo, genDat, genInfo, modDat, modInfo FROM OPS$SCM.Auftrag WHERE arsAuftragsNr = '000327' AND substr(geninfo,1,3) = '000237' AND trunc(gendat) > trunc(sysdate-1 ) ORDER BY gendat; The optimizer resolves the access to table AUFTRAG via index range scan, and table access by index rowid. We have two nonunique indexes to choose from: IDX2_AUFTRAG (columns arsAuftragsNr and dategAuftragsNr) (BLevel 2, 21180 leaf blocks, clustering factor 5395080) IDX7_AUFTRAG (column arsAuftragsNr) (Blevel 2, 16870 leaf blocks, clustering factor 4221470) The Optimizer chooses IDX2_AUFTRAG for the index range scan, but I can't explain why. The 10053 trace tells: ======10053========== Access Path: index (RangeScan) Index: IDX2_AUFTRAG resc_io: 186.00 resc_cpu: 1585830 ix_sel: 0.000034 ix_sel_with_filters: 0.000034 Cost: 94.49 Resp: 94.49 Degree: 1 Access Path: index (AllEqRange) Index: IDX7_AUFTRAG resc_io: 1027.00 resc_cpu: 8999338 ix_sel: 0.000241 ix_sel_with_filters: 0.000241 Cost: 521.97 Resp: 521.97 Degree: 1 ... ... Best:: AccessPath: IndexRange Index: IDX2_AUFTRAG Cost: 94.49 Degree: 1 Resp: 94.49 Card: 0.00 Bytes: 0 ======10053========== But why is the cost of a AllEqRange on a single column index (IDX7_AUFTRAG) 521.97 and an index range scan on a multi-column index (IDX2_AUFTRAG) has only cost 94.49? By all I know IDX7_AUFTRAG should be the better choice. How can I find out why cost is calculated this way? If oracle-l can handle attachments, you will find the full 10053 trace including an execution plan with predactes, a "select * from user_indexes" for the two segments mentioned above, and an "opatch -lsinventory" version information with this mail. Again for clarification: I am not interested in a solution for this very case, my priority is to understand what I'm missing here. Thanks a lot in advance Martin Klier -- Usn's IT Blog for Linux and Oracle http://www.usn-it.de -- //www.freelists.org/webpage/oracle-l