1 Reply Latest reply: Sep 3, 2013 3:24 AM by odie_63 RSS

    Create index on nested table (Structured storage)

    991278

      Hi,

       

      I have XMLType column created with Structured storage in my schema.

       

      I can see a nested table created by Oracle internally and would like to index this nested table to improve performance of my XMLQuery operation.

       

      SELECT  TABLE_NAME,TABLE_TYPE_NAME,PARENT_TABLE_NAME,substr(PARENT_TABLE_COLUMN,1,50) PARENT_TABLE_COLUMN from USER_NESTED_TABLES where PARENT_TABLE_NAME='Order_store1';
      
      
      TABLE_NAME                      TABLE_TYPE_NAME                PARENT_TABLE_NAME              PARENT_TABLE_COLUMN
      ------------------------------  ------------------------------ ------------------------------ ------------------------------
      SYS_PR8Pn0C/llk0asQOgK6yDfno==  Order_store1_COLL              Order_store1                   "XMLDATA"."Order_store"            
      
      
      

       

      How do I create index on the nested table??

       

       

      Thanks..

        • 1. Re: Create index on nested table (Structured storage)
          odie_63

          How do I create index on the nested table??

           

          Just like you would create an index on a regular table.

          CREATE INDEX my_nt_index_1 on "SYS_PR8Pn0C/llk0asQOgK6yDfno==" ( "COLUMN_NAME" ) ;

           

          And it may be better to include the NESTED_TABLE_ID pseudocolumn as well :

          CREATE INDEX my_nt_index_1 on "SYS_PR8Pn0C/llk0asQOgK6yDfno==" ( "COLUMN_NAME", nested_table_id ) ;


          But first, to make this process easier, you can rename the nested tables with meaningful names using ALTER TABLE RENAME command or DBMS_XMLSTORAGE_MANAGE.renameCollectionTable (new 12c built-in, or available here for prior releases).