Question:

I have Primary Keys on two tables:
(Oracle8i)
 

create table transaction_history (
 transaction_id             integer     not null  PRIMARY KEY USING INDEX
TABLESPACE IDXS,
 feature_id                    integer     not null references
merchant_features(feature_id),
 feature_type                varchar(100)     not null references
feature_types(feature_name),
 merchant_id                  integer not null references
merchant(merchant_id),
  transaction_date         date default sysdate,
........);

create table transaction_detail (
 transaction_id                                integer     not null
references transaction_history(transaction_id),
 transaction_detail_name             varchar(100),
.......);

When I execute query :

truncate table plan_table;
explain plan
for
select
(sum(transaction_detail_tier_points))
from transaction_history h, transaction_detail d
where
h.transaction_id = d.transaction_id  and
h.feature_type = 'PURCHASE' and
h.merchant_id = 1
and '28-FEB-00' LIKE h.transaction_date;

In explain plan it was not used index of PK (   TABLE ACCESS    FULL
TRANSACTION_HISTORY);
 But when I created additional INDEX

create index TRANSACTION_HISTORY_IND
 on  transaction_history( TRANSACTION_ID, feature_id, feature_type,
customer_id, merchant_id,
                         transaction_date, transaction_tier_id, state)
tablespace IDXS;

In explain plan   was:        ( INDEX        FAST FULL SCAN
TRANSACTION_HISTORY_IND)
and the performence improoved about 300%.

why it happened,
Thank you,
Arthur

Answer 1:

Arthur, in order for Oracle to use an index as a general rule your
where clause must reference the leading column or columns in the
index.  You only reference the transaction_id in the join condition so
as far as Oracle knows he needs every single row in the history table
and a full table scan is the best way to get every row.

Afer you built the second index every column in the transaction_history
table you reference in your where clause is in the index so Oracle
chose to basically do a full table scan (Fast full scan) of the index
which is probably much smaller than the table.

If you built this index just to support your query you only need the
four columns in your where clause and any hist table columns you want
to select as the other columns are just consuming space.

Answer 2:

I have seen a odd situation where a primary key was created on a table but no
cooresponding index was created.  That, or the index got dropped but the
primary key definition remained.  Another idea would be to name the primary
key specifically.  Don't know if that would make a difference.  Probably not.

Hope this gives some ideas.