Mathew -- Thanks for the question regarding "Tuning sql with bind variables", version 10.2.0.3
Submitted on 10-Apr-2008 15:51 Central time zone
Last updated 11-Apr-2008 11:40
You Asked
Hi Tom,
Here is a sample sql from a package used for searching clients. The query is very fast when we remove the "OR" condition "ln_cl_id is null" from the WHERE condition. There are many columns with similiar OR condition, which I have removed for simplicity. The table TCLIENT has 1.2 million rows and CL_ID is the primary key.
I. SQL
=======
var ln_cl_id number
exec :ln_cl_id:=15591
select cl_id, frst_nme,lst_nme from tclient
where (cl_id = :ln_cl_id or :ln_cl_id is null);
1 row selected.
Elapsed: 00:00:44.34
Execution Plan
----------------------------------------------------------
Plan hash value: 961750858
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62326 | 1643K| 17314 (31)| 00:01:13 |
|* 1 | TABLE ACCESS FULL| TCLIENT | 62326 | 1643K| 17314 (31)| 00:01:13 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:LN_CL_ID IS NULL OR "CL_ID"=TO_NUMBER(:LN_CL_ID))
II. SQL without the OR condition
================================
var ln_cl_id number
exec :ln_cl_id:=15591
select cl_id, frst_nme,lst_nme from tclient
where (cl_id = :ln_cl_id );
1 row selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2410463105
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TCLIENT | 1 | 27 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | XPKTCLIENT | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CL_ID"=TO_NUMBER(:LN_CL_ID))
exec pkg_cl_search.prc_cl_search(p_cl_id=>15591,.....);
open cursor_cl_results for
select cl_id, frst_nme,lst_nme from
tclient where (cl_id = p_cl_id or p_cl_id is null)
and .....(other conditions);
Is there any way to improve the query other than using a IF statement in the package to create two queries like the following:-
if p_cl_id is not null then
open cursor_cl_results for
select cl_id, frst_nme,lst_nme from
tclient where cl_id = p_cl_id and ....(other conditions);
else
open cursor_cl_results for
select cl_id, frst_nme,lst_nme from
tclient where .....(other conditions execept cl_id check);
end if;
Thanks,
Mathew
and we said...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279 another approach that uses one query - dynamically constructed, to reference just the binds you want.
Hopefully, you understand that if you have:
where (c1 = :bind1 or :bind1 is null)
and (c2 = :bind2 or :bind2 is null)
....
the ability to optimize is basically "non-existent" - we cannot use for example an index on C1 - because we MIGHT NOT BE SEARCHING on c1 at run time. And so on. So basically the optimizers hands are totally "tied", it is not possible to develop an efficient plan.
Hence, you desire a slightly more specific query - one that tells the optimizer what you are searching for.
And one approach to that is outlined in that link above.
what about ...
April 11, 2008 - 9am Central time zone
Reviewer: Sokrates
... replacing or by union all :
sql> create table test(i integer primary key, name varchar2(30));
Table created.
sql> commit;
Commit complete.
sql> select count(*) from test;
COUNT(*)
----------
59467
sql> exec dbms_stats.gather_table_stats(tabname => 'TEST', cascade => true, ownname => user)
PL/SQL procedure successfully completed.
sql> variable i number
sql> exec :i := 40000
PL/SQL procedure successfully completed.
sql> select value from v$mystat where statistic#=(select statistic# from v$statname where
name='table scan rows gotten');
VALUE
----------
329765
sql> set autotr traceonly
sql> select * from test where (i = :i or :i is null);
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102 | 2754 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 102 | 2754 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:I IS NULL OR "I"=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
311 consistent gets
0 physical reads
0 redo size
385 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> set autotr off
sql> select value from v$mystat where statistic#=(select statistic# from v$statname where
name='table scan rows gotten');
VALUE
----------
389592
REM the table was full scanned
sql> set autotr traceonly
sql> select * from test where i = :i union all select * from test where :i is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 489762101
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 2727 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 27 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C00154180 | 1 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | TEST | 100 | 2700 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("I"=TO_NUMBER(:I))
4 - filter(:I IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
385 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> set autotr off
sql> select value from v$mystat where statistic#=(select statistic# from v$statname where
name='table scan rows gotten');
VALUE
----------
389700
REM the full table scan as in step 5 of the plan was not performed
? Followup April 11, 2008 - 11am Central time zone:
the problem is the explosion of predicates - they have more than ONE where clause anded together....
for this simple case, NVL or expansion would be all that is needed
where c = nvl(:b,c)
that would set up "two plans in one" with a filter that would either full scan or index range scan.
yes, this presumes C is NOT NULL -
April 11, 2008 - 9am Central time zone
Reviewer: Mathew Joseph
Thanks for the help.