vendor sql tuning

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Fri, 15 Jul 2005 00:23:57 -0400
Message-ID: <d95e86070507142123beb8ba9@mail.gmail.com>

This is a 9.2 database on HP 11i.

There is some sql from a 3rd party app that is supporting one of our call centers. It is taking approximately 3 minutes to complete. They are not using bind variables and we cannot change the sql. They also have no integrity constraints defined. Here is the sql

SELECT case_id.*, case_id.ROWID

   FROM case_id
   WHERE (case_id.company_id = 'SYS'

       AND (case_id.address_id IN (SELECT address.address_id
                                                       FROM address
                                                       WHERE
(address.company_id = 'SYS'
                                                            AND
address.last_name = 'Plaugher'
                                                            AND
address.postal_code = '45804'
                                                      ))
        OR case_id.case_id IN (SELECT DISTINCT associated_address.case_id
                                                  FROM
associated_address, address
                                                   WHERE (address.company_id =
                                                                    
associated_address.company_id
                                                       AND address.address_id =
                                                                 
associated_address.address_id
                                                       AND
address.company_id = 'SYS'
                                                       AND
address.last_name = 'Plaugher'
                                                       AND
address.postal_code = '45804' ))
              )
         )

ORDER BY case_id.company_id, case_id.case_id;

The two subqueries return sub second and each returns no rows. Every row in case_id has a company value of 'SYS'. The sql is currently full scanning case_id which has 2890788 rows. Case_id is unique. I built a unique index on case_id, address_id, company_Id and ran dbms_stats on the table. It won't use the index. I created a primary key constraint on case_id and it won't use the index. I'm not sure where to go from here.

...again, i can't change the sql.

i know where the execution time is being spent. i know i need to have an index access to case_id. the query will never return more than three or 4 rows.

even the explain plan shows that i am using the unique index on case_id,address_id,company_Id but when i trace the session it is a full table scan.

any suggestions?

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 14 2005 - 23:25:53 CDT