This discussion is archived
1 Reply Latest reply: Sep 3, 2013 1:24 AM by odie_63 RSS

Create index on nested table (Structured storage)

991278 Newbie
Currently Being Moderated

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

    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).

Legend

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