LOcal partitioned Index
In a local partitioned index, the number of index partitions will match the number of partitions in the base table. Local indexes are partitioned indexes which are easy to manage and each index partition is associated with its respective table partitions. Local indexes are most suited for data warehousing or DSS applications.Local indexes are more flexible when it comes to partition maintenance operations..When we take any action(MERGE, SPLIT,EXCHANGE etc) on local partition, it impacts only that partition and other partition will be available.For example, If the DBA decides to move a table partition, only the associated local index partition needs to be rebuilt. With a global index all index partitions must be rebuilt.
The major advantage of local indexes is there will not be any index invalidation when you perform alter table exchange, drop, add partitions. When you perform above mention operations on the underlying table, the index remain valid.
Come to the table reorganization part, the local indexes gives the advantage of making individual partitions of a table and indexes offline for the maintenance. So it won’t affect the other partitions in the table or index. The maintenance would be easy for the local indexes.
The index partition gets deleted when the underlying table partition dropped. We can create bitmap on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. Bitmap indexes cannot be with Global indexes.
SQL> CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE USERS,
PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE USERS,
PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE USERS);
Table created.
SQL> col PARTITION_NAME for a33
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3
SQL> declare
v_no number :=1;
begin
delete employees;
for i in 1..10 loop
insert into employees values(v_no,'name...',v_no);
v_no := v_no+1;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3
SQL> select * from EMPLOYEES;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ---------- -------------
1 name... 1
2 name... 2
3 name... 3
4 name... 4
5 name... 5
6 name... 6
7 name... 7
8 name... 8
9 name... 9
10 name... 10
10 rows selected.
SQL> create index idx_local on employees(last_name) local;
Index created.
SQL> select locality from user_part_indexes where index_name='IDX_LOCAL';
LOCALI
------
LOCAL
SQL> col index_name for a20
SQL> select INDEX_NAME,INDEX_type ,PARTITIONED,GLOBAL_STATS from user_indexes where INDEX_NAME='IDX_LOCAL';
INDEX_NAME INDEX_TYPE PAR GLO
-------------------- --------------------------- --- ---
IDX_LOCAL NORMAL YES NO
SQL> select INDEX_NAME,PARTITION_NAME,status from USER_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL';
INDEX_NAME PARTITION_NAME STATUS
-------------------- ---------------------- --------
IDX_LOCAL EMPLOYEES_PART1 USABLE
IDX_LOCAL EMPLOYEES_PART2 USABLE
IDX_LOCAL EMPLOYEES_PART3 USABLE
SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1 9
EMPLOYEES_PART2 1
EMPLOYEES_PART3 0
SQL>
That is, When you create a local index on a partitioned table, it automatically creates index partitions as many as in the table partitions. Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.
Note:- If you specify the partition information when building a local partitioned index, the number of partitions must match the number of partitions in the table on which the partitioned index is built.
In a local partitioned index, the number of index partitions will match the number of partitions in the base table.
This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.Droping a partition from base table will delete the corresponding index partition
SQL> ALTER TABLE EMPLOYEES drop partition EMPLOYEES_PART2;
Table altered.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART1 9
EMPLOYEES_PART3 0
SQL> select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='IDX_LOCAL';
INDEX_NAME PARTITION_NAME
--------------------------------- ---------------------------------
IDX_LOCAL EMPLOYEES_PART3
IDX_LOCAL EMPLOYEES_PART1
SQL>
You can't drop partition from a local partioned index.
SQL> alter index IDX_LOCAL drop partition EMPLOYEES_PART3;
alter index IDX_LOCAL drop partition EMPLOYEES_PART3
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid
for local partitioned index
SQL>
Disadvantage:- we can't add partition manually to the local index.
Locally partitioned indexes can be created as prefixed or non-prefixed
Local prefixed indexes - When you create a prefixed locally-partitioned index, it means that the partitioning column(s) for the table are on the leading edge of the index For example, if a table is range partitioned on the TIMESTAMP column, a local prefixed index on that table would have TIMESTAMP as the first column in its column list.The example that we discussed above falls under local prefixed index.
Local non-prefixed indexes - If the partitioning column(s) of the table are not on the leading edge, it is regarded as a non-prefixed
index. In earlier versions of Oracle, having a local index as prefixed offered performance advantages over its non-prefixed counterpart. With later versions of Oracle, including version 11gR2, the advantages of creating local indexes as prefixed have diminished. However, if your database environment is an OLTP
system, it still benefits query performance to have local prefixed indexes over non-prefixed indexes, as the optimizer will potentially scan less index partitions in order to retrieve the data for a query. Also The index may or may not contain the partition key columns of the table. Local non-prefixed indexes does not support partition pruning,
For example, Assuming the INVOICES table is range partitioned on INVOICE_DATE, the following example is of a local non-prefixed index. The indexed column does not match the partition key.
CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Global Index
Globally partitioned indexes essentially mean that the index has a different partitioning scheme and ispartitioned based on a different column or set of columns than the table. Global index partitions are mainly used for OLTP applications where fewer index probes are required than with local partitioned indexes.For Global indexes the DBA has the choice to create the partitions as many as required. In Global partition scheme the index is harder to maintain since the index may span partitions in the base table.
You can create the following types of globally partitioned indexes:
• Range
• Hash
You can create a globally partitioned index on a partitioned table or a non-partitioned table
If we drop one of the index partition as part of reorganization the entire global index will be affected.
The following operations on a heap-organized table render a global index unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE
For range-based globally partitioned indexes, there must always be a maximum specified for the index, with a high value of MAXVALUE.
Global indexes can be range partitioned using a different partition scheme from the underlying table. i.e. on a table (containing two columns id and start_date) that is hash partitioned by id, the global index on start_date can be range partitioned.
There are two types of global indexes.They are,
1. Global non-paritioned indexes (similar to the normal non-partitioned indexes)
2. Global partitioned indexes.
You can add partitions to the global partition index by splitting the MAXVALUE partition using “alter index drop partition” .You can drop a empty global index partition.
If you try to drop a non-empty global index partion will cause the next highest partition to be marked unusable. I.e. You cannot drop the highest partition in a global index.
Unlike locally partitioned indexes that can be defined as prefixed or non-prefixed, all globally
partitioned indexes must be created as prefixed; that is, the partitioning column must be on the leading
edge of the index.
Example of Global Non-partitioned index.
------------------------------------------
SQL> CREATE INDEX employees_global_idx ON employees(employee_id);
or --> CREATE INDEX employees_global_idx ON employees(employee_id) GLOBAL;
SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_IDX';
no rows selected
SQL>
No rows selected means,its a Global Non-partitioned index.
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_IDX';
INDEX_NAME INDEX_TYPE GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_IDX NORMAL YES
SQL>
Lets drop this index and create a partioned global index
SQL> drop index EMPLOYEES_GLOBAL_IDX;
Index dropped.
SQL>
Here is an example for a range-based globally partitioned index,
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
2 3 (PARTITION p1 VALUES LESS THAN(3),
4 PARTITION p2 VALUES LESS THAN(6),
5 PARTITION p3 VALUES LESS THAN(11),
6 PARTITION p4 VALUES LESS THAN(20),
7 PARTITION p5 VALUES LESS THAN(MAXVALUE));
Index created.
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME INDEX_TYPE GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL NO
SQL> select locality from dba_part_indexes where index_name='EMPLOYEES_GLOBAL_PART_IDX';
LOCALI
------
GLOBAL
SQL> select INDEX_NAME,PARTITION_NAME from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME
--------------------------------- ---------------------------------
EMPLOYEES_GLOBAL_PART_IDX P1
EMPLOYEES_GLOBAL_PART_IDX P2
EMPLOYEES_GLOBAL_PART_IDX P3
EMPLOYEES_GLOBAL_PART_IDX P4
EMPLOYEES_GLOBAL_PART_IDX P5
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES',cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME INDEX_TYPE GLO
--------------------------------- --------------------------- ---
EMPLOYEES_GLOBAL_PART_IDX NORMAL YES
SQL>
Lets remove a empty partition :-
SQL> select count(*) from employees where employee_id between 12 and 20;
COUNT(*)
----------
0
SQL> ALTER index employees_global_part_idx drop partition p4;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P3 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
SQL>
Now we will drop the partition P3 and see status. When we drop this partition, it should invalidate the next highest partition. Here, P5 is next highest partition.
SQL> alter index employees_global_part_idx drop partition p3;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P5 UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
SQL>
SQL> alter index employees_global_part_idx rebuild;
alter index employees_global_part_idx rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
SQL> alter index employees_global_part_idx rebuild partition p5;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
SQL>
if you drop a partition from the base table all index partition become invalidated -
SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART1;
Table altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P5 UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P2 UNUSABLE
EMPLOYEES_GLOBAL_PART_IDX P1 UNUSABLE
SQL>
SQL> alter index employees_global_part_idx rebuild partition p5;
Index altered.
SQL> alter index employees_global_part_idx rebuild partition p2;
Index altered.
SQL> alter index employees_global_part_idx rebuild partition p1;
Index altered.
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
SQL>
In case you're using a global partitioned index, you can specify the update global indexes clause to prevent the global index from being marked as unusable. The two big advantages in specifying
the update global indexes clause is that the index remains online and available during the maintenance operation and you don't have to rebuild it after the maintenance operation.
SQL> alter table EMPLOYEES drop partition EMPLOYEES_PART2 update global indexes;
Table altered.
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'EMPLOYEES' order by partition_position;
PARTITION_NAME NUM_ROWS
--------------------------------- ----------
EMPLOYEES_PART3 0
SQL> select INDEX_NAME,PARTITION_NAME,status from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME STATUS
--------------------------------- --------------------------------- --------
EMPLOYEES_GLOBAL_PART_IDX P5 USABLE
EMPLOYEES_GLOBAL_PART_IDX P2 USABLE
EMPLOYEES_GLOBAL_PART_IDX P1 USABLE
SQL>
The second type of globally partitioned that can be created is the hash partitioned index. This is typically done for performance reasons and keeps the index more evenly spread between the partitions.
example-
SQL> CREATE INDEX employees_ih1
2 ON employees (department_id)
3 GLOBAL
4 PARTITION BY HASH(department_id) partitions 4;
Index created.
My database is in 12c so what will happen when you do drop/truncate partion along with update global indexes?
Oracle 12c: Global Index maintenance is now asynchronous
--------------------------------------------------------
Maintaining global indexes were always a expensive task in Oracle, particularly in the context of partitioned tables, where dropping or truncating a table partition could cause a global index to become UNUSABLE/INVALID unless specified to update the indexes during the drop/truncate operation. However, updating the index entries during partition drop/truncate can eventually slow down the actual drop/truncate operation.
With Oracle 12c, a drop or truncate table partition (with update indexes clause) is optimized by deferring the maintenance of associated global indexes, while still leaving the indexes in VALID state.
Prior to 12c, a drop/truncate table partition (with update indexes clause) would cause a synchronous maintenance for the associated global indexes and thereby would delay the actual drop/truncate operation. However, with 12c this global index maintenance operation is performed asynchronously which optimizes the drop or truncate partition operation.
SQL> select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME NUM_ROWS ORP
--------------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX P5 1 YES
EMPLOYEES_GLOBAL_PART_IDX P2 0 YES
EMPLOYEES_GLOBAL_PART_IDX P1 0 YES
SQL>
As we can observe, the global indexes are still in VALID/USABLE state. However, this time the new column ORPHANED_ENTRIES is showing a value YES.
The actual index maintenance is performed at a later time, triggered by one of the following.
The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job is scheduled to run at 02:00 every day.
The SYS.PMO_DEFERRED_GIDX_MAINT_JOB job can be run manually using the DBMS_SCHEDULER.RUN_JOB procedure.
Run the DBMS_PART.CLEANUP_GIDX procedure.
Run the ALTER INDEX REBUILD [PARTITION] command.
Run the ALTER INDEX [PARTITION] COALESCE CLEANUP command.
Now how oracle make sure that these index will return the correct result as it contains Orphaned entry? .Oracle internally maintains the list of all orphaned entries associated with a global index. We can view the orphaned entries by querying SYS.INDEX_ORPHANED_ENTRY$ or SYS.INDEX_ORPHANED_ENTRY_V$ views as shown below.
SQL>select * from SYS.INDEX_ORPHANED_ENTRY_V$;
When we query data (using index) from a truncated/dropped table partition, Oracle scans through this list of orphaned index entries to avoid/ignore querying data from the respective table partitions (which is/are truncated or dropped).
Oracle will periodically clean up the orphan index entries by means of a scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB which is scheduled to run at 2:00 AM every day as shown below.
SQL>select owner,job_name,program_name,next_run_date,state,enabled from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
We can also clean up the orphan index entries on demand either by running the scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB or by calling the procedure DBMS_PART.CLEANUP_GIDX as shown below.
SQL> exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB');
PL/SQL procedure successfully completed.
SQL> select INDEX_NAME,PARTITION_NAME,num_rows,orphaned_entries from DBA_IND_PARTITIONS where INDEX_NAME='EMPLOYEES_GLOBAL_PART_IDX';
INDEX_NAME PARTITION_NAME NUM_ROWS ORP
-------------------------- --------------------------------- ---------- ---
EMPLOYEES_GLOBAL_PART_IDX P5 1 NO
EMPLOYEES_GLOBAL_PART_IDX P2 0 NO
EMPLOYEES_GLOBAL_PART_IDX P1 0 NO
SQL>
Reference:-
http://www.oraclebuffer.com/oracle/oracle-12c-global-index-maintenance-is-now-asynchronous/
http://myorastuff.blogspot.com/2008/08/local-index-versus-global-index-on.html
No comments:
Post a Comment