Re: Poor Performance when SQL with wild card

I appreciate your quick advice with such as exact verification. I understand how we can lead Oracle Optimiser. It was so helpful for me, also your site FAQ.

I tested on my real DB, according to your advice, about second example. Execution Plan and Statistics showed better performance than before actually.
( not enough for us, but ) Because I can't get rid of non-index fields to get,
it's maximun speed I suppose.
and now we have a plan to extend that table, so your advice'll be more effective.

// Maki Takahashi

>
> 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.
>
Received on Thu Feb 03 2000 - 23:24:54 CST