Indexes not being used when using bind variables

[skaushik]

Hi all,

I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the use of bind variables totally discards the usage of indexes and the explain plan also does not show the use of indexes.

Any idea how the optimizer behaves when using bind variables? It would be great if someone can help me out in making sure that the indexes are always used even in the case of using bind variables.

Thanks.

[Dave44]

Hi all,

I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the use of bind variables totally discards the usage of indexes and the explain plan also does not show the use of indexes.

Any idea how the optimizer behaves when using bind variables? It would be great if someone can help me out in making sure that the indexes are always used even in the case of using bind variables.

Thanks.

How is the data being used? How many rows in the table? In general if >= 20% of rows in a table are going to be needed it will be faster for a full table scan than an index search. If your stats on the table are refreshed often then the optimizer will make the best possible decisions.

string bind variables behave differently than number bind variables as far as hard parses are conerned. If you are concerned about bind variable usage in a query try replacing the binds with actual values and see if the plan changes much.

Indexes do not always mean faster queries, it depends on the table and amount of data to be used within the table.

[debasisdas]

Please find a related discussion here .

[skaushik]

I have given the query details below. The oracle version used in Oracle 9.2. The details about this query are that HISTORY_VIEW_NO W is a view and it is very large. The columns begin_inst and end_inst have indexes on them.

Using bind variables in the query and very slow execution:
[code=oracle]

SELECT *
FROM HISTORY_VIEW_NO W
WHERE begin_inst <= :endTime
AND end_inst >= :beginTime
AND family = 'ABC';

Explain plan:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 3 3396
HASH JOIN 3 441 3396
HASH JOIN 6 444 103
TABLE ACCESS BY INDEX ROWID USER1.TABLE1 4 180 4
INDEX RANGE SCAN USER1.ENT_IDX1 4 2
VIEW USER1.MNT_TABLE 1 1 K 52 K 99
UNION-ALL
HASH JOIN OUTER 630 25 K 6
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
TABLE ACCESS FULL USER1.TABLE2 507 14 K 1
HASH JOIN 107 2 K 44
VIEW 107 1 K 39
SORT UNIQUE 107 1 K 39
INDEX FAST FULL SCAN USER1.TABLE2_U0 1 507 5 K 1
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
HASH JOIN 503 16 K 45
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
VIEW 503 10 K 40
SORT UNIQUE 503 14 K 40
TABLE ACCESS FULL USER1.TABLE2 503 14 K 1
TABLE ACCESS FULL USER1.TABLE1 629 11 K 4
VIEW 4 K 304 K 3292
UNION-ALL
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_T ABLE 2 K 101 K 1643
INDEX RANGE SCAN USER1.OEE_H_BEG IN_IDX 7 K 25
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_T ABLE 2 K 74 K 1643
INDEX RANGE SCAN USER1.OEE_H_BEG IN_IDX 7 K 25
FILTER
TABLE ACCESS FULL USER1.TABLE3 51 1 K 3
FILTER
TABLE ACCESS FULL USER1.TABLE3 38 1 K 3
[/code]


Without using bind variables in the query and very fast execution: (I am using the same values in this query as used for the bind variables above)

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
[code=oracle]

SELECT STATEMENT Optimizer Mode=CHOOSE 1 120
HASH JOIN 1 147 120
HASH JOIN 6 444 103
TABLE ACCESS BY INDEX ROWID user1.table1 4 180 4
INDEX RANGE SCAN user1.ENT_IDX1 4 2
VIEW user1.MNT_table 1 1 K 52 K 99
UNION-ALL
HASH JOIN OUTER 630 25 K 6
TABLE ACCESS FULL user1.table1 630 7 K 4
TABLE ACCESS FULL user1.table2 507 14 K 1
HASH JOIN 107 2 K 44
VIEW 107 1 K 39
SORT UNIQUE 107 1 K 39
INDEX FAST FULL SCAN user1.table2_U0 1 507 5 K 1
TABLE ACCESS FULL user1.table1 630 7 K 4
HASH JOIN 503 16 K 45
TABLE ACCESS FULL user1.table1 630 7 K 4
VIEW 503 10 K 40
SORT UNIQUE 503 14 K 40
TABLE ACCESS FULL user1.table2 503 14 K 1
TABLE ACCESS FULL user1.table1 629 11 K 4
VIEW 1 K 128 K 16
UNION-ALL
TABLE ACCESS BY INDEX ROWID user1.history_t able 4 192 5
INDEX RANGE SCAN user1.OEE_H_END _IDX 4 3
TABLE ACCESS BY INDEX ROWID user1.history_t able 4 152 5
INDEX RANGE SCAN user1.OEE_H_END _IDX 4 3
FILTER
TABLE ACCESS FULL user1.table3 1 K 39 K 3
FILTER
TABLE ACCESS FULL user1.table3 769 21 K 3
[/code]

Note that the cost for the query that uses the bind variables is much higher than the cost of the query without bind variables. I also tried the same query with the underlying table alone and still the execution time was long.

The modified query with the base table of the HISTORY_VIEW_NO W view is below.
[code=oracle]

SELECT *
FROM HISTORY_TABLE
WHERE begin_inst <= :endTime
AND end_inst >= :beginTime

--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 1643
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_T ABLE 2 K 110 K 1643
INDEX RANGE SCAN USER1.OEE_H_BEG IN_IDX 7 K 25

--The modified query without bind variables (and using the same values as used ---for the bind variables) produced the explain plan below.

--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 4 5
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_T ABLE 4 208 5
INDEX RANGE SCAN USER1.OEE_H_END _IDX 4 3
[/code]

Any pointers to this problem would be very helpful.

Thanks.