Query slow with XML field in results

[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?

[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).

[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;

Server version information:
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>

[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.