This discussion is archived
1 Reply Latest reply: Oct 9, 2012 4:13 AM by odie_63 RSS

Tuning xml

944750 Newbie
Currently Being Moderated
Hi,
I have 2 tables as below (in similar format) having CLOB type column and these are range partitioned on the value_date column.

CREATE TABLE I_TAB_A
(
STG_RECORD_ID NUMBER NOT NULL,
STG_LOAD_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE NOT NULL,
STG_STATUS_FLAG VARCHAR2(1 CHAR) DEFAULT 'V' NOT NULL,
VALUE_DATE DATE,
MESSAGE_XML CLOB NOT NULL
)

And, i am creating a view to select from these 2 tables. but following select is taking long time. Can someone suggest me how i can tune this select ?

oracle DB and client version are as below:
============================================

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>


============================================

--CREATE OR REPLACE VIEW V_A_MOV
--AS
SELECT glc.stg_record_id                stg_record_id
, glc.value_date stg_cob_date
, glc.msg_type msg_type
, mh.business_date business_date
, mh.event_datetime event_datetime
, SUBSTR(mh.time_context_ref,1,255) time_context_ref
, mh.e_transfer_id e_transfer_id
, mh.e_transfer_id_1 e_transfer_id_1
, gi.setmt_status setmt_status
, glc.msg_type||gi.setmt_id setmt_id
, DECODE (glc.msg_type, 'GEQ', 'Glacier Equities'
, 'Glacier Credit/Rates') setmt_system_name
, rgi1.business_line
, rgi1.ops_team
, gi.ext_gateway external_gateway
, nt.value_date value_date
, nt.value_date_1 value_date_1
, nt.payer_pty_ref payer_pty_ref
, nt.rec_pty_ref rec_pty_ref
, nt.setmt_ccy setmt_ccy
, nt.net_setmt_amount net_setmt_amount
, nt.netted_payment netted_payment
, nt.setmt_instruction_type setmt_instruction_type
, nt.swift_mt swift_mt
, si.correspondent_id correspondent_id
, si.correspondent_id_type correspondent_id_type
, si.correspondent_acct_name correspondent_acct_name
, si.correspondent_acct_no correspondent_acct_no
, si.correspondent_sub_acct_no correspondent_sub_acct_no
, si.benf_bank_id benf_bank_id
, si.benf_bank_id_type benf_bank_id_type
, si.benf_bank_name benf_bank_name
, si.benf_bank_acct_no benf_bank_acct_no
, si.benf_bank_sub_acct_no benf_bank_sub_acct_no
, si.benf_bank_ref_text benf_bank_ref_text
, si.benficiary_name benficiary_name
, si.benficiary_acct_no benficiary_acct_no
, si.benficiary_sub_acct_no benficiary_sub_acct_no
, SUBSTR(TRIM(REPLACE(REPLACE(XMLQUERY('settlementInstruction/beneficiary/routingExplicitDetails/routingAddress/streetAddress/streetLine' PASSING sa.COLUMN_VALUE RETURNING CONTENT)
,'<streetLine>' , ''), '</streetLine>' , '')),1,2000) benficiary_street_line
, si.benficiary_city benficiary_city
, si.benficiary_state benficiary_state
, si.benficiary_country benficiary_country
, si.benficiary_postalCode benficiary_postalCode
, si.benficiary_id benficiary_id
, si.benficiary_id_type benficiary_id_type
, si.benficiary_name_1 benficiary_name_1
, si.benficiary_acct_no_1 benficiary_acct_no_1
, si.benficiary_sub_acct_no_1 benficiary_sub_acct_no_1
, SUBSTR(TRIM(REPLACE(REPLACE(XMLQUERY('settlementInstruction/beneficiary/routingIdsAndExplicitDetails/routingAddress/streetAddress/streetLine' PASSING sa.COLUMN_VALUE RETURNING CONTENT)
,'<streetLine>' , ''), '</streetLine>' , '')),1,2000) benficiary_street_line_1
, si.benficiary_city_1 benficiary_city_1
, si.benficiary_state_1 benficiary_state_1
, si.benficiary_country_1 benficiary_country_1
, si.benficiary_postalCode_1 benficiary_postalCode_1
, pty.book_ent_persp book_ent_persp
, pty.party_id_1 party_id_1
, pty.party_id_2 party_id_2
, UPPER(pty.party_internal_1) party_internal_1
, UPPER(pty.party_internal_2) party_internal_2
, UPPER(pty.party_role_1) party_role_1
, UPPER(pty.party_role_2) party_role_2
, pty.party_1_name_1 party_1_name_1
, pty.party_1_name_2 party_1_name_2
, pty.party_2_name_1 party_2_name_1
, pty.party_2_name_2 party_2_name_2
, pty.party_3_name_1 party_3_name_1
, pty.party_3_name_2 party_3_name_2
, pty.party_4_name_1 party_4_name_1
, pty.party_4_name_2 party_4_name_2
, pty.party_5_name_1 party_5_name_1
, pty.party_5_name_2 party_5_name_2
, pty.party_1_id_1 party_1_id_1
, pty.party_1_id_2 party_1_id_2
, pty.party_2_id_1 party_2_id_1
, pty.party_2_id_2 party_2_id_2
, pty.party_3_id_1 party_3_id_1
, pty.party_3_id_2 party_3_id_2
, pty.party_4_id_1 party_4_id_1
, pty.party_4_id_2 party_4_id_2
, pty.party_5_id_1 party_5_id_1
, pty.party_5_id_2 party_5_id_2
, UPPER(pty.party_1_id_type_1) party_1_id_type_1
, UPPER(pty.party_1_id_type_2) party_1_id_type_2
, UPPER(pty.party_2_id_type_1) party_2_id_type_1
, UPPER(pty.party_2_id_type_2) party_2_id_type_2
, UPPER(pty.party_3_id_type_1) party_3_id_type_1
, UPPER(pty.party_3_id_type_2) party_3_id_type_2
, UPPER(pty.party_4_id_type_1) party_4_id_type_1
, UPPER(pty.party_4_id_type_2) party_4_id_type_2
, UPPER(pty.party_5_id_type_1) party_5_id_type_1
, UPPER(pty.party_5_id_type_2) party_5_id_type_2
FROM (select glc1.*, 'GEQ' msg_type from i_tab_a glc1 where value_date = :l_cob_date
UNION ALLL
select glc2.*, 'GCR' msg_type from i_tab_b glc2 where value_date = :l_cob_date) glc
, XMLTABLE( 'emml/messageHeader' PASSING XMLTYPE(glc.MESSAGE_XML)
COLUMNS business_date VARCHAR2(255) PATH 'businessDate/text()'
, event_datetime VARCHAR2(255) PATH 'eventDateTime/text()'
, time_context_ref VARCHAR2(255) PATH 'eventDateTime/@timeContextReference'
, e_transfer_id VARCHAR2(255) PATH 'sourceSystemMessageIdentifier'
, e_transfer_id_1 VARCHAR2(255) PATH 'sourceSystemMessageInstigator'
) mh
, XMLTABLE( 'emml/newTransfer/settlementInformation/settlementInstruction' PASSING XMLTYPE(glc.MESSAGE_XML)) sa
, XMLTABLE( 'emml/transferEventHeader' PASSING XMLTYPE(glc.MESSAGE_XML)
COLUMNS setmt_id VARCHAR2(255) PATH 'transferGroupIdentifier/transferGroupId'
, setmt_system_name VARCHAR2(255) PATH 'transferGroupIdentifier/systemReference'
, setmt_status VARCHAR2(255) PATH 'additionalComments'
, ext_gateway VARCHAR2(255) PATH 'externalGateway'
, group_class_scheme VARCHAR2(255) PATH 'transferGroupIdentifier/transferGroupClassificationScheme'
) gi
, ( SELECT glc.stg_record_id stg_record_id
, MAX(CASE
WHEN rgi.group_type = 'Operations Processing Group' THEN rgi.ops_team
END) ops_team
, MAX(CASE
WHEN rgi.group_type = 'Reporting Unit' THEN rgi.ops_team
END) business_line
FROM (select glc1.*, 'GEQ' msg_type from i_glc_e_transfer_eq glc1 where value_date = '05-OCT-2012'
UNION ALL
select glc2.*, 'GCR' msg_type from i_glc_e_transfer_cr glc2 where value_date = '05-OCT-2012') glc
, XMLTABLE('emml/transferEventHeader/reportingGroupIdentifier' PASSING XMLTYPE(glc.MESSAGE_XML)
COLUMNS ops_team VARCHAR2(255) PATH 'reportingGroupId'
, group_type VARCHAR2(255) PATH 'reportingGroupClassificationScheme'
) rgi
GROUP BY glc.stg_record_id ) rgi1
, XMLTABLE( 'emml/newTransfer' PASSING XMLPARSE(glc.MESSAGE_XML)
COLUMNS value_date DATE PATH 'adjustedSettlementDate'
, value_date_1 VARCHAR2(255) PATH 'tradeAttributes/tradeDate/text()'
, payer_pty_ref VARCHAR2(255) PATH 'cashMovement/payerPartyReference'
, rec_pty_ref VARCHAR2(255) PATH 'cashMovement/receiverPartyReference'
, setmt_ccy VARCHAR2(255) PATH 'cashMovement/settlementAmount/currency'
, net_setmt_amount VARCHAR2(255) PATH 'cashMovement/settlementAmount/amount'
, setmt_instruction_type VARCHAR2(255) PATH 'settlementDetails/settlementInstructionType'
, swift_mt VARCHAR2(255) PATH 'settlementDetails/settlementProcessingInstruction'
, netted_payment VARCHAR2(255) PATH 'settlementDetails/nettedCashflow'
) nt
, XMLTABLE( 'emml/newTransfer/settlementInformation/settlementInstruction' PASSING XMLTYPE(glc.MESSAGE_XML)
COLUMNS correspondent_id VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingIds/routingId'
, correspondent_id_type VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
, correspondent_acct_name VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingName'
, correspondent_acct_no VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingAccountNumber'
, correspondent_sub_acct_no VARCHAR2(255) PATH 'correspondentInformation/routingIdsAndExplicitDetails/routingSubAccountNumber'
, benf_bank_id VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingIds/routingId'
, benf_bank_id_type VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
, benf_bank_name VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingName'
, benf_bank_acct_no VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingAccountNumber'
, benf_bank_sub_acct_no VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingSubAccountNumber'
, benf_bank_ref_text VARCHAR2(255) PATH 'beneficiaryBank/routingIdsAndExplicitDetails/routingReferenceText'
, benficiary_name VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingName'
, benficiary_acct_no VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAccountNumber'
, benficiary_sub_acct_no VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingSubAccountNumber'
, benficiary_city VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/city'
, benficiary_state VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/state'
, benficiary_country VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/country'
, benficiary_postalCode VARCHAR2(255) PATH 'beneficiary/routingExplicitDetails/routingAddress/postalCode'
, benficiary_id VARCHAR2(255) PATH 'beneficiary//routingIdsAndExplicitDetails/routingIds/routingId'
, benficiary_id_type VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingIds/routingIdScheme'
, benficiary_name_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingName'
, benficiary_acct_no_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAccountNumber'
, benficiary_sub_acct_no_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingSubAccountNumber'
, benficiary_city_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/city'
, benficiary_state_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/state'
, benficiary_country_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/country'
, benficiary_postalCode_1 VARCHAR2(255) PATH 'beneficiary/routingIdsAndExplicitDetails/routingAddress/postalCode'
) si
, XMLTABLE('emml' PASSING XMLTYPE(glc.MESSAGE_XML)
COLUMNS book_ent_persp VARCHAR2(255) PATH 'newTransfer/bookingEntityPerspective'
, party_internal_1 VARCHAR2(255) PATH 'newTransfer/party[1]/isPartyInternal'
, party_internal_2 VARCHAR2(255) PATH 'newTransfer/party[2]/isPartyInternal'
, party_id_1 VARCHAR2(255) PATH 'newTransfer/party[1]/@id'
, party_id_2 VARCHAR2(255) PATH 'newTransfer/party[2]/@id'
, party_role_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyRole'
, party_role_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyRole'
, party_1_name_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyName'
, party_1_name_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyName'
, party_2_name_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyName'
, party_2_name_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyName'
, party_3_name_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyName'
, party_3_name_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyName'
, party_4_name_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyName'
, party_4_name_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyName'
, party_5_name_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyName'
, party_5_name_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyName'
, party_1_id_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyId'
, party_1_id_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyId'
, party_2_id_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyId'
, party_2_id_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyId'
, party_3_id_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyId'
, party_3_id_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyId'
, party_4_id_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyId'
, party_4_id_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyId'
, party_5_id_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyId'
, party_5_id_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyId'
, party_1_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[1]/partyIdClassificationScheme'
, party_1_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[1]/partyIdentifier[2]/partyIdClassificationScheme'
, party_2_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[1]/partyIdClassificationScheme'
, party_2_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[2]/partyIdentifier[2]/partyIdClassificationScheme'
, party_3_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[1]/partyIdClassificationScheme'
, party_3_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[3]/partyIdentifier[2]/partyIdClassificationScheme'
, party_4_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[1]/partyIdClassificationScheme'
, party_4_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[4]/partyIdentifier[2]/partyIdClassificationScheme'
, party_5_id_type_1 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[1]/partyIdClassificationScheme'
, party_5_id_type_2 VARCHAR2(255) PATH 'newTransfer/party[5]/partyIdentifier[2]/partyIdClassificationScheme'
) pty
WHERE rgi1.stg_record_id = glc.stg_record_id
;
  • 1. Re: Tuning xml
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Can someone suggest me how i can tune this select ?
    With such an extensive query, I think there's absolutely no chance of optimizing anything without migrating the column to Binary XMLType.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points