Explain plan question
Date: Mon, 28 Feb 2000 14:09:07 -0800
Message-ID: <4kCu4.1685$XL5.23636_at_news.pacbell.net>
[Quoted] [Quoted] 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> Received on Mon Feb 28 2000 - 23:09:07 CET