Re: Poor Performance when SQL with wild card

Ok.. why is SQL Server so fast with wildcard query? Does pretty much the same thing you just demonstrated under the covers?

• Dc.

Thomas Kyte wrote:

> 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 - 17:13:43 CST