vendor sql tuning
- From: Chris Stephens <cstephens16@xxxxxxxxx>
- To: "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 15 Jul 2005 00:23:57 -0400
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
