Re: Tuning query Question

Try this:

select (sum(transaction_detail_tier_points))  from transaction_detail ,

          transaction_history
where transaction_history.feature_type = 'BONUSSKU'

   and merchant_id = 1
   and trunc(transaction_date) = '28-FEB-2000 00:00:00'    and transaction_detail.transaction_id = transaction_history.transaction_id;

The LIKE in your prior sql statement will cause a FULL TABLE SCAN since it has to perform a linear search through each record in the table. The truncation of the transaction date will allow the database to perform a logarithmic (spelling) search for the date of '28-FEB-2000 00:00:00'.

Kevin

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 Thu Mar 09 2000 - 09:14:05 CST