-
I am in process of creating partition table based on range.
If the key on the partition table is different then LOCAL Indexes, will it impact on performance?
i.e.
CREATE TABLE TEST
(c1 number,
c2 varchar2(20),
c3 varchar2(10),
c4 varchar2(10))
PARALLEL(DEGREE 4 INSTANCES 1)
PARTITION by range (c1,c2)
(
PARTITION T1 VALUES LESS THAN (100,’A’)
STORAGE(INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1)
LOGGING,
…
)
CREATE INDEX TEST_IND on TEST(c1,c3,c2)
LOCAL (PARTITION T1
STORAGE(INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
FREELISTS 1
FREELIST GROUPS 1)
TABLESPACE TSIND LOGGING,
…
)
If table is created with PARALLEL DEGREE and INDEX is not, what will happen?
Thanks.
-
I am not fully sure,
but to me it seems it will not have a serious negative impact on the performance.
But to be fast it needs the fields c1, c2
in any query to access only 1 partition.
When using only the fields c1, c3 the index is used, but not a direct access to to the right partition.
-
Definitely there will be a difference in performance. If you have both the partition key and the index keys are same, then definitely you enjoy the better performance.
-
For OLTP applications: Global indexes and local prefixed indexes are beffer than nonunique local indexes because they minimize the number of index partition probes.
For DSS applications: Local non-prefixed indexes are ideal for DSS systems where you need to partition by a date range but want to query often on another column.
