Re: Poor Performance when SQL with wild card

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 02 Feb 2000 14:08:33 -0500
Message-ID: <sqkg9s8rnj88mk3juhcdfrlm01qj829hb0@4ax.com>

A copy of this was sent to "Maki Takahashi" <maki_at_randd.tjsys.co.jp> (if that email address didn't require changing) On Wed, 2 Feb 2000 18:28:27 +0900, you wrote:

>I'm now using SQLServer 6.5 and trying to move to Oracle8.
>but its performance is tremendously bad...
>only the case using wild card like
> select * from table where col like '%aaa%'
>
>SQLServer 6.5 3 minutes
>SQL 7.0 30 min
>Oracle8 more than 30 min
>
>sometimes Oracle fails to return the result set when it includes so many
>records.
>DB tuning and index tuning which I did were not effective at all.
>
>I can't have any clue for tuning now. but I have to do version up anyway.
>Is that unavoidable to be poor performance on Oracle8 ?
>or any solution we have?
>

A query like that cannot really use an index -- it has to full scan the table. Since the like RHS starts with '%' -- it is going to full scan.

One thing I've done in the past for things like this is to use a hint and help the optimizer realize a fast full scan of the index is in order. For example, consider this:

scott_at_dev8i> -- create table t as select * from all_objects;
scott_at_dev8i> 
scott_at_dev8i> -- create index oname_idx on t(object_name);
scott_at_dev8i> 
scott_at_dev8i> -- analyze table t compute statistics;
scott_at_dev8i> 
scott_at_dev8i> set autotrace on
scott_at_dev8i> 
scott_at_dev8i> select object_name, object_id from t where object_name like

'%aaa%';

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
/6aaa1cc8_NullExpression             8364
/aaad6c00_RecoveryCoordinatorH       6996
/6aaa1cc8_NullExpression             8365
/aaad6c00_RecoveryCoordinatorH       6997
/faaab49b_ZipFile                    8659
/6aaa6f3_HttpResponseHeaders        10870
/faaab49b_ZipFile                    8658
/6aaa6f3_HttpResponseHeaders        10871

8 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=1081 Bytes=50807) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost=44 Card=1081 Bytes=50807)

Statistics

          0  recursive calls
          4  db block gets
        434  consistent gets
          0  physical reads
          0  redo size
       1675  bytes sent via SQL*Net to client
        703  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

Full table scan. read 434 blocks to get the answer.... Now, same question just phrased differently:

scott_at_dev8i> select /*+ FIRST_ROWS */ object_name, object_id 2 from t, ( select rowid rid from t where object_name like '%aaa%' ) a 3 where t.rowid = a.rid
4 /

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
/6aaa1cc8_NullExpression             8364
/6aaa1cc8_NullExpression             8365
/6aaa6f3_HttpResponseHeaders        10870
/6aaa6f3_HttpResponseHeaders        10871
/aaad6c00_RecoveryCoordinatorH       6996
/aaad6c00_RecoveryCoordinatorH       6997
/faaab49b_ZipFile                    8658
/faaab49b_ZipFile                    8659

8 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS 1 0 NESTED LOOPS (Cost=1177 Card=1081 Bytes=65941)

   2    1     INDEX (FULL SCAN) OF 'ONAME_IDX' (NON-UNIQUE) (Cost=96 C
   3    1     TABLE ACCESS (BY USER ROWID) OF 'T' (Cost=1 Card=21609 B

Statistics

          0  recursive calls
          0  db block gets
        109  consistent gets
          0  physical reads
          0  redo size
       1556  bytes sent via SQL*Net to client
        777  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

Many less blocks read to get the same answer. In this case, we selected just INDEXED columns (rowid is part of the index) in the inline view. Additionally we either HINTED the query as I did or set the optimizer goal to FIRST_ROWS. This helped the optimzer pick a plan that did a fast_full_scan of the index to get the rowids we liked and then used nested loops to go to the underlying table to get the rest of the columns.

If I only needed to retrieve columns that were in the INDEX -- then this simplifies down to:

scott_at_dev8i> select object_name from t where object_name like '%aaa%';

OBJECT_NAME

/6aaa1cc8_NullExpression
/6aaa1cc8_NullExpression
/6aaa6f3_HttpResponseHeaders
/6aaa6f3_HttpResponseHeaders
/aaad6c00_RecoveryCoordinatorH
/aaad6c00_RecoveryCoordinatorH
/faaab49b_ZipFile
/faaab49b_ZipFile

8 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1081 Bytes=3 1 0 INDEX (FAST FULL SCAN) OF 'ONAME_IDX' (NON-UNIQUE) (Cost=1

Statistics

          0  recursive calls
          4  db block gets
         97  consistent gets
          0  physical reads
          0  redo size
       1437  bytes sent via SQL*Net to client
        692  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

which shows another way to speed this up maybe -- add the additional columns you are going to retrieve to the end of the index (assuming they don't make the index huge again slowing down the scan of the index)

>thanks.
>
>// Maki Takahashi
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Feb 02 2000 - 13:08:33 CST