This discussion is archived
2 Replies Latest reply: Nov 18, 2012 7:49 PM by user8941550 RSS

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

user8941550 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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..

Legend

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