14 Replies Latest reply: Oct 24, 2012 10:11 AM by Marco Gralike RSS

    Exercise in Range Partitioning, Structured and Unstructured XMLIndexes

    Marco Gralike
      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
          -- 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
            -- 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
              -- 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
                -- 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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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