Thanks for the question, Alejandro.
Asked: April 11, 2019 - 11:33 pm UTC
Last updated: April 15, 2019 - 4:30 am UTC
Version: 12c
Viewed 10K+ times! This question is
You Asked
I have a question about a global index on a history table that has been partitioned into weeks from 1 to 53 with subpartitions from 1 to 4 all this into a list type partitioning. The question is that local and global indexes have been created. What would be the best option in optimization to enter by partitions?
1.- Leave the global index
2.- Transform it in local
It will be accessed by the week (FISEMANA) and after the local index (FITIPOREP).
example of a query:
SELECT <fields>
FROM TACLIMGGEO IC
WHERE IC.FISEMATRASO = :var
AND NIVELGEO = :var2
AND FITIPOREP = :var3
AND FISEMANA = :var4
AND IC.FIANIO = :var5
ORDER BY 1;
e.g:
CREATE TABLE TAIMGCONGEO
(PAIS NUMBER,
SEMATRASO NUMBER,
FITIPOREP NUMBER,
FISEMANA NUMBER,
FIANIO NUMBER
)
PARTITION BY LIST(FISEMANA)
SUBPARTITION BY LIST (FITIPOREP)
SUBPARTITION TEMPLATE
(SUBPARTITION TIPREP1 VALUES (1) TABLESPACE LP01_DAT,
SUBPARTITION TIPREP2 VALUES (2) TABLESPACE LP02_DAT,
SUBPARTITION TIPREP3 VALUES (3) TABLESPACE LP03_DAT,
SUBPARTITION TIPREP4 VALUES (4) TABLESPACE LP04_DAT,
SUBPARTITION TIPREP5 VALUES (5) TABLESPACE LP05_DAT
)
(
PARTITION PSEM1 VALUES (1)
TABLESPACE LP01_DAT,
PARTITION PSEM2 VALUES (2)
TABLESPACE LP02_DAT,
PARTITION PSEM3 VALUES (3)
TABLESPACE LP03_DAT,
PARTITION PSEM4 VALUES (4),
.
.
.
53
);
/
CREATE INDEX IX3 ON TACLIMGGEO(FISEMANA, FIANIO) GLOBAL;
/
CREATE INDEX ix1 ON taclimggeo (
FITIPOREP,
pais,
sematraso
)
LOCAL ( PARTITION psem1
NOLOGGING
TABLESPACE lp01_idx,
PARTITION
psem2
NOLOGGING
TABLESPACE lp02_idx,
PARTITION
psem3
NOLOGGING
TABLESPACE lp03_idx,
.
.
.
53
);
and Connor said...
I see no benefit in IX3 being global because the leading key FISEMANA is the partitioning key anyway.
CREATE INDEX IX3 ON TACLIMGGEO(FIANIO) LOCAL
would achieve the same thing, as well as giving (some) benefit to queries that referenced FIANIO and omitted FISEMANA from the predicates.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment