DBA Hub

📋Steps in this guide1/3

Partial Indexes for Partitioned Tables in Oracle Database 12c Release 1

Oracle 12c allows the creation of global and local indexes on a subset of the partitions of a partitioned table.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Table: INDEXING [ON | OFF] Clause

The decision about which partitions are indexed is made using the clause associated with each partition, with the default being . We create a partitioned table with three partitions. The first two partitions have indexing turned on. One by default, and one explicitly. The third partition has indexing turned off. The INDEXING column of the view shows us the indexing status of each partition. Only the 2016 partition has INDEXING turned off. We change the indexing clause for a partition using the command. We turn indexing off for the 2014 and 2015 partitions, and turn indexing on for the 2016 partition. We can see the changes reflected in the output from the view. Indexing is off for the 2014 and 2015 partitions. Indexing is on for the 2016 partition.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Create and populate the partitioned table.
drop table t1 purge;

create table t1 (
  id            number,
   description   varchar2(50),
   created_date  date
)
partition by range (created_date) (
  partition part_2014 values less than (date '2015-01-01'),
  partition part_2015 values less than (date '2016-01-01') indexing on,
  partition part_2016 values less than (date '2017-01-01') indexing off
);


insert into t1 values (1, 't1 one', date '2014-07-01');
insert into t1 values (2, 't1 two', date '2015-07-01');
insert into t1 values (3, 't1 three', date '2016-07-01');
commit;

column table_name format a20
column partition_name format a20

select table_name,
       partition_name,
       indexing
from   user_tab_partitions
where  table_name = 'T1'
order by 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            ON
T1                   PART_2015            ON
T1                   PART_2016            OFF

SQL>

alter table t1 modify partition part_2014 indexing off;
alter table t1 modify partition part_2015 indexing off;
alter table t1 modify partition part_2016 indexing on;

select table_name,
       partition_name,
       indexing
from   user_tab_partitions
where  table_name = 'T1'
order by 1,2;

TABLE_NAME           PARTITION_NAME       INDE
-------------------- -------------------- ----
T1                   PART_2014            OFF
T1                   PART_2015            OFF
T1                   PART_2016            ON

SQL>
2

Index: INDEXING [FULL | PARTIAL] Clause

Even with the partition indexing settings in place, by default indexes are created as , so the partition setting are ignored. We create a local index on the column. If we don't specify the clause during index creation, the default action is . Checking the column of the view we see all index partitions are usable. By default index creation ignores the setting of the table partitions. Checking the column of the view we see the index is marked as indexing . We drop the index and create it again, this time using the clause. Now we see the table partitions marked as are not indexed, and have index partitions marked as unusable. The 2016 table partition was marked as , so it is indexed and we see a usable index partition. Checking the column of the view we see the index is marked as indexing . Global indexes can also be created as partial indexes, with only the flagged partitions included in the index. We create a partial global index. Notice the use of the indexing partial clause. The resulting global index is marked as .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
drop index t1_local_partial_idx;

-- create index t1_local_partial_idx on t1(created_date) local indexing full;
create index t1_local_partial_idx on t1(created_date) local;

column index_name format a25

select index_name,
       partition_name,
       status
from   user_ind_partitions
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            USABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            USABLE

SQL>

column indexing format a8

select index_name,
       indexing
from   user_indexes
where index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_LOCAL_PARTIAL_IDX      FULL

SQL>

drop index t1_local_partial_idx;
create index t1_local_partial_idx on t1(created_date) local indexing partial;

select index_name,
       partition_name,
       status
from   user_ind_partitions
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1,2;

INDEX_NAME                PARTITION_NAME       STATUS
------------------------- -------------------- --------
T1_LOCAL_PARTIAL_IDX      PART_2014            UNUSABLE
T1_LOCAL_PARTIAL_IDX      PART_2015            UNUSABLE
T1_LOCAL_PARTIAL_IDX      PART_2016            USABLE

SQL>

column indexing format a8

select index_name,
       indexing
from   user_indexes
where  index_name = 'T1_LOCAL_PARTIAL_IDX'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>

create index t1_global_partial_idx on t1(description) global indexing partial;


-- Check the indexing status of the indexes.
select index_name,
       indexing
from   user_indexes
where  index_name like 'T1%'
order by 1;

INDEX_NAME                INDEXING
------------------------- --------
T1_GLOBAL_PARTIAL_IDX     PARTIAL
T1_LOCAL_PARTIAL_IDX      PARTIAL

SQL>
3

Modified Views

The , and views have been modified to include an column, which indicates if indexing is or for the partition. The view has been modified to include an column, which indicates if the index is or . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!