Rem
Rem $Header: rdbms/admin/xsindex.sql /main/2 2009/03/15 21:01:57 jsamuel Exp $
Rem
Rem xsindex.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      xsindex.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsamuel     03/03/09 - modify index size this is replaced with
Rem                           structured xml index in fusion
Rem    jsamuel     01/09/08 - move security class xml index
Rem    jsamuel     12/27/07 - Index Creation on XS tables
Rem    jsamuel     12/27/07 - Created
Rem


-- create XML index for XS$principals
create index xdb.prin_xidx on xdb.xs$principals p (value(p)) indextype is 
xdb.xmlindex 
parameters('PATH TABLE prin_pt
            PATH ID INDEX prin_pidx
            ORDER KEY INDEX prin_oidx
            VALUE INDEX prin_vidx
            PATHS(PREDICATE(/r:user[r:UID][r:userName] /r:role[r:UID][r:name]
                 /r:dynamicRole[r:UID][r:name]  )
             NAMESPACE MAPPING(xmlns:r="http://xmlns.oracle.com/xs"))');

create unique index xdb.prin_uididx on xdb.prin_pt(TO_NUMBER(value0));  
create unique index xdb.prin_ruididx on xdb.prin_pt(TO_NUMBER(value2));
create unique index xdb.prin_druididx on xdb.prin_pt(TO_NUMBER(value4));

/************ Create Token - ID mapping tables *********************/
/* If the block size is less than 8K use smaller token sizes so
 * that index creation doesn't fail. (see bug 3928505)
 *
 * The max index key sizes for various block sizes are:
 *  2K max index key size = 1478 bytes (on Linux)
 *  4K max index key size = 3118 bytes (on Linux)
 *  8K max index key size = 6398 bytes (on Linux)
 *
 * For each of the various token column sizes below, the maximum token
 * length that would permit token-->id index creation was determined and
 * then a value 5% less (to account for any platform specific variance)
 * was picked as the token size.
 * 
 * Values of 1400 and 3000 was chosen for 2K and 4K respectively
 * taken from rdbms/admin/catxdbtm.sql
 *
 */

declare
  bsz number;
  xsprin_tok_bytes   number;
 
begin
    /* figure out block size and use appropriate token size */
   select t.block_size into bsz from user_tablespaces t, user_users u
      where u.default_tablespace = t.tablespace_name;

   if bsz < 4096 then
      xsprin_tok_bytes  := 400;
   elsif bsz < 8192 then
      xsprin_tok_bytes  := 1000;
   else
      xsprin_tok_bytes  := 4000;
   end if;
commit;
execute immediate 'create unique index xdb.prin_unameidx on xdb.prin_pt (substr(value1, 1,' || xsprin_tok_bytes || '))';
execute immediate 'create unique index xdb.prin_rnameidx on xdb.prin_pt (substr(value3, 1,' || xsprin_tok_bytes || '))';
execute immediate 'create unique index xdb.prin_drnameidx on xdb.prin_pt (substr(value5, 1,' || xsprin_tok_bytes || '))';

commit;
end;
/


-- create xml index for xs$securityclass
create index xdb.sc_xidx on xdb.xs$securityclass sc (value(sc)) indextype is xdb.xmlindex 
parameters ('PATH TABLE sc_pt
              PATH ID INDEX sc_pidx
              ORDER KEY INDEX sc_oidx
              VALUE INDEX sc_vidx
              PATHS (PREDICATE(/s:securityClass[@targetNamespace][@name] /s:securityClass/s:privilege[@name] 
                    /s:securityClass/s:aggregatePrivilege[@name]) 
             NAMESPACE MAPPING(xmlns:s="http://xmlns.oracle.com/xs"))');