Geek Answers Handbook

Stored Procedure SQL Query Execution Plan

I am a bit stuck in a stored procedure that runs very slowly. The stored procedure basically contains a request that uses the input parameter (in_id), and is placed in such as:

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 the SQL query execution plan separately with a predefined value, I get good results with the query using the index. However, when I call the procedure from my application, I see that no index is used, and the table gets a full scan, which gives slow performance.
If I remove the first part of the WHERE clause "(in_id is null)" the application performance is fast.
Why is the index not used during a call from my application (in_id passed)?

+4
source share
3 answers

Assuming this in_idis a query parameter, not a column name:

A request must have only one exec plan, regardless of input. Therefore, if you pass the parameter in_idas NULL, it should return ALL rows. If you pass non-NULL in_id, only one PK value should be returned.

So, Oracle chooses the “worst possible” exec. plan on doing the least possible scenario. "General" queries are the way to hell. Just split the request into two.

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

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

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

UNIQUE- , .

+1

in_id null

fooobar.com/questions/1582398/...

NULL INDEX:

  • NULL- " B * Oracle

  • , , , C1 C2, , , NULL , , C1 NULL, C2 NULL - .

:

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.

, , , "IS NOT NULL" 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)

- NOT NULL, / , OWNER - NULL OBJECT_ID:

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)

: -

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

Maybe help .. or you can use oracle hint

+Use_concat
0
source

More articles:


All Articles