Tuning query Question

From: Arthur <arthur_at_stario.com>
Date: Mon, 28 Feb 2000 14:41:34 -0800
Message-ID: <vOCu4.1692$XL5.24446@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 - 16:41:34 CST