Re: Explain plan question

Arthur wrote:
>
> How can I improve performance of this query .
> The COST is too high (942). Why NESTED LOOPS so high?
> I have index on feature_type and
> I use COST optimizer:
> analyze table TRANSACTION_DETAIL compute statistics;
> analyze table TRANSACTION_HISTORY compute statistics;
>
> SQL> TRUNCATE TABLE PLAN_TABLE;
>
> Table truncated.
>
> SQL> explain plan
> 2 for
> 3 select
> 4 (sum(transaction_detail_tier_points))
> 5 from transaction_detail , transaction_history
> 6 where
> 7 transaction_detail.transaction_id = transaction_history.transaction_id
> and
> 8 transaction_history.feature_type = 'BONUSSKU' and
> 9 merchant_id = 1 and
> 10 '28-FEB-00' LIKE transaction_date;
>
> Explained.
>
> SQL> column operation format a30
> SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||
> 2 DECODE(id, 0, 'Cost = '||position) "OPERATION",
> 3 options, object_name,cost
> 4 FROM plan_table
> 5 START WITH id = 0
> 6 CONNECT BY PRIOR id = parent_id;
>
> OPERATION OPTIONS OBJECT_NAME
> ------------------------------ ------------------------------ --------------
> --------------
> COST
> ---------
> SELECT STATEMENT Cost = 942
> 942
>
> SORT AGGREGATE
>
> NESTED LOOPS
> 942
>
> TABLE ACCESS FULL
> TRANSACTION_HISTORY
> 192
>
> TABLE ACCESS BY INDEX ROWID
> TRANSACTION_DETAIL
> 3
>
> INDEX RANGE SCAN
> TRANSACTION_DETAIL_IND
> 2
>
> 6 rows selected.
>
> SQL>
Don't forget that COST is pretty much irrelevant - the cost figure between two different queries cannot be compared. It is only relative to the other (unseen) access paths that got discarded for the current query.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse