hi there,

yes, you can specify storage parameters for a local index creation. for example, on a range partitioned table (EMP), you can create a local index (EMPX1) with the following sql:

CREATE INDEX empx1 ON emp (empno)
LOCAL
PCTFREE 5
STORAGE(INITIAL 8M NEXT 1M PCTINCREASE 0)
TABLESPACE empx_ts NOLOGGING PARALLEL;

please note, however, that any storage parameters specified are for EACH partition and not for whole table. e.g.:

table EMP is range partitioned by column YEARMO. the current existing partitions are:

- emp_199901
- emp_199902
- emp_199903

so the aforementioned CREATE INDEX script will create a local index with an INITIAL 8M size for EACH of the EMP partitions. so in this example, there will be 3 local 8MB indexes created, for a total size of 24MB

but if table EMP is composite partitioned. e.g:

table EMP is range partitioned by YEARMO and then hash partitioned by SSN:

(range)...emp_199901*
.....(hash)....emp_199901_a
..................emp_199901_b
..................emp_199901_c
(range) emp_199902
.....(hash)....emp_199902_a
..................emp_199902_b
..................emp_199902_c
(range) emp_199903
.....(hash)....emp_199903_a
..................emp_199903_b
..................emp_199903_c

* Please ignore all those annoying dots. i did that just to preserve the formatting.

now the aforementioned CREATE INDEX script will create 9 8MB local indexes!! one for each subpartition for a total of 72MB!

so just be careful when specifying storage parameters for a local index on a partitioned table.

have fun

nick