SELECT /*+ cardinality (t1 100) */ MPC1.METERPOINTID,
MPC1.METERPOINTCOUNTERNR, MPC1.DEBFOUNDING
FROM
METERPOINTCOUNTERS MPC1, TABLE(:B1 ) T1 WHERE (MPC1.METERPOINTID =
T1.COLUMN_VALUE)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.03 0 284 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.03 0 284 0 13
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 89
Rows Row Source Operation
------- ---------------------------------------------------
13 HASH JOIN (cr=284 pr=0 pw=0 time=0 us cost=133 size=1100 card=100)
13 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=0 us)
100000 TABLE ACCESS FULL METERPOINTCOUNTERS (cr=284 pr=0 pw=0 time=0 us cost=103 size=900000 card=100000)
Table's primary key is MeterPointId, MeterPointCounterNr. Table(:B1) is an array of numbers which I send in to the procedure, it contains 13 entries in this case.
Is a full table scan really the best option here? Wouldn't an index seek be better? The cardinality hint allowed an index to be used in a different query but this one and others still insist in using the full scan.