There are lots of really nice capabilities introduced in the
12c database that significantly improves the manageability of partitioned
environments.
One of those new capabilities is the ability to create a partial index. In fact, in Oracle 12c we can create an index on a specific
partition and of course this subject will true about indexes that will create by
INDEXING PARTIAL expression. This property will decrease space utilization, Create index performance improvement and
also, increase the maintainperformance of the index and will increase the speed of DML
on partition tables.
For using this facility when we create a table we can use
INDEXING expression and use INDEXING OFF
for a partition that prevents to create an index on a specific partition.
Indexing property is in the table and partition level is by default
on.
create table vahid.raptor(id number(9), create_date date,DEC
VARCHAR2(4000)) INDEXING OFF
PARTITION BY RANGE
(create_date)
INTERVAL(NUMTOYMINTERVAL(12, 'MONTH'))
(
partition p_1
VALUES LESS THAN(to_date('2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,
partition p_2
VALUES LESS THAN(to_date('2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,
partition p_3
VALUES LESS THAN(to_date('2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')) INDEXING ON
);
We can change property of partition indexing after create
table:
SQL> alter table
vahid.raptor modify partition p_2 indexing on;
SQL> alter
table vahid.raptor modify partition p_2 indexing off;
Now I want to insert rows in the raptor and I will check
index status as well as query execution status on the table.
begin
for i in 1.. 10000
loop
insert into
vahid.raptor values(1,TO_DATE('2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));
insert into
vahid.raptor values(i,TO_DATE('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));
insert into
vahid.raptor values(i,TO_DATE('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));
insert into
vahid.raptor values(i,TO_DATE('2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));
insert into
vahid.raptor values(i,TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',100,'Raptor-F35'));
end loop;
commit;
end;
gather table statistics and check the size of each segment
begin
dbms_stats.gather_table_stats(ownname =>'VAHID' ,tabname =>
'RAPTOR' ,degree => 8,cascade => true);
end;
Check indexing status:
select d.table_name,d.partition_name,d.indexing from
dba_tab_partitions d where d.table_name='RAPTOR';
For viewing indexing property of a table using the below query:
select table_name,
def_indexing from dba_part_tables where table_name='RAPTOR';
As well as we can change above property by:
SQL> alter table vahid.RAPTOR modify default attributes
indexing ON;
Let me create partial and full index on CREATE_DATE and
check it.
SQL> set timing on;
SQL> CREATE INDEX
IDX_01 ON vahid.RAPTOR(CREATE_DATE) LOCAL INDEXING PARTIAL;
Index created.
Elapsed: 00:00:00.27
Check index partition status:
select index_name,
partition_name, status from dba_ind_partitions
where index_name='IDX_01' order by partition_position;
As you can see in the above grid just index IDX_01 is usable on
partition p_3.
Check index space utilization:
select
p.segment_name,p.partition_name,p.bytes/1024/1024 SIZE_MB from dba_segments p
where p.segment_name='IDX_01';
Now we will check the execution plan of query and the point is
full table scan on p_1 and p_2
SELECT COUNT(create_date) FROM vahid.raptor WHERE
create_date
BETWEEN TO_DATE('02/01/2002','MM/DD/YYYY') AND
TO_DATE('02/01/2010','MM/DD/YYYY') ;
Also, check the query on partition p_3 and we will understand a
big difference in the plan
SELECT
COUNT(create_date) FROM vahid.raptor WHERE
create_date
BETWEEN TO_DATE('02/01/2019','MM/DD/YYYY') AND
TO_DATE('03/01/2019','MM/DD/YYYY') ;
The point: we can rebuild unusable partitions manually:
SQL> ALTER INDEX IDX_01
REBUILD PARTITION p_1;
select index_name,
partition_name, status from dba_ind_partitions
where index_name='IDX_01' and partition_name='P_1' order by
partition_position;
As we should know when I change INDEXING from ON to OFF the index will unusable.
FULL INDEXING:
At first, I dropped IDX_01 and recreate it.
SQL> drop index IDX_01;
SQL> CREATE
INDEX IDX_01 ON
vahid.raptor(create_date) LOCAL INDEXING FULL;
Index created.
Elapsed: 00:00:00.54
The time of index creation as you see increased and also the
index space utilization will increase.
