Re: Tuning query Question

The problem here is the ratio between transaction_detail (180000 rows returned) and transaction_history (250 rows returned). As you don't provide any information about your tables I can only GUESS transaction_date is a likely candidate for an index. Also you shouldn't use like without '%' or '_', like without that is just =. If there is a time in your date field better use between trunc('28-feb-00') and trunc('29-feb-00') - 1/3600.

Cost is usually saying nothing, cardinality usually is exact.

Hth,

Sybrand Bakker, Oracle DBA

Arthur <arthur_at_stario.com> wrote in message news:vOCu4.1692$XL5.24446_at_news.pacbell.net...
> How can I improve performance of this query .
> And if anyone have idea about Statistics I get?
>
> I have index on feature_type and
> use COST optimizer:
> analyze table TRANSACTION_DETAIL compute statistics;
> analyze table TRANSACTION_HISTORY compute statistics;
>
>
> SQL> SET AUTOTRACE ON
> SQL> select
> 2 (sum(transaction_detail_tier_points))
> 3 from transaction_detail , transaction_history
> 4 where
> 5 transaction_detail.transaction_id =
transaction_history.transaction_id
> and
> 6 transaction_history.feature_type = 'BONUSSKU' and
> 7 merchant_id = 1 and
> 8 '28-FEB-00' LIKE transaction_date;
>
> (SUM(TRANSACTION_DETAIL_TIER_POINTS))
> -------------------------------------
>
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=942 Card=1 Bytes=86)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (Cost=942 Card=250 Bytes=21500)
> 3 2 TABLE ACCESS (FULL) OF 'TRANSACTION_HISTORY' (Cost=192
> Card=250 Bytes=15000)
>
> 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'TRANSACTION_DETAIL'
> (Cost=3 Card=180000 Bytes=4680000)
>
> 5 4 INDEX (RANGE SCAN) OF 'TRANSACTION_DETAIL_IND' (NON-
> UNIQUE) (Cost=2 Card=180000)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 472 recursive calls
> 7 db block gets
> 3289 consistent gets
> 977 physical reads
> 0 redo size
> 583 bytes sent via SQL*Net to client
> 917 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 9 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
>
Received on Mon Feb 28 2000 - 17:03:53 CST