===========
Oracle 11 i
===========

New Options for Index Storage for Oracle mainly deals with Partition by Range, Hash... Talk to Niels to check if we need to implement partitioning for Indexes

Syntax:

Table Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col [ASC | DESC]) index_clause index_attribs

Bitmap Join Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col_expression [ASC | DESC])
            FROM [schema.]table_name [tbl_alias]
               WHERE condition [index_clause] index_attribs

Cluster Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON CLUSTER [schema.]cluster_name index_attribs

index_clauses:

LOCAL STORE IN (tablespace)

LOCAL STORE IN (tablespace)
  (PARTITION [partition
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause]
       [STORE IN {tablespace_name|DEFAULT]
       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

LOCAL (PARTITION [partition
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause]
       [STORE IN {tablespace_name|DEFAULT]
       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

GLOBAL PARTITION BY RANGE (col_list)
   ( PARTITION partition VALUES LESS THAN (value_list)
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause] )

INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS ('ODCI_Params')]
 {This for table index only, not bitmap join Index}

index_attribs:
any combination of the following

    NOSORT|SORT
    REVERSE
    COMPRESS int
    NOCOMPRESS
    COMPUTE STATISTICS
    [NO]LOGGING
    ONLINE
    TABLESPACE {tablespace|DEFAULT}
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause
    PARALLEL parallel_clause

If the PARALLEL clause is used it should be the last option.
==========
Sample DDL
==========

Index Partition by Hash

CREATE INDEX "HR_CLONE"."IND1" ON "HR_CLONE"."EMPLOYEES" ("EMPLOYEE_ID")
INITRANS 5 MAXTRANS 5
GLOBAL PARTITION BY HASH ("EMPLOYEE_ID") (PARTITION "IND1_P1" TABLESPACE "USERS")

Index Partition by Range

CREATE INDEX "HR_CLONE"."IND1" ON "HR_CLONE"."EMPLOYEES" ("EMPLOYEE_ID")
INITRANS 5 MAXTRANS 5
GLOBAL PARTITION BY RANGE ("EMPLOYEE_ID")
(PARTITION "IND1_P1" VALUES LESS THAN () NOCOMPRESS , PARTITION "IND1_P2" VALUES LESS THAN (MAXVALUE))