Re: Index Not Used with Parameterized Query

For the parameterized version I see this-

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          3           0
Fetch        1      0.00       0.00          0         14          0          17


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.00       0.00          0         14          3          17

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT UNIQUE 
      0   HASH JOIN 
      0    TABLE ACCESS BY INDEX ROWID I
     13     INDEX RANGE SCAN (object id 17309)
      0    TABLE ACCESS FULL E

for the non-parameterized version I see this-

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.00       0.00         33         69          0          17


------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.00       0.00         33         69          0          17

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows Row Source Operation

-------  ---------------------------------------------------
     17  SORT UNIQUE 
     49   NESTED LOOPS 
     13    TABLE ACCESS BY INDEX ROWID I
     13     INDEX RANGE SCAN (object id 17309)
     49    TABLE ACCESS BY INDEX ROWID E
     61     INDEX RANGE SCAN (object id 48414)

Biff Gaut
Gaithersburg, MD Received on Tue Dec 30 2003 - 15:15:56 CST