RE: Query Performance

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <bill@xxxxxxxxxxxx>, "'Oracle-L@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 21 Jul 2010 14:46:59 -0400
The existing plans would help. But it would probably be sufficient to
encapsulate the incidentkey table separately as a subquery together with its
filters on in_date (for the partition pruning) and any column filters on
incidentkey (possibly using the rownum trick to force projection), and then
apply the equijoins separately outside that row source. Most likely the
outer join with the attr_code.in_causecode to what seems to actually be
in_firecause.in_causecode frustrates partition pruning in some way. I'm
speculating a bit here in lieu of more information.

 

Regards, 

 

mwf

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Bill Zakrzewski
Sent: Wednesday, July 21, 2010 2:11 PM
To: Oracle-L@xxxxxxxxxxxxx
Subject: Query Performance

 

All -

 

Oracle 10.2.0.4.0

RH Linux 5.3

 

I have a query that contains a subquery.  When I run the subquery as a
standalone it completes in less than 2 minutes, but the full query takes
over 2 hours and ATTR_CODE is a small table.  The first thing I noticed was
that when I run the subquery as a standalone it uses partition pruning for
the incidentkey table, but when incorporated as a subquery it does not.  FYI
- it is partitioned on IN_DATE - one partition for each year.  Any thoughts
for why this is happening or how I can force it to use the partitioning?

 

 

Thanks,

Bill

 

SELECT DISTINCT ATTR_CODE.CODEVALUE, ATTR_CODE.DECODEVALUE,
INCIDENT.INCIDENTID, INCIDENT.FDIDSTATE, 

                INCIDENT.IN_EXPOSURE, INCIDENT.IN_STATUS,
INCIDENT.IN_MUTUALAIDFLAG, INCIDENT.IN_FATALFS, 

                INCIDENT.IN_FATALOTHER, INCIDENT.IN_NONFATALFS,
INCIDENT.IN_NONFATALOTHER, 

                INCIDENT.IN_LOSSPROPERTY, INCIDENT.IN_LOSSOTHER,
INCIDENT.IN_LOSSTOTAL

FROM ATTR_CODE, (SELECT INCIDENTKEY.INCIDENTID, INCIDENTKEY.FDIDSTATE,
INCIDENTKEY.IN_EXPOSURE, 

                        INCIDENTKEY.IN_STATUS, IN_BASIC.IN_MUTUALAIDFLAG,
IN_BASIC.IN_FATALFS, 

                        IN_BASIC.IN_FATALOTHER, IN_BASIC.IN_NONFATALFS,
IN_BASIC.IN_NONFATALOTHER, 

                        IN_BASIC.IN_LOSSPROPERTY, IN_BASIC.IN_LOSSOTHER,
IN_BASIC.IN_LOSSTOTAL, 

                        IN_FIRECAUSE.IN_CAUSECODE

                   FROM INCIDENTKEY, IN_BASIC, IN_FIRECAUSE, ATTR_CODE

                  WHERE INCIDENTKEY.INCIDENTID = IN_BASIC.INCIDENTID 

                    AND IN_BASIC.IN_TYPE >= '110' 

                    AND IN_BASIC.IN_TYPE < '124' 

                    AND IN_BASIC.IN_PROPERTYUSE >= '400' 

                    AND IN_BASIC.IN_PROPERTYUSE < '500' 

                    AND INCIDENTKEY.INCIDENTID = IN_FIRECAUSE.INCIDENTID 

                    AND IN_FIRECAUSE.IN_CAUSECODE = ATTR_CODE.CODEVALUE 

                    AND ATTR_CODE.CODECAT = 266 

                    AND INCIDENTKEY.FDIDSTATE = 'VA' 

                    AND INCIDENTKEY.IN_DATE >=
TO_DATE('01/01/2009','MM/dd/yyyy') 

                    AND INCIDENTKEY.IN_DATE <=
TO_DATE('12/31/2009','MM/dd/yyyy') 

                    AND INCIDENTKEY.FDID NOT IN (SELECT FDID

                                                   FROM FD_HEADER

                                                  WHERE FDIDSTATE = 'VA' 

                                                    AND
UPPER(FD_FIPSCOUNTYCODE) = 'XXX') ) INCIDENT

WHERE ATTR_CODE.CODECAT = 266 

  AND ATTR_CODE.CODEVALUE = INCIDENT.IN_CAUSECODE(+) 

ORDER BY INCIDENT.FDIDSTATE, ATTR_CODE.CODEVALUE
References:
- Query Performance
  - From: Bill Zakrzewski
RE: Query Performance

Other related posts:
