Stored procedure SQL execution plan

4
votes

I'm a bit stuck with a stored procedure that is executing really slow. The stored procedure basically contains a query that uses an incoming parameter (in_id) and is put in a cursor like this:

open tmp_cursor for 
select col1, col2, col3
from table1 tab
where ((in_id is null) or (tab.id = in_id));  -- tab.id is the PK

When I get an execution plan for the SQL query separately with predefined value, I get good results with the query using an index. However when I call the procedure from my application, I see that no index is being used and the table gets full scan, thus giving slow performance.
If I remove the first part of WHERE clause "(in_id is null)" the performance from the application is fast again.
How come the index isn't used during the call from my application (in_id is passed in)?

3
sqloraclesql-execution-plan
If the query is suppose to return data if the incoming parameter is null or if it matches the table ID then wouldn't it be better to do a if/else statement where if it's null then it will run the query without the where clause, else it will run with only the table ID condition? - John Odom

3 Answers

2
votes

in_id is null

I have answered a similar question here https://stackoverflow.com/a/26633820/3989608

Some facts about NULL values and INDEX:

  • Entirely NULL keys are not entered into a ‘normal’ B*Tree in Oracle

  • Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.

Some portion of the demonstration by Thomas Kyte regarding the same:

ops$tkyte@ORA9IR2> create table t
2  as
3  select object_id, owner, object_name
4    from dba_objects;
Table created.

ops$tkyte@ORA9IR2> alter table t modify (owner NOT NULL);
Table altered.

ops$tkyte@ORA9IR2> create index t_idx on t(object_id,owner);
Index created.

ops$tkyte@ORA9IR2> desc t
Name                    Null?    Type
----------------------- -------- ----------------
OBJECT_ID                        NUMBER
OWNER                   NOT NULL VARCHAR2(30)
OBJECT_NAME                      VARCHAR2(128)

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.

Well, that index can certainly be used to satisfy “IS NOT NULL” when applied to OBJECT_ID:

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1)

In fact – even if the table did not have any NOT NULL columns, or we didn’t want/need to have a concatenated index involving OWNER – there is a transparent way to find the NULL OBJECT_ID values rather easily:

ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.

ops$tkyte@ORA9IR2> create index t_idx_new on t(object_id,0);
Index created.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'T_IDX_NEW' (NON-UNIQUE) (Cost=2 Card=1)

Source : Something about nothing by Thomas Kyte

1
votes

Assuming that in_id is a query parameter - not a column name:

The query has to have only ONE exec plan, regardless of the input. So if you pass parameter in_id as NULL, then it is supposed to return ALL rows. If you pass non-NULL in_id is should return only a single PK value.

So Oracle chooses the "worst possible" exec. plan to deal with "worst possible" scenario. The "generic" queries are road to hell. Simply split the query into two.

select col1, col2, col3
from table1 tab
where in_id is null or in_id is not null;

This will use FULL table scan, which is the best way how to get all the rows.

select col1, col2, col3
from table1 tab
where tab.id = in_id;  -- tab.id is the PK

This will use UNIQUE index scan, which is the best way how to get a single indexed row.

0
votes
select col1, col2, col3 from table1 tab where (tab.id = nvl(in_id,tab.id));

May be help.. or you may use oracle hint 

+Use_concat