Degraded performance of a query after adding Index

Question

I have a query which part of a SP which is executed fairly regularly, and the query took a while to execute, so I decided to have a look at it. I did an autotrace on the query, and this was the execution plan returned [ pasted in pastebin due to excessive size ]

I added indexes on the tables which was undergoing full table access, and ran the query. The query performance was worse then before, despite the cost being significantly lower.

Why is this so, can anyone shed a light on the same ?

The database is an Oracle 10gR2 ( Release 10.2.0.1.0 ).

This is the query being run

SELECT DISTINCT CAC_FLEX_03, CAC_FLEX_04
        FROM PCOM_CUST_PRACTICE_INFO A,
             PGIM_ZIP_CODES          C,
             PGIM_PROD_TARIFF_DATA   B,
             PCOM_CODES_APPL_CODES   D
       WHERE A.PCPI_CUST_CODE IN ('002023', '002025')
         AND C.ZC_ZIP_CODE = A.PCPI_PIN_CODE
         AND C.ZC_CITY_CODE = A.PCPI_CITY
         AND C.ZC_COUNTY_CODE = A.PCPI_COUNTY
         AND C.ZC_STATE_CODE = A.PCPI_STATE
         AND B.PTD_CVR_CODE = 'TF-001'
         AND B.PTD_VALUE_SET2 = A.PCPI_STATE
         AND B.PTD_VALUE_SET4 = A.PCPI_COUNTY
         AND B.PTD_VALUE_SET5 = D.CAC_FLEX_03
         AND D.CAC_FLEX_04 IS NOT NULL
         AND ZC_STATE_CODE =
             (SELECT POL_FLEX_04
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND B.PTD_VALUE_SET3 =
             (SELECT POL_FLEX_01
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND CAC_TYPE = 'TERR-CODE'
         AND CAC_FLEX_03 = 0;

Answer 1

A few things:

First, if you are accessing over half of the data blocks, full scan will be faster because reading the index block is another IO call, so the read of an indexed row is generally twice as expensive time wise as reading a sequential row.

Second, you need to look at your plans with and without the index. There will be information here that will let you know what changed. If you see a "Merge Join Cartesian" the planner has made an error. That plan is NEVER good. Inner loops of full scans have the same IO cost, but take less memory and temp space.

Third, you built stats with ANALYZE TABLE. Don't. Even Oracle says it is bad and broken. Use the dbms_stats package to build your stats, and you will get more accurate stats. If it is still odd, change your sample size, or do full stats instead of estimated.