Explain plan question

From: Arthur <arthur_at_stario.com>
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