set echo on
set timing on

--conn rss/rss

drop index newsitem_bix_ix force;
drop index newsitem_bix_text_ix;

-- Create XML index on the binary XML table
-- Notice the path subsetting
create index newsitem_bix_ix on newsItemsTable(OBJECT_VALUE) indextype is xdb.xmlindex
             parameters ('PATH TABLE      newsitem_bix_path_table
                          PATHS (INCLUDE (/a:entry/a:title /item/title /r1:item/r1:title) 
                                 NAMESPACE MAPPING (xmlns:a="http://purl.org/atom/ns#" xmlns:r1="http://purl.org/rss/1.0/"))
                          PATH ID INDEX   newsitem_bix_path_id_ix
                          ORDER KEY INDEX newsitem_bix_order_key_ix
                          VALUE INDEX     newsitem_bix_value_ix')
/

-- Create a secondary text index on the VALUE column of the path table
CREATE INDEX newsitem_bix_text_ix ON newsitem_bix_path_table (VALUE) INDEXTYPE IS CTXSYS.CONTEXT
       parameters ('transactional')
/

call dbms_stats.gather_index_stats(USER,'newsitem_bix_ix')
/
call dbms_stats.gather_index_stats(USER,'newsitem_bix_text_ix')
/
commit
/

