Page 1 of 1
Query slow with XML field in results
Posted: Mon 13 Oct 2014 19:21
by azuckerman
I have a query executing on a table that has a CLOB column type that contains XML data.
Using this query (without the CLOB column), everything is fast and smooth:
SELECT si.INVOICE_NUM, si.INVOICE_DT, si.EXPIRE_DT, si.CLI_LEGAL_NAME, si.READY
FROM INVOICE si
However, adding the CLOB column causes significant delays to moving through the results:
SELECT si.INVOICE_NUM, si.INVOICE_DT, si.EXPIRE_DT, si.CLI_LEGAL_NAME, si.READY, si.INVOICE_XML
FROM INVOICE si
What can I do to make the scrolling more like the first query when using the second?
Re: Query slow with XML field in results
Posted: Tue 14 Oct 2014 07:25
by alexa
Could you please provide us the CREATE definition of the table and a couple of records with data so we can reproduce it on our side?
Also, please provide us the version of the Oracle server and Oracle client (if applicable).
Re: Query slow with XML field in results
Posted: Tue 14 Oct 2014 15:47
by azuckerman
I cannot post the data as it is private invoice data.
Code: Select all
CREATE TABLE INVOICE (
INVOICE_NUM VARCHAR2(8 BYTE) NOT NULL,
INVOICE_DT DATE NOT NULL,
EXPIRE_DT DATE,
CLI_NUM VARCHAR2(8 BYTE) NOT NULL,
CLI_LEGAL_NAME VARCHAR2(50 BYTE) NOT NULL,
CREATE_DT DATE DEFAULT TRUNC(sysdate) NOT NULL,
PRINTED NUMBER(*, 0) DEFAULT 0 NOT NULL,
ONHOLD NUMBER(*, 0) DEFAULT 0 NOT NULL,
INVOICE_XML CLOB NOT NULL,
READY NUMBER(*, 0) DEFAULT 0 NOT NULL,
CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_NUM, INVOICE_DT)
)
TABLESPACE USERS
STORAGE (INITIAL 64 K
NEXT 1 M
MAXEXTENTS UNLIMITED)
LOGGING;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Client version information:
Windows 7 64bit
11.2.0
64bit
An example of the XML:
Code: Select all
<?xml version="1.0" encoding="utf-16"?>
<Invoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Cli_Num>01234567</Cli_Num>
<Comm_Id>987654</Comm_Id>
<Invoice_Num>00000001</Invoice_Num>
<Invoice_Dt>2014-10-01T00:00:00</Invoice_Dt>
<Cli_Legal_Name>COMPANY NAME</Cli_Legal_Name>
<Contact_Full_Name>CONTACT NAME</Contact_Full_Name>
<Contact_Full_Address>PO BOX 12345
AUSTIN TX 78701</Contact_Full_Address>
<Expire_Dt>2014-10-30T00:00:00</Expire_Dt>
<todays_Dt>2014-10-14T16:54:22.7291554-05:00</todays_Dt>
<Total_Amt>$420.00</Total_Amt>
<Credit_Amt>$0.00</Credit_Amt>
<Pending_Amt>$0.00</Pending_Amt>
<Total_Amt_To_Pay>$420.00</Total_Amt_To_Pay>
<Late_Amt>$630.00</Late_Amt>
<Delinquent_Amt>$840.00</Delinquent_Amt>
<Account_Types>
<anyType xsi:type="xsd:string">0911</anyType>
</Account_Types>
<Details>
<anyType xsi:type="Invoice_Detail">
<Acct_Num>0000001</Acct_Num>
<Facility_Name>LOCATION NAME</Facility_Name>
<Facility_Street />
<Facility_Full_Address />
<Acct_Type_Cd>0911</Acct_Type_Cd>
<Acct_Type_Desc>PRODUCT</Acct_Type_Desc>
<_Fee_Amt>420</_Fee_Amt>
<_Credit_Amt>0</_Credit_Amt>
<_Late_Amt>630</_Late_Amt>
<_Delinquent_Amt>840</_Delinquent_Amt>
<Worksheets_Included>N/A</Worksheets_Included>
<Expire_Dt>0001-01-01T00:00:00</Expire_Dt>
<Fee_Amt>$420.00</Fee_Amt>
<Credit_Amt>$0.00</Credit_Amt>
<Late_Amt>$630.00</Late_Amt>
<Delinquent_Amt>$840.00</Delinquent_Amt>
</anyType>
</Details>
<Details_X />
<Details_Y />
<Details_Z />
</Invoice>
Re: Query slow with XML field in results
Posted: Wed 15 Oct 2014 08:36
by alexa
Thank you for the reply. We are currently investigating this issue and will get back to you as soon as possible.
I cannot post the data as it is private invoice data.
You can send a reply straight to our support system at supportATdevartDOTcom, so we will keep further correspondence with you on this issue via e-mail.