Database Software

Hi Folks,

Below is a query that consumes a large amount of CPU and wonder if there is a better way to do this?

Basically for a top level there can be one or more mid-levels that can have one or more sub-levels and the end result needs to flatten each of these so that we can a complete set of records (one record present for each sub-level in effect).

create or replace view comp_hist_cause_details
as 
select case.case_id,
       extractValue(value(d), '/ComplaintHistory/@subscript') as history_subscript,
       extractValue(value(dets), '/CauseDetails/@subscript') as cause_subscript,
       extractValue(value(dets), '/CauseDetails/Cause') as Cause,
       extractValue(value(dets), '/CauseDetails/CauseDesc') as CauseDesc,
       extractValue(value(dets), '/CauseDetails/CauseSubCategory') as CauseSubCategory,
       extractValue(value(dets), '/CauseDetails/CauseSubCategoryDesc') as CauseSubCategoryDesc,
       extractValue(value(dets), '/CauseDetails/CauseSubCategoryOther') as CauseSubCategoryOther,
       extractValue(value(dets), '/CauseDetails/GCRCause') as GCRCause,
       extractValue(value(dets), '/CauseDetails/GCRCauseDesc') as GCRCauseDesc,
       extractValue(value(dets), '/CauseDetails/GCRSubCause') as GCRSubCause,
       extractValue(value(dets), '/CauseDetails/GCRSubCauseDesc') as GCRSubCauseDesc,
       extractValue(value(dets), '/CauseDetails/IsIncidentCause') as IsIncidentCause,
       extractValue(value(dets), '/CauseDetails/IsPrimaryCause') as IsPrimaryCause
from complaint_xml_load,
     XMLTABLE ('$doc/CBA-FW-GCRComplaints-Work-CreateComplaint/item'
         passing cases as "doc"
         columns case_id          varchar2(128) path 'pzInsKey',
                 ComplaintHistory XMLType       path 'ComplaintHistory'
              ) as  case,
     table(XMLSequence(extract(case.ComplaintHistory, '/')))d,
     table(XMLSequence(extract(value(d), '/ComplaintHistory/CauseDetails'))) dets;

So we find each case, then in that we get all the complaint history as a nested table, then inside of that we get the cause details for each of those.

The base table is created using Object Relational, so other queries that flatten to the mid-level work well.

On this the version of the DB is:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Due to other restrictions on the target database we can only go up to 10.2.0.3.

Thoughts please?

Kind Regards

Keith

Edited by: mdrake on Dec 15, 2010 8:10 PM