Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, karthick.

Asked: January 22, 2009 - 8:53 am UTC

Last updated: November 15, 2011 - 8:41 am UTC

Version: 10g R2

Viewed 10K+ times! This question is

You Asked

Hi,

Here is a small test case.

SQL> create table t
  2  as
  3  select *
  4  from all_objects
  5  /

Table created.

SQL> create index t_idx on t(object_type)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.

SQL> delete from plan_table
  2  /

10 rows deleted.

SQL> explain plan
  2  for
  3  select * from t where object_type like '%TABLE%'
  4  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5823 |   511K|   358   (3)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    |  5823 |   511K|   358   (3)| 00:00:05 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

   1 - filter("OBJECT_TYPE" LIKE '%TABLE%')

13 rows selected.


When we use LIKE with preceding % it always goes for full scan.

My question is

1. Why
2. In such case is full scan the best option. Is it possible to get a index scan with a better cost ?

Thanks,
Karthick.

and Tom said...

1) because it would have to inspect each and every index entry - think about it, you are asking for all strings that start with *antyhing*, which means *everything* might apply

2) if you expect that very few rows will actually be returned, and an index would be appropriate, you can use TEXT to build an index that can retrieve data efficiently using leading and trailing wildcards

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

Rating

  (3 ratings)

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

Comments

Sometimes it *will* use the index...

Steve C., January 22, 2009 - 5:09 pm UTC

There are instances where Oracle will use an index to resolve a like with the pattern of '%text%'. If the query can be resolved without having to go back to the table (rowid lookup), the index may be chosen. Example:

select distinct first_nm from person where first_nm like '%EV%';



Tom Kyte
January 22, 2009 - 5:39 pm UTC

yes, if it can use the index to AVOID going to the table - in that case it would do an index fast full scan - a full scan of the smaller index.

but it isn't really using the index as an index in that case - just as a faster means to full scan the table (by using this skinnier version of it)

A reader, November 14, 2011 - 8:23 am UTC

Hi Tom,

we are fetching data based on Text column.

if we add multiple LIKE condition on same column in WHERE clause the query takes more time..

example:
WHERE some condition
AND ( UPPER(MESSAGE) like '%CANCEL%'
OR UPPER(MESSAGE) like '%CHANGE%'
OR UPPER(MESSAGE) like '%AMEND%')


i need to add around 20 more with LIKE..

will index give solution for this? could you please suggest good solution?
Tom Kyte
November 15, 2011 - 8:41 am UTC

it shouldn't really take that much more time - what kind of "more time" are you talking about there?

I see NO numbers whatsoever here - no clue what is happening in real life.


It is exceedingly doubtful an index would be useful for a leading wildcard search.

Unless the index were a text index and you used contains to search instead of like.

Reader, February 27, 2015 - 3:16 pm UTC 

FULL table scan does not make sense in the following situation :
- We have a 15G table with about 40 columns
- One of the columns called NAME is indexed and the index size is 800M

- The following shows that an INDEX FULL scan requires almost 8 times less I/O then a FULL TABLE scan.
   
SQL> SELECT NAME, LISTING_ID FROM DPF.VBLIF_SO_HISTO WHERE  NAME LIKE '%Bess Tank Line%' ;

NAME                           L
------------------------------ -
 Bess Tank Line

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2266721468

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |  2006 |   430K  (1)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| VBLIF_SO_HISTO |     1 |  2006 |   430K  (1)| 00:00:17 |
------------------------------------------------------------------------------------

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

   1 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%Bess Tank Line%')


Statistics
----------------------------------------------------------
        795  recursive calls
          0  db block gets
    1183985  consistent gets
    1183829  physical reads
          0  redo size
        428  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
          1  rows processed



SQL> SELECT /*+ index (VBLIF_SO_HISTO VBLIF_SO_HISTO_NAME) */
  2  NAME, LISTING_ID
  3  FROM DPF.VBLIF_SO_HISTO
  4  WHERE NAME LIKE  '%Bess Tank Line%' ;

NAME                           L
------------------------------ -
 Bess Tank Line

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3012449389

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |  2006 |  2055K  (1)| 00:01:21 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| VBLIF_SO_HISTO      |     1 |  2006 |  2055K  (1)| 00:01:21 |
|*  2 |   INDEX FULL SCAN                   | VBLIF_SO_HISTO_NAME |  2192K|       |   152K  (1)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%Bess Tank Line%')


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


I don't see why we have to use a hint ;  the optimizer should be able to realize that.

Thanks

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library