2 Replies Latest reply: Nov 18, 2012 9:49 PM by user8941550 RSS

    Convrtd to Invterval Part-> ORA-03113: end-of-file on communication channel

    user8941550
      Hi all,

      I had a table as Interval Partitioned. In order to create XML- Xpath indexes on it, I converted it to Range Partitioned table.
      I am able to create the XPATH indexes but I get the error: ORA-03113: end-of-file on communication channel


      - When I revert the code to Interval Partitioned without the XMLIndex, it works fine(although takes time as no XML Index)
      - When I convert table to non partitioned table, create the XML Index, it works fine.
      But I need the partitons, so when I create the partitioned table I get the error.




      CREATE TABLE INT_PART_TABLE
      (
      DB_ID VARCHAR2(10 BYTE),
      xML_mESSAGE SYS.XMLTYPE,
      LOAD_TIMESTAMP TIMESTAMP(6)
      )
      XMLTYPE xML_mESSAGE STORE AS BINARY XML
      PARTITION BY RANGE (LOAD_TIMESTAMP)
      (
      PARTITION MAX VALUES LESS THAN (TIMESTAMP' 2013-06-01 00:00:00')
      TABLESPACE CSTR_STG_DATA
      )
      NOCOMPRESS
      NOCACHE
      ENABLE ROW MOVEMENT;
      /

      BEGIN
      DBMS_XMLINDEX.dropparameter('Indx_Par');
      END;

      BEGIN
      DBMS_XMLINDEX.REGISTERPARAMETER(
      'Indx_Par',
      'PATH TABLE Table1
      PATHS (INCLUDE ( /abc:field1/xyz:field2
      /abc:field1/def:field2
      )
      NAMESPACE MAPPING ( xmlns:abc="ABCD"
      xmlns:def="DEFG"
      xmlns:xyz="XYZA"
      ))
      ');
      end;


      create index INDX_XPATHS on "INT_PART_TABLE" (XML_MESSAGE) indextype is xdb.xmlindex
      parameters ('PARAM Indx_Par') local;


      Now if I execute the following statement in

      SELECT T.xML_mESSAGE
      FROM INT_PART_TABLE1 T
      WHERE XMLEXISTS (
      '
      declare namespace abc="ABCD";
      declare namespacedef="DEFG";
      declare namespace xyz="XYZA";
      let $tt as xs:boolean := fn:exists($p/main/id = ("144283","9085802")])
      return if ($tt) then true()
      else ()'
      PASSING T.xML_mESSAGE AS "p");



      - Is there any other way of writing this Select statement, which may work?
      - Any other thing I need to take care of when defining the table and partitions script so that I don't get this error?
        • 1. Re: Convrtd to Invterval Part-> ORA-03113: end-of-file on communication channel
          odie_63
          Hi,

          I think it's time you give a clear (and working) test case so that we can safely try to reproduce the issue.
          What you've given so far has syntax error and name mismatch.

          So please :

          - database version (SELECT * FROM v$version)
          - complete sequence of DLLs
          - some sample XML documents (it doesn't have to be the real ones, but at least something realistic)

          Thanks in advance.

          '
          declare namespace abc="ABCD";
          declare namespacedef="DEFG";
          declare namespace xyz="XYZA";
          let $tt as xs:boolean := fn:exists($p/main/id = ("144283","9085802")])
          return if ($tt) then true()
          else ()'
          Why all that stuff? You don't have to return a boolean.

          The following works for me on 11.2.0.3 :
          SQL> CREATE TABLE int_part_table (
            2    db_id          VARCHAR2(10)
            3  , xml_message    XMLTYPE
            4  , load_timestamp TIMESTAMP
            5  )
            6  XMLTYPE xml_message STORE AS BINARY XML
            7  PARTITION BY RANGE (load_timestamp) (
            8    PARTITION MAX VALUES LESS THAN (timestamp '2013-06-01 00:00:00')
            9  )
           10  NOCOMPRESS
           11  NOCACHE
           12  ENABLE ROW MOVEMENT;
           
          Table created
           
          SQL> insert into int_part_table values (1, xmltype('<main><id>144283</id></main>'), sysdate);
           
          1 row inserted
           
          SQL> insert into int_part_table values (1, xmltype('<main><id>9085802</id></main>'), sysdate);
           
          1 row inserted
           
          SQL> insert into int_part_table values (1, xmltype('<main><id>1</id></main>'), sysdate);
           
          1 row inserted
           
          SQL> commit;
           
          Commit complete
           
          SQL> create index int_part_table_uix on int_part_table (xml_message)
            2  indextype is xdb.xmlindex
            3  parameters (
            4  'PATH TABLE INT_PART_TABLE_PT
            5  PATHS ( INCLUDE ( /main/id ) )')
            6  local;
           
          Index created
           
          SQL> SELECT xml_message
            2  FROM int_part_table
            3  WHERE XMLExists(
            4         '/main[id=("144283","9085802")]'
            5         PASSING xml_message
            6       )
            7  ;
          
          XML_MESSAGE
          ---------------------------------------------------------------------------------------------------------------------------------------
          
          <main>
            <id>144283</id>
          </main>
          
          <main>
            <id>9085802</id>
          </main>
          
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3517234298
          
          ---------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
          ---------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                       |                              |     1 |   155 |    34   (6)| 00:00:01 |       |       |
          |   1 |  NESTED LOOPS                          |                              |     1 |   155 |    34   (6)| 00:00:01 |       |       |
          |   2 |   VIEW                                 | VW_SQ_1                      |     1 |    25 |    32   (4)| 00:00:01 |       |       |
          |   3 |    HASH UNIQUE                         |                              |     1 |    47 |         |             |       |       |
          |*  4 |     HASH JOIN SEMI                     |                              |     1 |    47 |    32   (4)| 00:00:01 |       |       |
          |   5 |      PARTITION SYSTEM SINGLE           |                              |     2 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
          |*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| INT_PART_TABLE_PT            |     2 |    90 |     2   (0)| 00:00:01 |     1 |     1 |
          |*  7 |        INDEX SKIP SCAN                 | SYS117585_INT_PART__PIKEY_IX |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |   8 |      COLLECTION ITERATOR PICKLER FETCH | XQSEQUENCEFROMXMLTYPE        |  8168 | 16336 |    29   (0)| 00:00:01 |       |       |
          |*  9 |   TABLE ACCESS BY USER ROWID           | INT_PART_TABLE               |     1 |   130 |     1   (0)| 00:00:01 | ROWID | ROWID |
          ---------------------------------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             4 - access("SYS_P3"."VALUE"=SYS_XQ_UPKXML2SQL(VALUE(KOKBF$),2,1,0) AND
                        SUBSTRB("VALUE",1,1599)=SUBSTRB(SYS_XQ_UPKXML2SQL(VALUE(KOKBF$),2,1,0),1,1599))
             6 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
             7 - access("SYS_P3"."PATHID"=HEXTORAW('704E') )
                 filter("SYS_P3"."PATHID"=HEXTORAW('704E') )
             9 - filter("ITEM_6"=TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE",0,7,65535,"INT_PART_TABLE".ROWID))
          
          Note
          -----
             - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
          SQL> SELECT xml_message
            2  FROM int_part_table
            3  WHERE XMLExists(
            4         '/main[id="144283" or id="9085802"]'
            5         PASSING xml_message
            6       )
            7  ;
          
          XML_MESSAGE
          ------------------------------------------------------------------------------------------------------------------------------------------------
          
          <main>
            <id>144283</id>
          </main>
          
          <main>
            <id>9085802</id>
          </main>
          
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 3748936130
          
          -----------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
          -----------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                         |                              |     1 |   155 |    11  (10)| 00:00:01 |       |       |
          |   1 |  NESTED LOOPS                            |                              |     1 |   155 |    11  (10)| 00:00:01 |       |       |
          |   2 |   VIEW                                   | VW_SQ_1                      |     2 |    50 |     8   (0)| 00:00:01 |       |       |
          |   3 |    HASH UNIQUE                           |                              |     2 |   180 |         |             |       |       |
          |   4 |     CONCATENATION                        |                              |       |       |         |             |       |       |
          |   5 |      NESTED LOOPS                        |                              |       |       |         |             |       |       |
          |   6 |       NESTED LOOPS                       |                              |     1 |    90 |     4   (0)| 00:00:01 |       |       |
          |   7 |        PARTITION SYSTEM SINGLE           |                              |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| INT_PART_TABLE_PT            |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |*  9 |          INDEX SKIP SCAN                 | SYS117585_INT_PART__PIKEY_IX |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |  10 |        PARTITION SYSTEM SINGLE           |                              |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |* 11 |         INDEX RANGE SCAN                 | SYS117585_INT_PART__PIKEY_IX |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |* 12 |       TABLE ACCESS BY LOCAL INDEX ROWID  | INT_PART_TABLE_PT            |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |  13 |      NESTED LOOPS                        |                              |       |       |         |             |       |       |
          |  14 |       NESTED LOOPS                       |                              |     1 |    90 |     4   (0)| 00:00:01 |       |       |
          |  15 |        PARTITION SYSTEM SINGLE           |                              |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID| INT_PART_TABLE_PT            |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |* 17 |          INDEX SKIP SCAN                 | SYS117585_INT_PART__PIKEY_IX |     3 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |  18 |        PARTITION SYSTEM SINGLE           |                              |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |* 19 |         INDEX RANGE SCAN                 | SYS117585_INT_PART__PIKEY_IX |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
          |* 20 |       TABLE ACCESS BY LOCAL INDEX ROWID  | INT_PART_TABLE_PT            |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
          |* 21 |   TABLE ACCESS BY USER ROWID             | INT_PART_TABLE               |     1 |   130 |     1   (0)| 00:00:01 | ROWID | ROWID |
          -----------------------------------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             8 - filter("SYS_P5"."VALUE"='9085802' AND SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1 AND SUBSTRB("VALUE",1,1599)='9085802')
             9 - access("SYS_P5"."PATHID"=HEXTORAW('704E') )
                 filter("SYS_P5"."PATHID"=HEXTORAW('704E') )
            11 - access("SYS_P5"."RID"="SYS_P3"."RID" AND "SYS_P3"."PATHID"=HEXTORAW('0BBD')  AND
                        "SYS_P3"."ORDER_KEY"<"SYS_P5"."ORDER_KEY")
                 filter(SYS_ORDERKEY_DEPTH("SYS_P3"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P5"."ORDER_KEY") AND
                        TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE",0,7,65535,"SYS_P3"."RID")=TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE_PT",0,7,65535,ROWI
                        D) AND "SYS_P5"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P3"."ORDER_KEY"))
            12 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
            16 - filter("SYS_P5"."VALUE"='144283' AND SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1 AND SUBSTRB("VALUE",1,1599)='144283' AND
                        (LNNVL("SYS_P5"."VALUE"='9085802') OR LNNVL("SYS_P5"."PATHID"=HEXTORAW('704E') ) OR
                        LNNVL(SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1) OR LNNVL(SUBSTRB("VALUE",1,1599)='9085802')))
            17 - access("SYS_P5"."PATHID"=HEXTORAW('704E') )
                 filter("SYS_P5"."PATHID"=HEXTORAW('704E') )
            19 - access("SYS_P5"."RID"="SYS_P3"."RID" AND "SYS_P3"."PATHID"=HEXTORAW('0BBD')  AND
                        "SYS_P3"."ORDER_KEY"<"SYS_P5"."ORDER_KEY")
                 filter(SYS_ORDERKEY_DEPTH("SYS_P3"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P5"."ORDER_KEY") AND
                        TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE",0,7,65535,"SYS_P3"."RID")=TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE_PT",0,7,65535,ROWI
                        D) AND "SYS_P5"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P3"."ORDER_KEY"))
            20 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
            21 - filter("ITEM_2"=TBL$OR$IDX$PART$NUM("DEV"."INT_PART_TABLE",0,7,65535,"INT_PART_TABLE".ROWID))
          I asked in one of your other threads if /main/id was unique per XML document.
          If so, you can use a simple function-based index instead of the XMLIndex :
          SQL> drop index int_part_table_uix;
          
          Index dropped.
          
          SQL> create index int_part_table_ix1 on int_part_table (
            2    xmlcast(
            3      xmlquery('/main/id' passing XML_MESSAGE returning content)
            4      as varchar2(10)
            5    )
            6  );
          
          Index created.
          
          SQL> SELECT xml_message
            2  FROM int_part_table
            3  WHERE XMLCast(
            4          XMLQuery('/main/id' PASSING xml_message RETURNING CONTENT)
            5          AS VARCHAR2(10)
            6        )
            7  IN ('144283', '9085802');
          
          XML_MESSAGE
          ---------------------------------------------------------------------------------------------------------------------------------
          
          <main>
            <id>144283</id>
          </main>
          
          <main>
            <id>9085802</id>
          </main>
          
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2864653096
          
          --------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
          --------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                    |                    |     2 |   236 |     2   (0)| 00:00:01 |       |       |
          |   1 |  INLIST ITERATOR                    |                    |       |       |            |       |  |       |
          |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| INT_PART_TABLE     |     2 |   236 |     2   (0)| 00:00:01 |     1 |     1 |
          |*  3 |    INDEX RANGE SCAN                 | INT_PART_TABLE_IX1 |     2 |       |     1   (0)| 00:00:01 |       |       |
          --------------------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             3 - access(CAST(EXTRACTVALUE(SYS_MAKEXML(0,"SYS_NC00003$"),'/main/id',null,0,0,524293,1073874944) AS
                        varchar2(10)   )='144283' OR CAST(EXTRACTVALUE(SYS_MAKEXML(0,"SYS_NC00003$"),'/main/id',null,0,0,524293,1073874944
                        ) AS varchar2(10)   )='9085802')
          • 2. Re: Convrtd to Invterval Part-> ORA-03113: end-of-file on communication channel
            user8941550
            Hi Odie,

            Thanks for the help. Your way of writing select by avoiding the fn:exists has helped in the performance.

            But when I try to incorporate the function based index thing using:

            create index int_part_table_ix1 on int_part_table (
            xmlcast(
            xmlquery('/main[a=b]/id' passing XML_MESSAGE returning content) -- As I have some conditions to be matched in main and id..Then the ID is unique after those conditions are met
            as varchar2(10)
            )
            );

            But I get the error :

            ORA-02070: database does not support correlation in this context

            Is there any workaround for this.

            Thanks..