Hello forum,
I have this simple query (this is oracle 10g):
SELECT * FROM zfieligiblercvbl WHERE mandt = :a0 and bukrs = :a1
There are (among other) the following indexes:
UNIQUE Index ZFIELIGIBLERCVBL~0 MANDT 1 BUKRS 8 BELNR 106.478 GJAHR 7 BUZEI 4
NONUNIQUE Index ZFIELIGIBLERCVBLZ2 Column Name #Distinct DAT_OFFER 390 BUKRS 8 FLG_OFFER 2
I expect that the primary key would be used, but strangely index Z2 is used:
SELECT STATEMENT ( Estimated Costs = 519 , Estimated #Rows = 25.841 ) 5 2 TABLE ACCESS BY INDEX ROWID ZFIELIGIBLERCVBL ( Estim. Costs = 518 , Estim. #Rows = 25.841 ) Estim. CPU-Costs = 7.715.825 Estim. IO-Costs = 517 1 INDEX SKIP SCAN ZFIELIGIBLERCVBLZ2 ( Estim. Costs = 79 , Estim. #Rows = 25.841 ) Search Columns: 1 Estim. CPU-Costs = 1.591.961 Estim. IO-Costs = 78
(this is too getting long; more details will come below)