Thanks for the question, OP.

Asked: November 27, 2000 - 10:04 pm UTC

Last updated: December 22, 2014 - 7:39 pm UTC

Version: 8.1.6.0.0

Viewed 10K+ times! This question is

You Asked

This Question is Related with the Tuning process.
Dear Tom,
Hi !

The LIKE operator if used do not make use of the INDEX
created on the table for that particular column.
Is there any way by which the LIKE condition can be
replaced in a fashion as to make sure the INDEX if
Exists can be utilized.

Is the same rule i.e whenever we make use of IN operator,
the INDEX is not utilized.If yes,Kindly suggest the
alternative for this also ?

Please answer taking this statement as an Example :=-

select empno,ename,deptno from emp
where name like 'a%';

Thanks & Regards
OP

and Tom said...

A like will use an index -- it depends on many other factors as to whether an index will be used or not. Look at this example. I copy the EMP table and run your query. It uses the index. I compute statistics on the table (small table) and it does not. The table just isn't BIG enough to use an index using CBO. I change my optimier goal to be "first rows" which favors index access more and it uses the index again....

So, LIKE definitely will (or will not) use an index. It depends on the circumstances and one would need more info to diagnose why/why not an index is being used in a particular case.

ops$tkyte@DEV816> create table emp
2 as
3 select * from scott.emp;

Table created.

ops$tkyte@DEV816> create index name_idx on emp(ename);

Index created.

ops$tkyte@DEV816> set autotrace on explain
ops$tkyte@DEV816> select empno,ename,deptno
2 from emp a
3 where ename like 'a%'
4 /

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=5
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1
2 1 INDEX (RANGE SCAN) OF 'NAME_IDX' (NON-UNIQUE)

ops$tkyte@DEV816> analyze table emp compute statistics;

Table analyzed.

ops$tkyte@DEV816> select empno,ename,deptno
2 from emp b
3 where ename like 'a%'
4 /

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=10)

ops$tkyte@DEV816> alter session set optimizer_goal = first_rows;

Session altered.

ops$tkyte@DEV816> select empno,ename,deptno
2 from emp c
3 where ename like 'a%'
4 /

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2
2 1 INDEX (RANGE SCAN) OF 'NAME_IDX' (NON-UNIQUE)

ops$tkyte@DEV816> set autotrace off

Rating

(17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

full table scan

A reader, October 01, 2001 - 11:43 am UTC

Suppose Iam using the like operator in a sub query in the predicate,
and Iam querying one of the two tables in the from clause in the sub query in the predicate.

Will we do a full table scan of the other table for each row returned by the subquery, or will we do one full table scan for all the rows returned by the sub query.

October 01, 2001 - 3:00 pm UTC

impossible to say without seeing the query, understanding the indexes and knowing the size/distribution of the data.

FOLLOW UP TO ABOVE COMMENT

A reader, October 01, 2001 - 3:19 pm UTC

select COLUMNS
from T,T1
where T.id = T1.id
OTHER CONDITIONS AND
T.Y_cd not in (SELECT Y_CD FROM T WHEREY_CD like '%GHIO%')
group by COLUMNS

Table t contains 2 million rows, table t1 contains 1000 rows, and the optimizer is RBO

FOLLOW UP TO ABOVE COMMENT

A reader, October 02, 2001 - 10:44 am UTC

Tom

The query is given above.

What about RBO

atul, July 26, 2002 - 10:23 pm UTC

Sir,

Like operator uses index in CBO,but if we have RBO
how to deal with it??
we can't change to FIRST_ROWS in RBO...

Thanks.
atul

July 28, 2002 - 3:23 pm UTC

did any say the RBO wouldn't use an index with like? I don't see that here at all.

scott@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
scott@ORA817DEV.US.ORACLE.COM> select * from emp where ename like 'F%';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)

that was the RBO (no cost, no card = RBO was used)

Using Like %

Dan, April 03, 2003 - 6:32 pm UTC

Tom,
We have a table which holds loan data of 900,000 records.We have search screen where most of the search is based on the loan id which is a number field.
My question is what is best way to use it...I cannot use like with number as i am forcing loan id to be character.

Dan

April 03, 2003 - 9:19 pm UTC

don't understand what you are asking for....

you say "is a number field"
"cannot use like with number"
"forcing loan id to be character"

those seem to be contradictory.

Dan could try this...

A reader, April 03, 2003 - 11:32 pm UTC

You could do something as obscure as

select *
from loan
where (loan_id between :x*power(10,1) and (:x+1)*power(10,1)-1)
or (loan_id between :x*power(10,2) and (:x+1)*power(10,2)-1)
or (loan_id between :x*power(10,3) and (:x+1)*power(10,3)-1)
or (loan_id between :x*power(10,4) and (:x+1)*power(10,4)-1)
or (loan_id between :x*power(10,5) and (:x+1)*power(10,5)-1)
or (loan_id between :x*power(10,6) and (:x+1)*power(10,6)-1)

or a function-based index on to_char(loan_id)??

but... if it needs to be searched like a character string, maybe it should be a charcter...

Wildcard search with IN operator

A reader, February 19, 2007 - 1:51 pm UTC

Tom,

How can I use IN operator for wildcard search? For example, I want to do this:

Update <table>
set col1=new_value
where col2=val2
and col3 LIKE IN ('%1','%2','%3');

Not sure if there is a way to combine IN and LIKE.

Thanks,
NC

February 19, 2007 - 2:23 pm UTC

it is called "or"

where (col3 like A or col3 like B or ... )

LIKE operator with SQL Function

Devendra Basak, April 20, 2007 - 5:21 am UTC

Hi Tom,

I have following query :

SELECT EMP_NAME
FROM EMP
WHERE UPPER(EMP_NAME) LIKE '%ABC%';

CREATE INDEX IX_NAME ON
emp(UPPER(EMP_NAME));

Though I have used FBI (Function Based Index) but Explain Plan shows that its doing a full table scan ie Index is not used.

Pls advise how can i optimize above query as this table has approx 2 million records.

Regards
Devendra

April 20, 2007 - 7:12 am UTC

well, think about it - you have a leading percent (what would a function based index do for you here...)

you can use a text index

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37336026927381#37360199308431

Tune Like Operator

Shweta, September 23, 2010 - 5:55 am UTC

Hi,
Say I have two queries:
1) Select EMPNO,ENAME FROM EMP WHERE JOB='MANAGER';
2) Select EMPNO,ENAME FROM EMP WHERE JOB LIKE 'MANAGE%';

Now, if i will use FunctionBased Index then it will only work for query 1 and go for Full table Scan in Query 2.
If i am using Text Index, then query2 will tune but that index will not work for Query 1.

Please tell me the solution for this as i want to create index which should tune my both of the above queries.

Please advice.

Thanks.
Shweta

September 27, 2010 - 7:29 am UTC

if you use a function based index, I fail to see how job='MANAGER' would use it, the index would be on f(job), NOT job.

so, where is your work - I always show you mine, where is your example?

I have the same problems with your text index comment - I don't see why a text index would not be used for either of those queries (given you rewrite the query to use contains of course).


ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(upper(object_name));

Index created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where upper(object_name)='MANAGER';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   242 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   242 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("OBJECT_NAME")='MANAGER')

ops$tkyte%ORA11GR2> select * from t where upper(object_name) like 'MANAGER%';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   242 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   242 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("OBJECT_NAME") LIKE 'MANAGER%')
       filter(UPPER("OBJECT_NAME") LIKE 'MANAGER%')

ops$tkyte%ORA11GR2> set autotrace off

Plan changes from Index range to FTS

Rajeshwaran Jeyabal, December 19, 2014 - 1:09 pm UTC

Tom,

Can you explain me why the plan changes from Index range scan to FTS with a simple OR condition added in the predicate?

<code>
drop table t purge;
create table t as select * from all_objects;
create index t_ind on t(object_name);

begin
 dbms_stats.gather_table_stats(user,'T',
 method_opt=>'for all indexed columns size 254',
 no_invalidate=>false);
end;
/

variable x varchar2(40)
exec :x :='Z';
rajesh@ORA11G> set autotrace traceonly explain statistics
rajesh@ORA11G> select sum(object_id)
  2  from t
  3  where object_name like upper(:x)||'%'
  4  /

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2602990223

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    38 |   336   (0)| 00:00:05 |
|   1 |  SORT AGGREGATE              |       |     1 |    38 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |  4244 |   157K|   336   (0)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | T_IND |   764 |       |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_NAME" LIKE UPPER(:X)||'%')
       filter("OBJECT_NAME" LIKE UPPER(:X)||'%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> select sum(object_id)
  2  from t
  3  where ( object_name like upper(:x)||'%'
  4     or :x is null );

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38 |   338   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |    38 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  8276 |   307K|   338   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X IS NULL OR "OBJECT_NAME" LIKE UPPER(:X)||'%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1216  consistent gets
          0  physical reads
          0  redo size
        534  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11G>
rajesh@ORA11G> set autotrace off
rajesh@ORA11G>

</code>

December 22, 2014 - 7:39 pm UTC

think about it for a moment....

if :x is null - how many rows are you going to hit? do you wish to hit that many rows via an index?

It all comes down to the estimated cardinality here - when you introduce an OR- the number of rows likely to be returned will go up in general. and it did.

You want to use NVL or expansion for this...



ops$tkyte%ORA11GR2> create table t
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx on t(object_name);

Index created.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x varchar2(30)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(object_id)
  2    from t
  3   where object_name = nvl(:x,object_name);

COUNT(OBJECT_ID)
----------------
           72984


Execution Plan
----------------------------------------------------------
Plan hash value: 1232542298

---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |    25 |   104   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE         |       |     1 |    25 |            |          |
|   2 |   CONCATENATION         |       |       |       |            |          |
|*  3 |    FILTER               |       |       |       |            |          |
|*  4 |     INDEX FAST FULL SCAN| T_IDX | 72984 |  1781K|   101   (1)| 00:00:02 |
|*  5 |    FILTER               |       |       |       |            |          |
|*  6 |     INDEX RANGE SCAN    | T_IDX |     2 |    50 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:X IS NULL)
   4 - filter("OBJECT_NAME" IS NOT NULL)
   5 - filter(:X IS NOT NULL)
   6 - access("OBJECT_NAME"=:X)

ops$tkyte%ORA11GR2> set autotrace off

there you get an index range scan when you supply a non-null bind and a full scan when you bind a null - best of both worlds!

Plan changes from Index range to FTS

Rajeshwaran Jeyabal, December 23, 2014 - 6:29 am UTC

I got the above trick of using nvl(:x,object_name) from your Oracle magazine column.
http://www.oracle.com/technetwork/issue-archive/o43asktom-094944.html - (section how to write this query) talks in detail about our issues.

How ever that does not applies here, because I have LIKE operator not the equal(=) condition. (see the below query with plans, I don't see the best of both for LIKE operators)

Questions is when we have a query like this ( select count(y) from t where ( object_name LIKE upper(:x)||'%' or :x is null ) ), why don't the optimizer peak the bind variable during hard parsing and come up with the best explain plan ( if bind variable value is null or has the most popular value then FTS, else go for an index plan) and then during soft-parsing Adaptive cursor sharing can kick in and come up with child cursor's if needed.

rajesh@ORA11G> select sum(object_id) ,  count(*)
  2  from t
  3  where object_name like nvl(upper(:x)||'%',object_name)
  4  /

SUM(OBJECT_ID)   COUNT(*)
-------------- ----------
        170540          2

1 row selected.

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  gj98cv5pa6pzm, child number 0
-------------------------------------
select sum(object_id) ,  count(*) from t where object_name like
nvl(upper(:x)||'%',object_name)

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   339 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    38 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  4244 |   157K|   339   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" LIKE NVL(UPPER(:X)||'%',"OBJECT_NAME"))

Regexp_substr vs LIKE

A reader, December 25, 2014 - 5:35 am UTC

Hi Tom, Could you explain if a regexp_substr would accomplish the performance improvement instead of using the "like" wild card.

Thanks.

Regexp_substr vs LIKE

A reader, December 25, 2014 - 5:35 am UTC

Hi Tom, Could you explain if a regexp_substr would accomplish the performance improvement instead of using the "like" wild card.

Thanks.

Solomon Yakobson, December 27, 2014 - 1:45 pm UTC

Hi Tom,

In regards to your post with "there you get an index range scan when you supply a non-null bind and a full scan when you bind a null - best of both worlds!"

The only reason NVL will work in Rajeshwaran's case is table t is a CTAS of all_objects and therefore object_name is not null.

In general, condition:

where ( object_name like upper(:x)||'%' or :x is null );

isn't equivalent to original:

where object_name = nvl(:x,object_name);

When :x is null, former will return all table rows while latter will return table rows where column value is not null.

Solomon Yakobson.

@solomon

Rajeshwaran Jeyabal, January 19, 2015 - 12:04 pm UTC

Solomon,

I did a conditional binding like this, and it worked out for me (able to get the best of both - Index plan for inputs and FTS for without (null) inputs).

Just adopted the trick published in Oracle magazine.
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

drop table t purge;
create table t 
as
select *
from all_objects;
create index t_idx on t(object_name);
exec dbms_stats.gather_table_stats(user,'T');

rajesh@ORA10G> create or replace procedure get_details
  2  (x varchar2 )
  3  as
  4     l_sql long;
  5     p_out sys_refcursor;
  6     l_value number;
  7  begin
  8     l_sql := ' select sum(object_id) '||
  9             ' from t ';
 10
 11     if x is not null then
 12     l_sql := l_sql
 13             ||' where object_name like :b1 ' ;
 14     else
 15     l_sql := l_sql
 16             ||' where 1=1 or :b1 is null ' ;
 17     end if;
 18
 19     open p_out for l_sql
 20             using x ;
 21     fetch p_out into l_value;
 22     close p_out;
 23     for y in (select * from table
 24                     (dbms_xplan.display_cursor))
 25     loop
 26             dbms_output.put_line(y.plan_table_output) ;
 27     end loop;
 28  end;
 29  /

Procedure created.

rajesh@ORA10G> variable y refcursor
rajesh@ORA10G> variable x varchar2(30)
rajesh@ORA10G> exec :x :='ABC';

PL/SQL procedure successfully completed.

rajesh@ORA10G> exec get_details(:x);
SQL_ID  363vwrc8ud3c5, child number 0
-------------------------------------
 select sum(object_id)  from t  where object_name like :b1

Plan hash value: 1789076273

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    29 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     2 |    58 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_NAME" LIKE :B1)
       filter("OBJECT_NAME" LIKE :B1)


PL/SQL procedure successfully completed.

rajesh@ORA10G> exec :x :=NULL;

PL/SQL procedure successfully completed.

rajesh@ORA10G> exec get_details(:x);
SQL_ID  31d552mw02r1h, child number 0
-------------------------------------
 select sum(object_id)  from t  where 1=1 or :b1 is null

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   184 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 58571 |   285K|   184   (1)| 00:00:03 |
---------------------------------------------------------------------------


PL/SQL procedure successfully completed.

rajesh@ORA10G>

to Rajesh

George Joseph, January 22, 2015 - 2:10 am UTC

from the posting above. will there be a plan change if the query
select sum(object_id) , count(*)
2 from t
3 where object_name like nvl(upper(:x)||'%',object_name)

is modified as

select sum(object_id) , count(*)
2 from t
3 where object_name like nvl(upper(:x),object_name)

The change done is on the "UPPER(:X)||'%' to UPPER(:X).

For queries that need to make use of wildcard operator, will binding the variable exec :x='ABC%' achieve the desired results and also how would the plan look like(will it use index-for ABC or full-for nulls)

I dont have an Oracle instance to test this out.

to George Joseph

Rajeshwaran, Jeyabal, June 02, 2015 - 12:19 pm UTC

George - i dont see any changes to plans (not null binds should use index plans and null binds should use FTS, but that doesn't happen)

rajesh@ORA10G> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

rajesh@ORA10G> create index t_idx on t(object_name);

Index created.

rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

rajesh@ORA10G> set serveroutput off
rajesh@ORA10G>
rajesh@ORA10G> variable x varchar2(5)
rajesh@ORA10G> exec :x :='ABC';

PL/SQL procedure successfully completed.

rajesh@ORA10G> select sum(object_id) , count(*)
  2  from t
  3  where object_name like nvl( upper(:x)||'%' , object_name);

SUM(OBJECT_ID)   COUNT(*)
-------------- ----------
        205326          1

1 row selected.

rajesh@ORA10G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  13dgz32r16gga, child number 0
-------------------------------------
select sum(object_id) , count(*) from t where object_name like nvl(
upper(:x)||'%' , object_name)

Plan hash value: 2966233522

--------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   170 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |    29 |            |
|*  2 |   TABLE ACCESS FULL| T    |  2696 | 78184 |   170   (2)| 00:00:03
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" LIKE NVL(UPPER(:X)||'%',"OBJECT_NAME"))


20 rows selected.

rajesh@ORA10G> exec :x :='ABC%';

PL/SQL procedure successfully completed.

rajesh@ORA10G> select sum(object_id) , count(*)
  2  from t
  3  where object_name like nvl( upper(:x) , object_name);

SUM(OBJECT_ID)   COUNT(*)
-------------- ----------
        205326          1

1 row selected.

rajesh@ORA10G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  cmann0u7qgsb7, child number 0
-------------------------------------
select sum(object_id) , count(*) from t where object_name like nvl(
upper(:x) , object_name)

Plan hash value: 2966233522

--------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   170 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |    29 |            |
|*  2 |   TABLE ACCESS FULL| T    |  2696 | 78184 |   170   (2)| 00:00:03
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_NAME" LIKE NVL(UPPER(:X),"OBJECT_NAME"))


20 rows selected.

rajesh@ORA10G>

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

full table scan

FOLLOW UP TO ABOVE COMMENT

FOLLOW UP TO ABOVE COMMENT

What about RBO

Using Like %

Dan could try this...

Wildcard search with IN operator

LIKE operator with SQL Function

Tune Like Operator

Plan changes from Index range to FTS

Plan changes from Index range to FTS

Regexp_substr vs LIKE

Regexp_substr vs LIKE

@solomon

to Rajesh

to George Joseph

More to Explore

Performance

Tuning Process for like Operator !

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

full table scan

FOLLOW UP TO ABOVE COMMENT

FOLLOW UP TO ABOVE COMMENT

What about RBO

Using Like %

Dan could try this...

Wildcard search with IN operator

LIKE operator with SQL Function

Tune Like Operator

Plan changes from Index range to FTS

Plan changes from Index range to FTS

Regexp_substr vs LIKE

Regexp_substr vs LIKE

@solomon

to Rajesh

to George Joseph

More to Explore

Performance