-
I have a table which has partitions. What I want to do is to create index for each partition, one at the time.
In the past I have used the syntax:
CREATE BITMAP INDEX IDX_FACT_INV_TIME ON
GLX_FACT_INVENTORY(TIME_KEY)
LOCAL
(
PARTITION P1037 TABLESPACE FACT_INV_01,
PARTITION P1121 TABLESPACE FACT_INV_02,
PARTITION P1191 TABLESPACE FACT_INV_03,
PARTITION P1254 TABLESPACE FACT_INV_04,
PARTITION P1290 TABLESPACE FACT_INV_05,...etc
);
to create the index.
I cannot find any other syntax for creating index for partitioned table, is it even possible to create index for one partition at the time?
Thanx for your help in advance!
Fiona
-
You are create for all partitioned table an index because you give the word 'local' on.
Index offer a table, which is partitioned.( offer all):
create
on (col1, col2,...);
Index of each patitioned tablepart:
create
on (col1, col2,...)
local;
Index has an own partitioned methode independent of the table
create
on (col1, col2,...)
global index by range (col)
partition p1 values less than 500,
partition p1 values less than 2500
....
I hope you have understaand it.
Thomas Schmidt
-
If the index is local, then its partitions are tied into those of the table and you can't create them separately. You can, however, switch them off and rebuild them separately using syntax such as:
alter index modify partition unusable;
and
alter index rebuild partition part_name;
I've found this quite a useful feature because you can switch off indexes during loads of partitions, which speeds up the loads but doesn't mean you have to rebuild the entire index.
