HELP! Full Table Scan

From: Mike Rushton <mike_at_mrush.demon.co.uk>
Date: 1996/12/06
Message-ID: <32a852d1.5068698@news.demon.co.uk>#1/1

Please help with Oracle doing a FULL TABLE SCAN

I am having problems with Oracle doing a full table scan when (i believe) it shouldn't. Someone please explain why!

The table is called INSURANCE_TRANSACTION and has the following primary key:

ALTER TABLE insurance_transaction
  ADD CONSTRAINT pk_insurance_transaction

  PRIMARY KEY ( insurance_trans_account_period,
                insurance_transaction_type,
                insurance_transaction_batch_no,
                insurance_transaction_number
               )

When i do the following select

SELECT count(*)
FROM insurance_transaction WHERE insurance_trans_account_period = 9610

the quesry uses the index as one would expect.

Explain Plan

---

SELECT STATEMENT                              
  SORT AGGREGATE                              

    INDEX RANGE SCAN PK_INSURANCE_TRANSACTION However, when i do the following:

SELECT premium_class_type,count(*)

FROM   insurance_transaction                      
WHERE  insurance_trans_account_period = 9610    
GROUP BY premium_class_type;

It doesn't use the index.

Explain Plan

---

SELECT STATEMENT                            
  SORT GROUP BY                             

    TABLE ACCESS FULL INSURANCE_TRANSACTION The Group is disabling it. Can someone explain why this is?

The first query takes 2 seconds, the second query takes 20 minutes.

If i add a hint:

SELECT /*+ INDEX(insurance_transaction pk_insurance_transaction)

       premium_class_type,count(*)  
FROM   insurance_transaction                      

WHERE insurance_trans_account_period = 9610 GROUP BY premium_class_type;

Then it DOES use the index, and takes about 2 minutes.

Additional Information

---

The table has approx 24 million records
of which 180,000 are for 9610. (This is 0.75%)

Mike Rushton
mike_at_mrush.demon.co.uk Received on Fri Dec 06 1996 - 00:00:00 CST