This discussion is archived
14 Replies Latest reply: Oct 24, 2012 8:11 AM by Marco Gralike RSS

Exercise in Range Partitioning, Structured and Unstructured XMLIndexes

Marco Gralike Oracle ACE Director
Currently Being Moderated
Just to share this exercise, hoping it might help people out there...

I left the errors (there are multiple) and some small copy/paste issues "as is" so by really reading it what is in front of you, you might see what I have attempted to do or to figure it out in the various small variations in the statements. That said, the errors rea insightful by their own (see issues in datatyping for selects and in the UXI en SXI XMLIndexes).

-- Setting up the environment
SQL> --
SQL> connect / as sysdba
Connected.
SQL> --
SQL> drop user marco cascade;

User dropped.

SQL> --
SQL> create user marco identified by marco account unlock;

User created.

SQL> grant dba, xdbadmin to marco;

Grant succeeded.

SQL> --
SQL> connect marco/marco
Connected.
SQL> --
SQL> set pages 5000
SQL> set lines 200
SQL> set long 10000
SQL> set verify off
SQL> set trimspool on
SQL> set echo on
SQL> --
SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
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 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SQL> --
SQL> CALL dbms_xmlschema.deleteschema('http://www.myserver.com/schema.v1.xsd',4);
CALL dbms_xmlschema.deleteschema('http://www.myserver.com/schema.v1.xsd',4)
     *
ERROR at line 1:
ORA-31000: Resource 'http://www.myserver.com/schema.v1.xsd' is not an XDB schema document
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 122
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 106
ORA-06512: at line 1


SQL> --
SQL> declare
  2    V_XMLSCHEMA XMLTYPE := xmltype('<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  3                                xmlns:xdb="http://xmlns.oracle.com/xdb"
  4                                targetNamespace="http://www.myserver.com/schema.v1.xsd"
  5                                elementFormDefault="qualified">
  6                            <xsd:element name="Employees">
  7                              <xsd:complexType>
  8                               <xsd:sequence>
  9                                 <xsd:element name="SSN" type="xsd:string"/>
 10                                 <xsd:element name="Name" type="xsd:string"/>
 11                                 <xsd:element name="DateOfBirth" type="xsd:date"/>
 12                                 <xsd:element name="EmployeeType" type="xsd:string"/>
 13                                 <xsd:element name="Salary" type="xsd:integer"/>
 14                               </xsd:sequence>
 15                              </xsd:complexType>
 16                             </xsd:element>
 17                           </xsd:schema>');
 18  BEGIN
 19   DBMS_XMLSCHEMA.registerSchema
 20   ( SCHEMAURL  => 'http://www.myserver.com/schema.v1.xsd',
 21       SCHEMADOC  => V_XMLSCHEMA,
 22       LOCAL        => FALSE,  -- local
 23       GENTYPES   => FALSE,  -- generate object types
 24       GENBEAN    => FALSE,  -- no java beans
 25       GENTABLES  => FALSE,  -- generate object tables
 26       FORCE        => FALSE,  -- cyclic dependancies in xsd
 27       OPTIONS    => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
 28       OWNER        => USER
 29   );
 30  END;
 31  /

PL/SQL procedure successfully completed.

SQL> --
SQL> CREATE TABLE TEST
  2    ( ID     NUMBER(10),
  3        DDAY     DATE,
  4        XMLDOC XMLTYPE
  5    )
  6    SEGMENT CREATION IMMEDIATE
  7    NOCOMPRESS NOLOGGING
  8   TABLESPACE USERS
  9        XMLTYPE COLUMN XMLDOC STORE AS SECUREFILE BINARY XML
 10        (TABLESPACE USERS NOCOMPRESS  KEEP_DUPLICATES)
 11          XMLSCHEMA "http://www.myserver.com/schema.v1.xsd" ELEMENT "Employees"
 12   PARTITION BY RANGE(DDAY)
 13    INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN (USERS)
 14    ( PARTITION RANGE_PART_01 VALUES  LESS THAN ('01-JAN-2011') );

Table created.

SQL> --
SQL> INSERT INTO TEST
  2  VALUES
  3  ( 1,
  4   (SYSDATE-365),
  5    XMLTYPE('<?xml version="1.0" ?>
  6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
  7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
  8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  9             <SSN>777777777</SSN>
 10             <Name>Mark D Dake</Name>
 11             <DateOfBirth>1960-01-01</DateOfBirth>
 12             <EmployeeType>Full Time</EmployeeType>
 13             <Salary>4000</Salary>
 14              </Employees>
 15              ')
 16  );

1 row created.

SQL> --
SQL> INSERT INTO TEST
  2  VALUES
  3  ( 2,
  4   (SYSDATE),
  5    XMLTYPE('<?xml version="1.0" ?>
  6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
  7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
  8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  9             <SSN>73737373</SSN>
 10             <Name>Cody Adams</Name>
 11             <DateOfBirth>1999-08-01</DateOfBirth>
 12             <EmployeeType>Part Time</EmployeeType>
 13             <Salary>6500</Salary>
 14              </Employees>
 15              ')
 16  );

1 row created.

SQL> --
SQL> INSERT INTO TEST
  2  VALUES
  3  ( 3,
  4   (SYSDATE+365),
  5    XMLTYPE('<?xml version="1.0" ?>
  6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
  7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
  8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  9             <SSN>333333333</SSN>
 10             <Name>Marco Gralike</Name>
 11             <DateOfBirth>2005-02-01</DateOfBirth>
 12             <EmployeeType>Night Shift</EmployeeType>
 13             <Salary>1500</Salary>
 14              </Employees>
 15              ')
 16  );

1 row created.

SQL> --
SQL> SELECT partition_name, tablespace_name, high_value
  2    FROM user_tab_partitions;

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------
RANGE_PART_01                  USERS                          TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P181                       USERS                          TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P184                       USERS                          TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                              MI:SS', 'NLS_CALENDAR=GREGORIAN')


3 rows selected.

SQL> --
SQL> CREATE INDEX XIDX_TEST_01
  2    ON TEST(XMLDOC)
  3    INDEXTYPE IS XDB.XMLINDEX
  4    LOCAL
  5    PARAMETERS
  6        ('PATHS (INCLUDE (/Employees/SSN /Employees/DateOfBirth)
  7                NAMESPACE MAPPING (xmlns="http://www.myserver.com/schema.v1.xsd")
  8              )
  9          PATH TABLE XDIX_TEST_PATHTABLE     (TABLESPACE USERS)
 10          PIKEY      INDEX XDIX_TEST_PIKEY   (TABLESPACE USERS)
 11          PATH ID    INDEX XDIX_TEST_PATH_ID (TABLESPACE USERS)
 12          ORDER KEY  INDEX XDIX_TEST_KEY     (TABLESPACE USERS)
 13          VALUE      INDEX XDIX_TEST_VALUE   (TABLESPACE USERS)
 14          ASYNC (SYNC ALWAYS) STALE (FALSE)'
 15         );
  ON TEST(XMLDOC)
     *
ERROR at line 2:
ORA-14762: Domain index creation on interval partitioned tables is not permitted


SQL> --
SQL> prompt ==========================================
==========================================
SQL> prompt Alas...I would like to be lazy...Dont you?
Alas...I would like to be lazy...Dont you?
SQL> prompt ==========================================
==========================================
SQL> --
SQL> DROP TABLE TEST PURGE
  2  /

Table dropped.
Edited by: Marco Gralike on Jun 8, 2011 1:42 AM
  • 1. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    -- Again, now not using interval range partitioning
    SQL> --
    SQL> CREATE TABLE TEST
      2    ( ID     NUMBER(10),
      3        DDAY     DATE,
      4        XMLDOC XMLTYPE
      5    )
      6    SEGMENT CREATION IMMEDIATE
      7    NOCOMPRESS NOLOGGING
      8   TABLESPACE USERS
      9        XMLTYPE COLUMN XMLDOC STORE AS SECUREFILE BINARY XML
     10        (TABLESPACE USERS NOCOMPRESS  KEEP_DUPLICATES)
     11        XMLSCHEMA "http://www.myserver.com/schema.v1.xsd" ELEMENT "Employees"
     12   PARTITION BY RANGE(DDAY)
     13       ( PARTITION RANGE_IN_2010       VALUES LESS THAN
     14               (TO_DATE('01-JAN-2011','DD-MON-YYYY')) TABLESPACE USERS
     15       , PARTITION RANGE_IN_2011       VALUES LESS THAN
     16               (TO_DATE('01-JAN-2012','DD-MON-YYYY')) TABLESPACE USERS
     17       , PARTITION RANGE_LATER_THEN_2012 VALUES LESS THAN
     18               (MAXVALUE) TABLESPACE USERS
     19       );
    
    Table created.
    
    SQL> --
    SQL> INSERT INTO TEST
      2  VALUES
      3  ( 1,
      4   (SYSDATE-365),
      5    XMLTYPE('<?xml version="1.0" ?>
      6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
      7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
      8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      9             <SSN>777777777</SSN>
     10             <Name>Mark D Dake</Name>
     11             <DateOfBirth>1960-01-01</DateOfBirth>
     12             <EmployeeType>Full Time</EmployeeType>
     13             <Salary>4000</Salary>
     14              </Employees>
     15              ')
     16  );
    
    1 row created.
    
    SQL> --
    SQL> INSERT INTO TEST
      2  VALUES
      3  ( 2,
      4   (SYSDATE),
      5    XMLTYPE('<?xml version="1.0" ?>
      6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
      7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
      8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      9             <SSN>73737373</SSN>
     10             <Name>Cody Adams</Name>
     11             <DateOfBirth>1999-08-01</DateOfBirth>
     12             <EmployeeType>Part Time</EmployeeType>
     13             <Salary>6500</Salary>
     14              </Employees>
     15              ')
     16  );
    
    1 row created.
    
    SQL> --
    SQL> INSERT INTO TEST
      2  VALUES
      3  ( 3,
      4   (SYSDATE+365),
      5    XMLTYPE('<?xml version="1.0" ?>
      6            <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
      7              xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
      8              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      9             <SSN>333333333</SSN>
     10             <Name>Marco Gralike</Name>
     11             <DateOfBirth>2005-02-01</DateOfBirth>
     12             <EmployeeType>Night Shift</EmployeeType>
     13             <Salary>1500</Salary>
     14              </Employees>
     15              ')
     16  );
    
    1 row created.
    
    SQL> --
    SQL> SELECT partition_name, tablespace_name, high_value
      2    FROM user_tab_partitions;
    
    PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
    ------------------------------ ------------------------------ --------------------------------------------------
    RANGE_IN_2010                  USERS                          TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                  MI:SS', 'NLS_CALENDAR=GREGORIAN')
    
    RANGE_IN_2011                  USERS                          TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                                                  MI:SS', 'NLS_CALENDAR=GREGORIAN')
    
    RANGE_LATER_THEN_2012          USERS                          MAXVALUE
    
    3 rows selected.
    
    SQL> --
    SQL> CREATE INDEX XIDX_TEST_01
      2    ON TEST(XMLDOC)
      3    INDEXTYPE IS XDB.XMLINDEX
      4    LOCAL
      5    PARAMETERS
      6        ('PATHS (INCLUDE (/Employees/SSN /Employees/DateOfBirth)
      7                NAMESPACE MAPPING (xmlns="http://www.myserver.com/schema.v1.xsd")
      8              )
      9          PATH TABLE XDIX_TEST_PATHTABLE     (TABLESPACE USERS)
     10          PIKEY      INDEX XDIX_TEST_PIKEY   (TABLESPACE USERS)
     11          PATH ID    INDEX XDIX_TEST_PATH_ID (TABLESPACE USERS)
     12          ORDER KEY  INDEX XDIX_TEST_KEY     (TABLESPACE USERS)
     13          VALUE      INDEX XDIX_TEST_VALUE   (TABLESPACE USERS)
     14          ASYNC (SYNC ALWAYS) STALE (FALSE)'
     15         );
    
    Index created.
    
    SQL> --
    SQL> ALTER INDEX XIDX_TEST_01
      2  PARAMETERS ('ADD_GROUP GROUP SAL_DATE_GROUP
      3              XMLTABLE CONTENT_SAL_DATE_TABLE
      4              XMLNAMESPACES(DEFAULT ''http://www.myserver.com/schema.v1.xsd'' )
      5              , ''/Employees''
      6              COLUMNS
      7                SALARY NUMBER(10)     PATH ''Salary''
      8              , RDATE  DATE      PATH ''DateOfBirth''
      9              , CDATE  VARCHAR2(10) PATH ''DateOfBirth''
     10            ');
    
    Index altered.
    
    SQL> --
    SQL> /*
    SQL> BEGIN
    SQL>   DBMS_XMLINDEX.registerParameter('SAL_DATE_PARAM'
    SQL>                          , 'ADD_GROUP GROUP SAL_DATE_GROUP
    SQL>             XMLTABLE CONTENT_SAL_DATE_TABLE
    SQL>             XMLNAMESPACES(DEFAULT ''http://www.myserver.com/schema.v1.xsd'' )
    SQL>             , ''/Employees''
    SQL>             COLUMNS
    SQL>               SALARY NUMBER(10)  PATH ''Salary''
    SQL>             , RDATE  DATE        PATH ''DateOfBirth''
    SQL>             , CDATE  VARCHAR(10) PATH ''DateOfBirth''
    SQL>                ');
    SQL> END;
    SQL> /
    SQL> --
    SQL> ALTER INDEX XIDX_TEST_01 PARAMETERS('PARAM SAL_DATE_PARAM');
    SQL> --
    SQL> */
    SQL> -- Delete if exist
    SQL> CALL dbms_stats.delete_table_stats(user,'TEST');
    
    Call completed.
    
    SQL> CALL dbms_stats.delete_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> CALL dbms_stats.delete_table_stats(user,'XDIX_TEST_PATHTABLE');
    
    Call completed.
    
    SQL> -- Create new ones
    SQL> CALL dbms_stats.gather_table_stats(user,'TEST');
    
    Call completed.
    
    SQL> CALL dbms_stats.gather_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> CALL dbms_stats.gather_table_stats(user,'XDIX_TEST_PATHTABLE');
    
    Call completed.
  • 2. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    -- Checks
    SQL> --
    SQL> -- Checks
    SQL> --
    SQL> col high_value for a85
    SQL> col xmldoc for a70
    SQL> col schema_url for a40
    SQL> --
    SQL> select table_name, partition_name, high_value
      2    from user_tab_partitions
      3   order by table_name
      4  /
    
    TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
    ------------------------------ ------------------------------ -------------------------------------------------------------------------------------
    CONTENT_SAL_DATE_TABLE         RANGE_IN_2011
    CONTENT_SAL_DATE_TABLE         RANGE_IN_2010
    CONTENT_SAL_DATE_TABLE         RANGE_LATER_THEN_2012
    TEST                           RANGE_LATER_THEN_2012          MAXVALUE
    TEST                           RANGE_IN_2010                  TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
    TEST                           RANGE_IN_2011                  TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
    XDIX_TEST_PATHTABLE            RANGE_LATER_THEN_2012
    XDIX_TEST_PATHTABLE            RANGE_IN_2010
    XDIX_TEST_PATHTABLE            RANGE_IN_2011
    
    9 rows selected.
    
    SQL> --
    SQL> select index_name, partition_name, partition_position
      2  from user_ind_partitions
      3  order by index_name
      4  /
    
    INDEX_NAME                     PARTITION_NAME                 PARTITION_POSITION
    ------------------------------ ------------------------------ ------------------
    SYS_IL0000081447C00004$$       SYS_IL_P191                                     2
    SYS_IL0000081447C00004$$       SYS_IL_P192                                     3
    SYS_IL0000081447C00004$$       SYS_IL_P190                                     1
    XDIX_TEST_KEY                  RANGE_IN_2011                                   2
    XDIX_TEST_KEY                  RANGE_IN_2010                                   1
    XDIX_TEST_KEY                  RANGE_LATER_THEN_2012                           3
    XDIX_TEST_PATH_ID              RANGE_IN_2010                                   1
    XDIX_TEST_PATH_ID              RANGE_IN_2011                                   2
    XDIX_TEST_PATH_ID              RANGE_LATER_THEN_2012                           3
    XDIX_TEST_PIKEY                RANGE_IN_2011                                   2
    XDIX_TEST_PIKEY                RANGE_LATER_THEN_2012                           3
    XDIX_TEST_PIKEY                RANGE_IN_2010                                   1
    XDIX_TEST_VALUE                RANGE_IN_2010                                   1
    XDIX_TEST_VALUE                RANGE_LATER_THEN_2012                           3
    XDIX_TEST_VALUE                RANGE_IN_2011                                   2
    XIDX_TEST_01                   RANGE_IN_2011                                   2
    XIDX_TEST_01                   RANGE_IN_2010                                   1
    XIDX_TEST_01                   RANGE_LATER_THEN_2012                           3
    
    18 rows selected.
    
    SQL> --
    SQL> col high_value for a50
    SQL> col parameters for a70
    SQL> --
    SQL> select index_name, high_value, parameters
      2  from user_ind_partitions
      3  order by index_name
      4  /
    
    INDEX_NAME                     HIGH_VALUE                                         PARAMETERS
    ------------------------------ -------------------------------------------------- ----------------------------------------------------------------------
    SYS_IL0000081447C00004$$       TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                   MI:SS', 'NLS_CALENDAR=GREGORIAN')
    
    SYS_IL0000081447C00004$$       MAXVALUE
    SYS_IL0000081447C00004$$       TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:
                                   MI:SS', 'NLS_CALENDAR=GREGORIAN')
    
    XDIX_TEST_KEY
    XDIX_TEST_KEY
    XDIX_TEST_KEY
    XDIX_TEST_PATH_ID
    XDIX_TEST_PATH_ID
    XDIX_TEST_PATH_ID
    XDIX_TEST_PIKEY
    XDIX_TEST_PIKEY
    XDIX_TEST_PIKEY
    XDIX_TEST_VALUE
    XDIX_TEST_VALUE
    XDIX_TEST_VALUE
    XIDX_TEST_01                   TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24: PATHS (INCLUDE (/Employees/SSN /Employees/DateOfBirth)
                                   MI:SS', 'NLS_CALENDAR=GREGORIAN')                                 NAMESPACE MAPPING (xmlns="http://www.myserver.com/schem
                                                                                      a.v1.xsd")
                                                                                                   )
                                                                                            PATH TABLE XDIX_TEST_PATHTABLE     (TABLESPACE USERS)
                                                                                            PIKEY      INDEX XDIX_TEST_PIKEY   (TABLESPACE USERS)
                                                                                            PATH ID    INDEX XDIX_TEST_PATH_ID (TABLESPACE USERS)
                                                                                            ORDER KEY  INDEX XDIX_TEST_KEY     (TABLESPACE USERS)
                                                                                            VALUE      INDEX XDIX_TEST_VALUE   (TABLESPACE USERS)
                                                                                            ASYNC (SYNC ALWAYS) STALE (FALSE)
    
    XIDX_TEST_01                   TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24: PATHS (INCLUDE (/Employees/SSN /Employees/DateOfBirth)
                                   MI:SS', 'NLS_CALENDAR=GREGORIAN')                                 NAMESPACE MAPPING (xmlns="http://www.myserver.com/schem
                                                                                      a.v1.xsd")
                                                                                                   )
                                                                                            PATH TABLE XDIX_TEST_PATHTABLE     (TABLESPACE USERS)
                                                                                            PIKEY      INDEX XDIX_TEST_PIKEY   (TABLESPACE USERS)
                                                                                            PATH ID    INDEX XDIX_TEST_PATH_ID (TABLESPACE USERS)
                                                                                            ORDER KEY  INDEX XDIX_TEST_KEY     (TABLESPACE USERS)
                                                                                            VALUE      INDEX XDIX_TEST_VALUE   (TABLESPACE USERS)
                                                                                            ASYNC (SYNC ALWAYS) STALE (FALSE)
    
    XIDX_TEST_01                   MAXVALUE                                           PATHS (INCLUDE (/Employees/SSN /Employees/DateOfBirth)
                                                                                                     NAMESPACE MAPPING (xmlns="http://www.myserver.com/schem
                                                                                      a.v1.xsd")
                                                                                                   )
                                                                                            PATH TABLE XDIX_TEST_PATHTABLE     (TABLESPACE USERS)
                                                                                            PIKEY      INDEX XDIX_TEST_PIKEY   (TABLESPACE USERS)
                                                                                            PATH ID    INDEX XDIX_TEST_PATH_ID (TABLESPACE USERS)
                                                                                            ORDER KEY  INDEX XDIX_TEST_KEY     (TABLESPACE USERS)
                                                                                            VALUE      INDEX XDIX_TEST_VALUE   (TABLESPACE USERS)
                                                                                            ASYNC (SYNC ALWAYS) STALE (FALSE)
    
    
    18 rows selected.
    
    SQL> --
    SQL> select index_name, table_name, type, index_type, path_table_name
      2  from user_xml_indexes
      3  /
    
    INDEX_NAME                     TABLE_NAME                     TYPE       INDEX_TYPE                  PATH_TABLE_NAME
    ------------------------------ ------------------------------ ---------- --------------------------- ------------------------------
    XIDX_TEST_01                   TEST                           BINARY     STRUCTURED and UNSTRUCTURED XDIX_TEST_PATHTABLE
    
    1 row selected.
    
    SQL> --
    SQL> select schema_url, local, hier_type, binary
      2    from user_xml_schemas
      3  /
    
    SCHEMA_URL                               LOC HIER_TYPE   BIN
    ---------------------------------------- --- ----------- ---
    http://www.myserver.com/schema.v1.xsd    NO  CONTENTS    YES
    
    1 row selected.
    
    SQL> --
    SQL> select index_name, table_name, uniqueness, index_type
      2    from user_indexes
      3   order by table_name
      4  ;
    
    INDEX_NAME                     TABLE_NAME                     UNIQUENES INDEX_TYPE
    ------------------------------ ------------------------------ --------- ---------------------------
    SYS_C0012841                   CONTENT_SAL_DATE_TABLE         UNIQUE    NORMAL
    SYS81459_81498_RID_IDX         CONTENT_SAL_DATE_TABLE         NONUNIQUE NORMAL
    SYS_IL0000081447C00004$$       TEST                           UNIQUE    LOB
    XIDX_TEST_01                   TEST                           NONUNIQUE FUNCTION-BASED DOMAIN
    XDIX_TEST_VALUE                XDIX_TEST_PATHTABLE            NONUNIQUE FUNCTION-BASED NORMAL
    XDIX_TEST_KEY                  XDIX_TEST_PATHTABLE            NONUNIQUE NORMAL
    XDIX_TEST_PATH_ID              XDIX_TEST_PATHTABLE            NONUNIQUE NORMAL
    XDIX_TEST_PIKEY                XDIX_TEST_PATHTABLE            NONUNIQUE NORMAL
    
    8 rows selected.
    
    SQL> --
    SQL> set autotrace on
    SQL> --
    SQL> select * from test
      2  /
    
            ID DDAY               XMLDOC
    ---------- ------------------ ----------------------------------------------------------------------
             1 08-JUN-10          <?xml version="1.0"?>
                                  <Employees xmlns="http://www.myserver.com/schema.v1.xsd" xsi:schemaLoc
                                  ation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/
                                  schema.v1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                    <SSN>777777777</SSN>
                                    <Name>Mark D Dake</Name>
                                    <DateOfBirth>1960-01-01</DateOfBirth>
                                    <EmployeeType>Full Time</EmployeeType>
                                    <Salary>4000</Salary>
                                  </Employees>
    
             2 08-JUN-11          <?xml version="1.0"?>
                                  <Employees xmlns="http://www.myserver.com/schema.v1.xsd" xsi:schemaLoc
                                  ation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/
                                  schema.v1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                    <SSN>73737373</SSN>
                                    <Name>Cody Adams</Name>
                                    <DateOfBirth>1999-08-01</DateOfBirth>
                                    <EmployeeType>Part Time</EmployeeType>
                                    <Salary>6500</Salary>
                                  </Employees>
    
             3 07-JUN-12          <?xml version="1.0"?>
                                  <Employees xmlns="http://www.myserver.com/schema.v1.xsd" xsi:schemaLoc
                                  ation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/
                                  schema.v1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                    <SSN>333333333</SSN>
                                    <Name>Marco Gralike</Name>
                                    <DateOfBirth>2005-02-01</DateOfBirth>
                                    <EmployeeType>Night Shift</EmployeeType>
                                    <Salary>1500</Salary>
                                  </Employees>
    
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2199936479
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     3 |   912 |     6   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE ALL|      |     3 |   912 |     6   (0)| 00:00:01 |     1 |     3 |
    |   2 |   TABLE ACCESS FULL | TEST |     3 |   912 |     6   (0)| 00:00:01 |     1 |     3 |
    --------------------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
             72  recursive calls
              0  db block gets
             97  consistent gets
              0  physical reads
              0  redo size
          34509  bytes sent via SQL*Net to client
           4913  bytes received via SQL*Net from client
             25  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              3  rows processed
  • 3. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    -- Checks, part 2.

    I left the errors just so you realize why...
    SQL> select xt.salary as "SXI_SALARY"
      2         , xt.rdate  as "SXI_REAL_DATE"
      3         , xt.cdate  as "SXI_CHAR_DATE"
      4    from test t
      5    ,    xmltable
      6              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      7            , '/Employees'
      8            PASSING t.xmldoc
      9            COLUMNS
     10              SALARY NUMBER(10)   PATH 'Salary'
     11            , RDATE  DATE           PATH 'DateOfBirth'
     12            , CDATE  VARCHAR2(10) PATH 'DateOfBirth'
     13            ) xt
     14  /
    
    SXI_SALARY SXI_REAL_DATE      SXI_CHAR_D
    ---------- ------------------ ----------
          4000 01-JAN-60          1960-01-01
          6500 01-AUG-99          1999-08-01
          1500 01-FEB-05          2005-02-01
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3665239887
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                        |     1 |    56 |     9   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS               |                        |     1 |    56 |     9   (0)| 00:00:01 |       |       |
    |   2 |   PARTITION SYSTEM ALL      |                        |     3 |   132 |     6   (0)| 00:00:01 |     1 |     3 |
    |   3 |    TABLE ACCESS FULL        | CONTENT_SAL_DATE_TABLE |     3 |   132 |     6   (0)| 00:00:01 |     1 |     3 |
    |*  4 |   TABLE ACCESS BY USER ROWID| TEST                   |     1 |    12 |     1   (0)| 00:00:01 | ROWID | ROWID |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_DAT
                  E_TABLE",0,0,0,"SYS_ALIAS_0".ROWID))
    
    
    Statistics
    ----------------------------------------------------------
            120  recursive calls
              0  db block gets
            352  consistent gets
              0  physical reads
              0  redo size
            797  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              3  rows processed
    
    SQL> --
    SQL> select xt.name as "UXI_NAME"
      2         , xt.ssn  as "UXI_SSN"
      3    from test t
      4    ,    xmltable
      5              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      6            , '/Employees'
      7            PASSING t.xmldoc
      8            COLUMNS
      9              SSN     NUMBER(10)   PATH 'SSN'
     10            , NAME     VARCHAR2(30) PATH 'Name'
     11            ) xt
     12  /
    
    UXI_NAME                          UXI_SSN
    ------------------------------ ----------
    Mark D Dake                     777777777
    Cody Adams                       73737373
    Marco Gralike                   333333333
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1308358721
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   1 |  NESTED LOOPS        |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   2 |   PARTITION RANGE ALL|      |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   3 |    TABLE ACCESS FULL | TEST |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   4 |   XPATH EVALUATION   |      |       |       |            |          |       |       |
    ---------------------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
             44  recursive calls
              0  db block gets
            304  consistent gets
              0  physical reads
              0  redo size
            705  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              3  rows processed
    
    SQL> --
    SQL> select xt.name   as "UXI_NAME"
      2         , xt.salary as "SXI_SALARY"
      3    from test t
      4    ,    xmltable
      5              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      6            , '/Employees'
      7            PASSING t.xmldoc
      8            COLUMNS
      9              NAME      VARCHAR2(30) PATH 'Name'
     10            , SALARY NUMBER(10)   PATH 'Salary'
     11            ) xt
     12  /
    
    UXI_NAME                       SXI_SALARY
    ------------------------------ ----------
    Mark D Dake                          4000
    Cody Adams                           6500
    Marco Gralike                        1500
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1308358721
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   1 |  NESTED LOOPS        |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   2 |   PARTITION RANGE ALL|      |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   3 |    TABLE ACCESS FULL | TEST |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   4 |   XPATH EVALUATION   |      |       |       |            |          |       |       |
    ---------------------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
             44  recursive calls
              0  db block gets
            304  consistent gets
              0  physical reads
              0  redo size
            697  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              3  rows processed
    
    SQL> --
    SQL> create unique index sec_ind_sal_cnt on CONTENT_SAL_DATE_TABLE(salary)
      2  /
    
    Index created.
    
    SQL> --
    SQL> CALL dbms_stats.gather_table_stats(user,'TEST');
    
    Call completed.
    
    SQL> CALL dbms_stats.gather_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> CALL dbms_stats.gather_table_stats(user,'XDIX_TEST_PATHTABLE');
    
    Call completed.
    
    SQL> --
    SQL> select xt.name   as "UXI_NAME"
      2         , xt.salary as "SXI_SALARY"
      3    from test t
      4    ,    xmltable
      5              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      6            , '/Employees'
      7            PASSING t.xmldoc
      8            COLUMNS
      9              NAME      VARCHAR2(30) PATH 'Name'
     10            , SALARY NUMBER(10)   PATH 'Salary'
     11            ) xt
     12  /
    
    UXI_NAME                       SXI_SALARY
    ------------------------------ ----------
    Mark D Dake                          4000
    Cody Adams                           6500
    Marco Gralike                        1500
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1308358721
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   1 |  NESTED LOOPS        |      | 24504 |  7059K|    89   (0)| 00:00:02 |       |       |
    |   2 |   PARTITION RANGE ALL|      |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   3 |    TABLE ACCESS FULL | TEST |     3 |   879 |     6   (0)| 00:00:01 |     1 |     3 |
    |   4 |   XPATH EVALUATION   |      |       |       |            |          |       |       |
    ---------------------------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
             99  recursive calls
              0  db block gets
            356  consistent gets
              0  physical reads
              0  redo size
            697  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              3  sorts (memory)
              0  sorts (disk)
              3  rows processed
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10  /
    
    SXI_SALARY
    ----------
          4000
          6500
          1500
    
    3 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3665239887
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                        |     1 |    37 |     9   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS               |                        |     1 |    37 |     9   (0)| 00:00:01 |       |       |
    |   2 |   PARTITION SYSTEM ALL      |                        |     3 |    75 |     6   (0)| 00:00:01 |     1 |     3 |
    |   3 |    TABLE ACCESS FULL        | CONTENT_SAL_DATE_TABLE |     3 |    75 |     6   (0)| 00:00:01 |     1 |     3 |
    |*  4 |   TABLE ACCESS BY USER ROWID| TEST                   |     1 |    12 |     1   (0)| 00:00:01 | ROWID | ROWID |
    ----------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_DAT
                  E_TABLE",0,0,0,"SYS_ALIAS_0".ROWID))
    
    
    Statistics
    ----------------------------------------------------------
             55  recursive calls
              0  db block gets
            298  consistent gets
              0  physical reads
              0  redo size
            590  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              3  rows processed
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10   WHERE trunc(DDAY) = trunc(SYSDATE)
     11  /
    
    SXI_SALARY
    ----------
          6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3817504763
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                        |     1 |    45 |     7   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS                       |                        |       |       |            |          |       |       |
    |   2 |   NESTED LOOPS                      |                        |     1 |    45 |     7   (0)| 00:00:01 |       |       |
    |   3 |    PARTITION RANGE ALL              |                        |     1 |    20 |     6   (0)| 00:00:01 |     1 |     3 |
    |*  4 |     TABLE ACCESS FULL               | TEST                   |     1 |    20 |     6   (0)| 00:00:01 |     1 |     3 |
    |*  5 |    INDEX RANGE SCAN                 | SYS81459_81498_RID_IDX |     1 |       |     0   (0)| 00:00:01 |       |       |
    |   6 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONTENT_SAL_DATE_TABLE |     1 |    25 |     1   (0)| 00:00:01 | ROWID | ROWID |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - filter(TRUNC(INTERNAL_FUNCTION("DDAY"))=TRUNC(SYSDATE@!))
       5 - access("T".ROWID="SYS_ALIAS_0"."RID")
           filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_DATE_TABLE"
                  ,0,0,0,"SYS_ALIAS_0".ROWID))
    
    
    Statistics
    ----------------------------------------------------------
             55  recursive calls
              0  db block gets
            298  consistent gets
              0  physical reads
              0  redo size
            528  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> --
    SQL> select xt.name   as "UXI_NAME"
      2         , xt.salary as "SXI_SALARY"
      3    from test t
      4    ,    xmltable
      5              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      6            , '/Employees'
      7            PASSING t.xmldoc
      8            COLUMNS
      9              NAME      VARCHAR2(30) PATH 'Name'
     10            , SALARY NUMBER(10)   PATH 'Salary'
     11            ) xt
     12   WHERE trunc(DDAY) = trunc(SYSDATE)
     13  /
    
    UXI_NAME                       SXI_SALARY
    ------------------------------ ----------
    Cody Adams                           6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1308358721
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |   245 | 74235 |    35   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS        |      |   245 | 74235 |    35   (0)| 00:00:01 |       |       |
    |   2 |   PARTITION RANGE ALL|      |     1 |   301 |     6   (0)| 00:00:01 |     1 |     3 |
    |*  3 |    TABLE ACCESS FULL | TEST |     1 |   301 |     6   (0)| 00:00:01 |     1 |     3 |
    |   4 |   XPATH EVALUATION   |      |       |       |            |          |       |       |
    ---------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter(TRUNC(INTERNAL_FUNCTION("DDAY"))=TRUNC(SYSDATE@!))
    
    
    Statistics
    ----------------------------------------------------------
             44  recursive calls
              0  db block gets
            304  consistent gets
              0  physical reads
              0  redo size
            609  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
  • 4. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    -- Checks, part 3
    SQL> create unique index sec_ind_Cdate_cnt on CONTENT_SAL_DATE_TABLE(Cdate)
      2  /
    
    Index created.
    
    SQL> create unique index sec_ind_Rdate_cnt on CONTENT_SAL_DATE_TABLE(Rdate)
      2  /
    
    Index created.
    
    SQL> --
    SQL> begin
      2    for i in 4..10000
      3    loop
      4    --
      5        INSERT INTO TEST
      6          VALUES
      7          ( i,
      8           (SYSDATE),
      9            XMLTYPE('<?xml version="1.0" ?>
     10                  <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
     11                 xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
     12                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     13                   <SSN>73737373</SSN>
     14                   <Name>Mark Drake</Name>
     15                   <DateOfBirth>1999-08-01</DateOfBirth>
     16                   <EmployeeType>FULLTIME</EmployeeType>
     17                   <Salary>7500</Salary>
     18                 </Employees>
     19                 ')
     20          );
     21       --
     22       end loop;
     23  end;
     24  /
    begin
    *
    ERROR at line 1:
    ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
    ORA-00001: unique constraint (MARCO.SEC_IND_CDATE_CNT) violated
    ORA-06512: at line 5
    
    
    SQL> --
    SQL> drop index sec_ind_Cdate_cnt;
    
    Index dropped.
    
    SQL> drop index sec_ind_Rdate_cnt;
    
    Index dropped.
    
    SQL> drop index sec_ind_sal_cnt;
    
    Index dropped.
    
    SQL> --
    SQL> begin
      2    for i in 4..10000
      3    loop
      4    --
      5        INSERT INTO TEST
      6          VALUES
      7          ( i,
      8           (SYSDATE),
      9            XMLTYPE('<?xml version="1.0" ?>
     10                  <Employees xmlns="http://www.myserver.com/schema.v1.xsd"
     11                 xsi:schemaLocation="http://www.myserver.com/schema.v1.xsd  http://www.myserver.com/schema.v1.xsd"
     12                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     13                   <SSN>73737373</SSN>
     14                   <Name>Mark Drake</Name>
     15                   <DateOfBirth>1999-08-01</DateOfBirth>
     16                   <EmployeeType>FULLTIME</EmployeeType>
     17                   <Salary>7500</Salary>
     18                 </Employees>
     19                 ')
     20          );
     21    --
     22    end loop;
     23  end;
     24  /
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> create index sec_ind_sal_cnt on CONTENT_SAL_DATE_TABLE(salary)
      2  /
    
    Index created.
    
    SQL> -- Delete if exist
    SQL> CALL dbms_stats.delete_table_stats(user,'TEST');
    
    Call completed.
    
    SQL> CALL dbms_stats.delete_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> CALL dbms_stats.delete_table_stats(user,'XDIX_TEST_PATHTABLE');
    
    Call completed.
    
    SQL> -- Create histogram on CONTENT table
    SQL> ALTER SESSION
      2    SET events '31150 trace name context forever, level 0x8';
    
    Session altered.
    
    SQL> CALL dbms_stats.gather_table_stats( user
      2                             , 'CONTENT_SAL_DATE_TABLE'
      3                             , estimate_percent=>100
      4                             , method_opt=>'for all columns size auto')
      5  /
    
    Call completed.
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10    WHERE xt.salary = 6500
     11  /
    
    SXI_SALARY
    ----------
          6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3808586183
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                        |    25 |   925 |   137   (2)| 00:00:02 |       |       |
    |   1 |  PARTITION SYSTEM ALL|                        |    25 |   925 |   137   (2)| 00:00:02 |     1 |     3 |
    |*  2 |   HASH JOIN          |                        |    25 |   925 |   137   (2)| 00:00:02 |       |       |
    |*  3 |    TABLE ACCESS FULL | CONTENT_SAL_DATE_TABLE |  2500 | 62500 |    30   (0)| 00:00:01 |     1 |     3 |
    |   4 |    TABLE ACCESS FULL | TEST                   | 10092 |   118K|   105   (0)| 00:00:02 |     1 |     3 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T".ROWID="SYS_ALIAS_0"."RID")
       3 - filter("SYS_ALIAS_0"."SALARY"=6500)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
            122  recursive calls
              1  db block gets
            903  consistent gets
              0  physical reads
            248  redo size
            528  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              3  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> --
    SQL> CALL dbms_stats.delete_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10    WHERE xt.salary = 6500
     11  /
    
    SXI_SALARY
    ----------
          6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1675051440
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                        |     1 |    49 |     3   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS                       |                        |     1 |    49 |     3   (0)| 00:00:01 |       |       |
    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONTENT_SAL_DATE_TABLE |     1 |    37 |     2   (0)| 00:00:01 | ROWID | ROWID |
    |*  3 |    INDEX RANGE SCAN                 | SEC_IND_SAL_CNT        |     1 |       |     1   (0)| 00:00:01 |       |       |
    |*  4 |   TABLE ACCESS BY USER ROWID        | TEST                   |     1 |    12 |     1   (0)| 00:00:01 | ROWID | ROWID |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("SYS_ALIAS_0"."SALARY"=6500)
       4 - filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_DATE_TABLE"
                  ,0,0,0,"SYS_ALIAS_0".ROWID))
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            488  consistent gets
              0  physical reads
              0  redo size
            528  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> --
    SQL> CALL dbms_stats.delete_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> --
    SQL> drop index sec_ind_sal_cnt;
    
    Index dropped.
    
    SQL> create unique index sec_ind_RID_sal_cnt on CONTENT_SAL_DATE_TABLE(rid, salary);
    
    Index created.
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10    WHERE xt.salary = 6500
     11  /
    
    SXI_SALARY
    ----------
          6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3613503880
    
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                     |     1 |    49 |    16   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS               |                     |     1 |    49 |    16   (0)| 00:00:01 |       |       |
    |*  2 |   INDEX FAST FULL SCAN      | SEC_IND_RID_SAL_CNT |     2 |    74 |    14   (0)| 00:00:01 |       |       |
    |*  3 |   TABLE ACCESS BY USER ROWID| TEST                |     1 |    12 |     1   (0)| 00:00:01 | ROWID | ROWID |
    -------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("SYS_ALIAS_0"."SALARY"=6500)
       3 - filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_
                  DATE_TABLE",0,0,0,"SYS_ALIAS_0".ROWID))
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
            109  recursive calls
              0  db block gets
            488  consistent gets
             40  physical reads
              0  redo size
            528  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> --
    SQL> create index sec_ind_sal_cnt on CONTENT_SAL_DATE_TABLE(salary)
      2  /
    
    Index created.
    
    SQL> CALL dbms_stats.delete_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    SQL> ALTER SESSION
      2    SET events '31150 trace name context forever, level 0x8';
    
    Session altered.
    
    SQL> CALL dbms_stats.gather_table_stats( user
      2                             , 'CONTENT_SAL_DATE_TABLE'
      3                             , estimate_percent=>100
      4                             , method_opt=>'for all columns size auto')
      5  /
    
    Call completed.
    
    SQL> --
    SQL> select xt.salary as "SXI_SALARY"
      2    from test t
      3    ,    xmltable
      4              (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      5            , '/Employees'
      6            PASSING t.xmldoc
      7            COLUMNS
      8              SALARY NUMBER(10)   PATH 'Salary'
      9            ) xt
     10    WHERE xt.salary = 6500
     11  /
    
    SXI_SALARY
    ----------
          6500
    
    1 row selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1675051440
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                        |     1 |    37 |     3   (0)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS                       |                        |     1 |    37 |     3   (0)| 00:00:01 |       |       |
    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| CONTENT_SAL_DATE_TABLE |     1 |    25 |     2   (0)| 00:00:01 | ROWID | ROWID |
    |*  3 |    INDEX RANGE SCAN                 | SEC_IND_SAL_CNT        |     1 |       |     1   (0)| 00:00:01 |       |       |
    |*  4 |   TABLE ACCESS BY USER ROWID        | TEST                   |     1 |    12 |     1   (0)| 00:00:01 | ROWID | ROWID |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access("SYS_ALIAS_0"."SALARY"=6500)
       4 - filter(TBL$OR$IDX$PART$NUM("MARCO"."TEST",0,0,0,"T".ROWID)=TBL$OR$IDX$PART$NUM("MARCO"."CONTENT_SAL_DATE_TABLE"
                  ,0,0,0,"SYS_ALIAS_0".ROWID))
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    Statistics
    ----------------------------------------------------------
             58  recursive calls
              0  db block gets
            359  consistent gets
              0  physical reads
              0  redo size
            528  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    
    SQL> set timing on
    SQL> --
    SQL> CALL dbms_stats.gather_table_stats(user,'XDIX_TEST_PATHTABLE');
    
    Call completed.
    
    Elapsed: 00:00:01.21
    SQL> CALL dbms_stats.gather_table_stats(user,'TEST');
    
    Call completed.
    
    Elapsed: 00:00:02.69
    SQL> CALL dbms_stats.gather_table_stats(user,'CONTENT_SAL_DATE_TABLE');
    
    Call completed.
    
    Elapsed: 00:00:00.36
    SQL> --
    SQL> select xt.name  as "UXI_NAME"
      2       , xt.birth as "UXI_BIRTH"
      3    from test t
      4    ,    xmltable
      5           (xmlnamespaces(default 'http://www.myserver.com/schema.v1.xsd')
      6      , '/Employees'
      7      PASSING t.xmldoc
      8      COLUMNS
      9        NAME  NUMBER(10)   PATH 'Name'
     10      , BIRTH DATE         PATH 'DateOfBirth'
     11      ) xt
     12   where xt.birth = to_date('1960-01-01','YYYY-MM-DD')
     13  /
    
      UXI_NAME UXI_BIRTH
    ---------- ------------------
               01-JAN-60
    
    1 row selected.
    
    Elapsed: 00:00:00.58
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3751828343
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                     |    81M|    23G|   220M  (1)|733:35:21 |       |       |
    |*  1 |  FILTER                               |                     |       |       |            |       |          |       |
    |   2 |   NESTED LOOPS                        |                     |    81M|    23G|   272K  (1)| 00:54:30 |       |       |
    |   3 |    PARTITION RANGE ALL                |                     | 10000 |  2939K|   107   (2)| 00:00:02 |     1 |     3 |
    |   4 |     TABLE ACCESS FULL                 | TEST                | 10000 |  2939K|   107   (2)| 00:00:02 |     1 |     3 |
    |   5 |    XPATH EVALUATION                   |                     |       |       |            |       |          |       |
    |   6 |   NESTED LOOPS                        |                     |       |       |            |       |          |       |
    |   7 |    NESTED LOOPS                       |                     |     1 |    84 |     4   (0)| 00:00:01 |       |       |
    |   8 |     PARTITION SYSTEM SINGLE           |                     |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
    |*  9 |      TABLE ACCESS BY LOCAL INDEX ROWID| XDIX_TEST_PATHTABLE |     1 |    41 |     2   (0)| 00:00:01 |   KEY |   KEY |
    |* 10 |       INDEX RANGE SCAN                | XDIX_TEST_PATH_ID   |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
    |  11 |     PARTITION SYSTEM ITERATOR         |                     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
    |* 12 |      INDEX RANGE SCAN                 | XDIX_TEST_PIKEY     |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
    |* 13 |    TABLE ACCESS BY LOCAL INDEX ROWID  | XDIX_TEST_PATHTABLE |     1 |    43 |     2   (0)| 00:00:01 |     1 |     1 |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(CAST(TO_DATE( (SELECT "SYS_P2"."VALUE" FROM "MARCO"."XDIX_TEST_PATHTABLE"
                  "SYS_P2","MARCO"."XDIX_TEST_PATHTABLE" "SYS_P0" WHERE "SYS_P0"."RID"=:B1 AND "SYS_P0"."PATHID"=HEXTORAW('1114')  AND
                  SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1 AND "SYS_P2"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P0"."ORDER_KEY") AND
                  "SYS_P0"."ORDER_KEY"<"SYS_P2"."ORDER_KEY" AND "SYS_P2"."PATHID"=HEXTORAW('1B5A')  AND "SYS_P2"."RID"=:B2 AND
                  SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1 AND "SYS_P2"."RID"="SYS_P0"."RID" AND
                  SYS_ORDERKEY_DEPTH("SYS_P0"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P2"."ORDER_KEY") AND
                  TBL$OR$IDX$PART$NUM("TEST",0,7,65535,"SYS_P0"."RID")=TBL$OR$IDX$PART$NUM("XDIX_TEST_PATHTABLE",0,0,65535,ROWID)),'SYY
                  YY-MM-DD') AS DATE         )=TO_DATE(' 1960-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       9 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
      10 - access("SYS_P0"."PATHID"=HEXTORAW('1114')  AND "SYS_P0"."RID"=:B1)
      12 - access("SYS_P2"."RID"=:B1 AND "SYS_P2"."PATHID"=HEXTORAW('1B5A')  AND
                  "SYS_P0"."ORDER_KEY"<"SYS_P2"."ORDER_KEY" AND "SYS_P2"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P0"."ORDER_KEY"))
           filter("SYS_P2"."RID"="SYS_P0"."RID" AND SYS_ORDERKEY_DEPTH("SYS_P0"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P2
                  "."ORDER_KEY") AND TBL$OR$IDX$PART$NUM("TEST",0,7,65535,"SYS_P0"."RID")=TBL$OR$IDX$PART$NUM("XDIX_TEST_PATHTABLE",0,0
                  ,65535,ROWID))
      13 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
    
    
    Statistics
    ----------------------------------------------------------
            141  recursive calls
              0  db block gets
           2976  consistent gets
              0  physical reads
              0  redo size
            603  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              3  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> spool off
  • 5. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    This exercise was initiated on a question here regarding the DDL syntax of structured xmlindexes on a range partitioned table: http://www.liberidu.com/blog/?p=1805&cpage=1#comment-17931

    Edited by: Marco Gralike on Jun 8, 2011 1:58 AM
  • 6. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    759514 Newbie
    Currently Being Moderated
    After clearing the hurdle of creating local indexes, I tried to add new partitions (previously, I created all partitions before inserting data and creating the local indexes) and
    received the following error:
    ALTER TABLE "ORDER" ADD PARTITION "ORDERS_PART_20110703" VALUES LESS THAN (TO_DATE('2011-07-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
    
    SQL Error: ORA-29874: warning in the execution of ODCIINDEXALTER routine
    ORA-29960: line 1, ORA-30968: invalid XPATH or NAMESPACE option for XML Index
    29874. 00000 - "warning in the execution of ODCIINDEXALTER routine"
    *Cause:  A warning was returned from the ODCIIndexAlter routine.
    *Action: Check to see if the routine has been coded correctly
                 Check the user defined warning log tables for greater details. 
    Would you happen to any idea as to the proper syntax to add a new partition to an existing range partitioned table with local unstructured and structured XMLIndexes?

    *Previously I created a month's worth of daily partitions for the month of June, before inserting and creating indexes. The above error was encountered when I tried to add new partitions to cater for July's input.

    Edited by: tthon on Jun 20, 2011 5:32 PM

    Edited by: tthon on Jun 20, 2011 5:35 PM
  • 7. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Not sure how, what or where, but I would advise to create a support request (SR) with Oracle support.
  • 8. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    BTW, I hope you didn't use because, although it works, I am not sure its supported...
    DBMS_XMLINDEX.REGISTERPARAMETER (
    ‘myIndexParam’,
    ‘PATH TABLE po_ptab
    PATH ID INDEX po_pidx
    ORDER KEY INDEX po_oidx
    VALUE INDEX po_vidx
    PATHS(NAMESPACE MAPPING(xmlns:p=”http://www.example.com/IPO”))
    GROUP MASTERGROUP XMLTABLE PO_TAB
    (”/p:PurchaseOrder”
    COLUMNS
    REFERENCE VARCHAR2(30) PATH ”p:Reference”,
    REQUESTOR VARCHAR2(30) PATH ”p:Requestor” )
    GROUP ITEMGROUP XMLTABLE ITEMGROUP_TAB
    (”/p:PurchaseOrder/p:LineItems/p:LineItem”
    COLUMNS
    LINENUMBER NUMBER(38) PATH ”@p:ItemNumber”,
    QUANTITY NUMBER(38) PATH ”@p:Quantity”,
    DESCRIPTION VARCHAR2(256) PATH ”p:Description” ));
  • 9. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    759514 Newbie
    Currently Being Moderated
    I did indeed use it because my parameter string is more than 1000 characters long and even when I break it up such that each ALTER INDEX is adding only one group, I have a group that by itself, contains more than 1000 characters...
  • 10. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    You will have to contact Oracle Support then for this issue. If it is XQuery related it might be solved by a patch regarding bug "7317171" (Re: "xquery" versus "select xmlquery" and passing clauses

    But nevertheless ask Oracle Support for help, because I think you might be now in a catch22 situation.
  • 11. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Michiel Weggen Newbie
    Currently Being Moderated
    Sorry to dig up this old thread, but I'm running into ORA errors when trying to follow the example code..

    I'm working on 11.2.0.2. The code I'm referring to is the code in the second post, which first creates an unstructured component with the LOCAL parameter and then adding a structured component group. Creating the table and index works fine, but adding the structured component group gives an ORA error:
    table TEST created.
    index XIDX_TEST_01 created.
    
    Error starting at line 1 in command:
    ALTER INDEX XIDX_TEST_01
      PARAMETERS ('ADD_GROUP GROUP SAL_DATE_GROUP
                  XMLTABLE CONTENT_SAL_DATE_TABLE
                  XMLNAMESPACES(DEFAULT ''http://www.myserver.com/schema.v1.xsd'' )
                  , ''/Employees''
                  COLUMNS
                    SALARY NUMBER(10)     PATH ''Salary''
                  , RDATE  DATE      PATH ''DateOfBirth''
                  , CDATE  VARCHAR2(10) PATH ''DateOfBirth''
                ')
    Error report:
    SQL Error: ORA-29874: warning in the execution of ODCIINDEXALTER routine
    ORA-29960: line 1, ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
    29874. 00000 -  "warning in the execution of ODCIINDEXALTER routine"
    *Cause:    A waring was returned from the ODCIIndexAlter routine.
    *Action:   Check to see if the routine has been coded correctly
               Check the user defined warning log tables for greater details.
    The same error occurs when trying to add the group via a PARAM.

    Edited by: Michiel Weggen on 24-okt-2012 6:07
  • 12. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Did you use somewhere in your CREATE TABLE statement
    PARTITION BY SYSTEM
    like something like
    CREATE TABLE test
       ( nr   NUMBER       NOT NULL
       , mark VARCHAR2(10) NOT NULL
       )
    PARTITION BY SYSTEM
    (
       PARTITION p1 TABLESPACE users,
       PARTITION p2 TABLESPACE users
    );
    
    SQL> SELECT partition_name
      2  FROM  user_segments
      3  WHERE segment_name = 'TEST';
    
    PARTITION_NAME
    ------------------------------
    P1
    P2
  • 13. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Michiel Weggen Newbie
    Currently Being Moderated
    Nope.

    I figured out the problem tho, after reading http://www.tzehon.com/2011/06/20/local-structured-xmlindexes/

    It seems that using alter index statements to add xmlindexes on partitioned tables isn't allowed anymore in 11.2.0.2, but if you put everything into 1 big PARAM and use that in the create index it should work. Trying it atm (takes a while since the DB is quite big).

    edit: it worked :)

    It might be helpful to update the original post to work with 11.2.0.2+ (if you can verify these conclusions)

    Edited by: Michiel Weggen on 24-okt-2012 7:18
  • 14. Re: Excercise in Range Partitioning, Structured and Unstructured XMLIndexes
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    It could be that an "ALTER INDEX" does not yet support the SYSTEM partitioned methods, will check in higher versions, but indeed DBMS_XMLINDEX can be used as an alternative in 11.2.x. It has the advantage that you can script the whole thing in one anonymous block code.

    http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_xmlindex.htm#CEGHHDEJ

    See the sub programs section for

    CREATEDATEINDEX Procedure
    Creates a secondary index for date values in the VALUE column of a PATH TABLE which is the storage table of an XMLIndex

    CREATENUMBERINDEX Procedure
    Creates a secondary index for number values in the VALUE column of a PATH TABLE which is the storage table of an XMLIndex

    DROPPARAMETER Procedure
    Drops the XMLIndex parameter string that is associated with a given parameter identifier.

    MODIFYPARAMETER Procedure
    Modifies the XMLIndex parameter string that is associated with a given parameter name

    REGISTERPARAMETER Procedure
    Registers a parameter string and XMLIndex parameter string pair in XDB

    SYNCINDEX Procedure
    Synchronizes the index manually