Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How come the optimizer uses this approach?

588770Jun 30 2010 — edited Jul 1 2010
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 29 2010
Added on Jun 30 2010
24 comments
4,085 views