Thanks for the question, Chintan.
Asked: August 04, 2016 - 10:17 pm UTC
Last updated: August 07, 2016 - 8:04 am UTC
Version: 11gR2
Viewed 1000+ times
You Asked
Hi Tom,
I am on Oracle 11gR2. I have a table where I have created a text index.
I have written a query as below:
SELECT *
FROM my_transactions t
WHERE 1=1
AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0;
It prompts me for the bind variable 'str'. When I enter it as 'a', it returns me data, which is fine.
But when I pass it as NULL, it returns no data, whereas, here, I want it to return whole data set.
I tried rewriting query as below, but it does full table scan and does not use index. This is leading to poor performance:
SELECT *
FROM my_transactions t
WHERE 1=1
AND (:str IS NULL OR CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0);
Appreciate any help here!
Thanks.
and Connor said...
A simple UNION ALL will do the trick
SQL> create table t1
2 as select * from dba_objects
3 where owner = 'SYS'
4 and object_name like 'DBMS%'
5 and object_type like 'PACKAGE';
Table created.
SQL>
SQL> create index t1_ix on t1 ( object_name )
2 indextype is ctxsys.context;
Index created.
SQL>
SQL>
SQL> variable str varchar2(20)
SQL>
SQL> set autotrace on
SQL> exec :str := 'pipe';
PL/SQL procedure successfully completed.
SQL> select object_Id
2 from t1
3 where :str is not null and contains(object_name,'%'||:str||'%') > 0
4 union all
5 select object_Id
6 from t1
7 where :str is null ;
OBJECT_ID
----------
11499
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 785902765
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 554 | 2801 | 8 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 36 | 4 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | T1_IX | | | 4 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS FULL | T1 | 553 | 2765 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:STR IS NOT NULL)
4 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'%'||:STR||'%')>0)
5 - filter(:STR IS NULL)
Statistics
----------------------------------------------------------
205 recursive calls
0 db block gets
402 consistent gets
3 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> exec :str := null;
PL/SQL procedure successfully completed.
SQL>
SQL> select object_Id
2 from t1
3 where :str is not null and contains(object_name,'%'||:str||'%') > 0
4 union all
5 select object_Id
6 from t1
7 where :str is null ;
OBJECT_ID
----------
11533
11530
12270
12269
11347
16657
...
...
11786
16515
16149
12247
16148
16147
15089
553 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 785902765
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 554 | 2801 | 8 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 36 | 4 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | T1_IX | | | 4 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS FULL | T1 | 553 | 2765 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:STR IS NOT NULL)
4 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'%'||:STR||'%')>0)
5 - filter(:STR IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
10920 bytes sent via SQL*Net to client
948 bytes received via SQL*Net from client
38 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
553 rows processed
SQL>
Notice the FILTER elements which in effect mean that we will only run one "half" of the plan, depending on whether the STR is null or not null.