Hi Gurus,
Need your help with indexes understanding.
Background
We have a huge table emp(no partition) , 2TB in size. As part of purge/archiving, we are arching data older then 2 years to hadoop. As part of table rebuild, we plan to do below.
1) Create a monthly interval partition table with hiredate column as partition key.
2) query based export from original table and import into new partition table.
3) Create indexes and other metadata.
4) Swap table names,index names, etc.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
As part of the older table, we have 3 indexes as
As part of the older table, there were 3 indexes which uses hiredate(partition column).
A) IDX1 (HIREDATE)
B) IDX2 (HIREDATE, EMPNO)
C) IDX3 (SAL, HIREDATE, EMPNO)
Question is
1) Do we need IDX1 index?
2) Reason for partition is,in future, we should be able to purge easily. if IDX1 is needed, I will create local index.
3) Do I also need to create IDX2 and IDX3 as local? How will composite index behavior will be if I create it as local? Will there be any performance impact?
Thanks in advance for your help and guidance.
Regards,
Lancerique.