Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed) July 26, 2013
Posted by Richard Foote in Global Indexes, Oracle Indexes, Partitioning.3 comments
Before I discuss another Oracle Database 12c new feature, Asynchronous Global Index Maintenance, thought it might be worthwhile discussing how Global Indexes were handled prior to 12c.
I’ll begin by creating and populating a simple range partitioned table:
SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
I’ll now create two global indexes, one non-partitioned, the other partitioned:
SQL> create index muse_id_i on muse(id); Index created. SQL> create index muse_code_i on muse(code) global partition by range(code) (partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue)); Index created. SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS --------------- --------------- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1500000 4224 4135 USABLE MUSE_CODE_I CODE_P2 1500000 4352 4177 USABLE MUSE_ID_I 3000000 9216 8633 VALID
So we currently have two happy chappy global indexes. I’m now however going to drop one of the table partitions without updating the global indexes and monitor both the db block gets and amount of redo that gets generated:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE ------------- ---------- db block gets 457109 redo size 234309652 SQL> alter table muse drop partition muse1; Table altered. Elapsed: 00:00:00.66 SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE -------------- ---------- db block gets 457165 redo size 234320512
We notice the operation completed very quickly and generated minimal db block gets (just 56) and redo (just 10860 bytes). However, this of course comes at a price:
SQL> select index_name, null partition_name, num_rows, leaf_blocks, status 2 from dba_indexes i where table_name='MUSE' and partitioned = 'NO' 3 union select index_name, i.partition_name, num_rows, leaf_blocks, status 4 from dba_ind_partitions i where index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS LEAF_BLOCKS STATUS --------------- --------------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1500000 4135 UNUSABLE MUSE_CODE_I CODE_P2 1500000 4177 UNUSABLE MUSE_ID_I 3000000 8633 UNUSABLE
Both global indexes are now unusable as a result as they haven’t been maintained on the fly and so have orphaned index entries pointing to the now non-existent table partition. So it was fast but left the global indexes in an unusable state which have to now be rebuilt.
The other option would be to drop the table partition but to also update the global indexes at the same time as follows:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE ------------ ---------- db block gets 129615 redo size 103978912 SQL> alter table muse drop partition muse1 update global indexes; Table altered. Elapsed: 00:00:13.08 SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# =n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129; NAME VALUE -------------- ---------- db block gets 185758 redo size 148012132
We notice this time, the operation has taken considerably longer and has generated many more db block gets (56,143 up from 56) and much more redo (44,033,220 bytes up from 10,860). So updating the global indexes on the fly comes at a cost, but at least they remain usable at the end of the operation:
SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name andtable_name='MUSE' and partitioned = 'NO' 2 union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS --------------- --------------- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 1000000 4224 4135 USABLE MUSE_CODE_I CODE_P2 1000000 4352 4177 USABLE MUSE_ID_I 2000000 9216 5849 VALID
Having updated the global indexes and having effectively deleted 1/3 of the table with the lowest ID values, if we were to now try and find the current minimum ID value:
SQL> select min(id) from muse; MIN(ID) ---------- 1000001 Execution Plan ---------------------------------------------------------- Plan hash value: 2104594370 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 6 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2787 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
The CBO is trying to use the index via the Index Full Min/Max Scan to quickly find this minimum ID. However, it keeps hitting leaf blocks with nothing but empty/deleted entries due to dropping the table partition, until it gets through roughly 1/3 of all the index leaf blocks before finally finding the first (and so minimum) non-deleted index value. As such, at 2,787 consistent gets, it’s a relatively expensive operation.
If however, we were to insert a whole bunch of new rows into the table (note these are rows with an ID value greater than existing rows) and then re-run the same query:
SQL> insert into muse select rownum+3000000, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> select min(id) from muse; MIN(ID) ---------- 1000001 Execution Plan ---------------------------------------------------------- Plan hash value: 2104594370 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 6 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 161 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
We notice that at just 161 consistent gets (reduced from 2,787), the Index Full Min/MAX Scan is much more efficient as most of the previously empty leaf blocks on the “left hand side of the index” have now been recycled due to inserting the new data into the “right hand side of the index”. As such, we now find the minimum ID value via the index much more efficiently.
So that was how things kinda worked in 11g and beforehand. However, with Oracle 12c, things have now changed as we’ll see in the next post …
Upcoming Oracle Events in Canberra (The Show Must Go On) July 19, 2013
Posted by Richard Foote in 12c, INSYNC13, Oracle.add a comment
For those of you who live locally in the Canberra region, there are a couple of key Oracle events coming up.
Oracle Database 12c Launch
Date: Friday, July 26 2013
Time: 8:30 AM – 1:00 PM
Where: Hyatt Hotel
120 Commonwealth Ave
Yarralumla, Canberra
I’ll be presenting “Simplify Consolidation with Oracle Database 12c”.
—-
AUSOUG INSYNC13 Conference
Date: 27-28 August 2013
Where: Crown Plaza Hotel
1 Binara Street
Canberra City
Program Details and Registration
I’ll be presenting “New Indexing Features Introduced in Oracle Database 12c”.
See you there 🙂
12c Partial Indexes For Partitioned Tables Part II (Vanishing Act) July 12, 2013
Posted by Richard Foote in 12c, Local Indexes, Oracle Indexes, Partial Indexes, Partitioning.5 comments
In Partial Indexes Part I, we looked at how it was possible with the 12c database to create a Partial Index based on data from only selected table partitions. The resultant Partial Index can be either a Global or Local Index.
In Part I, we only really looked at Global Indexes, so let’s look at a Local Index example. Using the same Partitioned Table example as before:
SQL> create table pink_floyd (id number, status varchar2(6), name varchar2(30)) indexing off partition by range (id) (partition pf1 values less than (1000001), partition pf2 values less than (2000001) indexing off, partition pf3 values less than (maxvalue) indexing on); Table created.
This time, we’ll create a Local Partial Index:
SQL> create index pink_floyd_status_i on pink_floyd(status) local indexing partial; Index created.
If we look at the details of the resultant Local Index:
SQL> select index_name, partition_name, num_rows, status, leaf_blocks from dba_ind_partitions where index_name = 'PINK_FLOYD_STATUS_I'; INDEX_NAME PARTITION_NAME NUM_ROWS STATUS LEAF_BLOCKS -------------------- --------------- ---------- -------- ----------- PINK_FLOYD_STATUS_I PK1 0 UNUSABLE 0 PINK_FLOYD_STATUS_I PK2 0 UNUSABLE 0 PINK_FLOYD_STATUS_I PK3 1000000 USABLE 2513
We can see that for those table partitions with INDEXING OFF, the associated Local Indexes have simply been made UNUSABLE. Since Unusable Indexes consume no storage, there is effectively no corresponding index segment for these index partitions.
For the one and only PK3 table partition with INDEXING ON, its associated Local Index has been created as normal. So the end result is very similar to the previous Global Index example, only those rows from the table partitions with the INDEXING ON property are effectively being indexed.
There is one scenario in which the creation of a Partial Index is not permitted, that is in the creation of a Unique Index or a Non-Unique Index to police a Primary Key or Unique Key constraint. Some examples:
SQL> create unique index pink_floyd_id_i on pink_floyd(id) indexing partial; create unique index pink_floyd_id_i on pink_floyd(id) indexing partial * ERROR at line 1: ORA-14226: unique index may not be PARTIAL SQL> alter table pink_floyd add constraint pink_floyd_pk primary key(id) using index (create index pink_floyd_id_i on pink_floyd(id) indexing partial); alter table pink_floyd add constraint pink_floyd_pk primary key(id) using index (create index pink_floyd_id_i on pink_floyd(id) indexing partial) * ERROR at line 1: ORA-14196: Specified index cannot be used to enforce the constraint. SQL> create index pink_floyd_id_i on pink_floyd(id) indexing partial; Index created. SQL> alter table pink_floyd add primary key(id); alter table pink_floyd add primary key(id) * ERROR at line 1: ORA-01408: such column list already indexed
It clearly doesn’t make sense to create a Partial Unique Index or on a Non-Unique Index policing a PK or Unique Key constraint as it would be impossible to use such an index to guarantee the required unique property. With missing index entries associated with non-indexed partitions, how can Oracle determine whether a value from new row already exists or not ? It can’t and hence Oracle doesn’t permit the creation of such a Partial Index.
Partial Indexes can potentially be extremely useful in reducing unnecessary storage requirements, reducing index maintenance overheads and in improving performance by reducing index block accesses.
But they’re only useful (possible) with Partitioned Tables.
I’ll next look at another cool index improvement introduced with the Oracle 12c Database that’s associated with Partitioning, Asynchronous Global Index Maintenance …
12c Partial Indexes For Partitioned Tables Part I (Ignoreland) July 8, 2013
Posted by Richard Foote in 12c, Oracle Indexes, Partial Indexes, Partitioning.14 comments
In my opinion, one of the unsung “Heroes” of the new 12c Oracle database are improvements associated with Partitioning. 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 now create both local and (importantly) global indexes on only a subset of partitions within a partitioned table. This provides us with the flexibility to say only create partitions with data that would make sense to index, to not index current partitions where perhaps data insert performance is paramount, etc. Additionally and just as importantly, the CBO is aware of the indexing characteristics of individual partitions and can access partitions in differing manners accordingly.
To illustrate, a simple little demo as usual 🙂 Firstly, I’ll create a partitioned table with the new INDEXING clause:
SQL> create table pink_floyd (id number, status varchar2(6), name varchar2(30)) INDEXING OFF partition by range (id) (partition pf1 values less than (1000001), partition pf2 values less than (2000001) INDEXING OFF, partition pf3 values less than (maxvalue) INDEXING ON); Table created.
The INDEXING clause determines whether or not the partition is to be indexed. It can be set at the table level and so set the default behaviour for the table or at the individual partition/subpartition level.
In the above example, I’ve set INDEXING OFF at the table level and so indexing by default is not be enabled for the table partitions. Therefore the PF1 partition is not indexed by default. The PF2 partition is explicitly set to also not be indexed but the PF3 index is explicitly set (INDEXING ON) to enable indexing and so override the table level default.
Let’s now populate the table with some basic data:
SQL> insert into pink_floyd select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> update pink_floyd set status = 'OPEN' where id > 2000000 and mod(id,10000)=0; 100 rows updated. SQL> commit; Commit complete.
Most of the data has a STATUS column value of ‘CLOSED’ but I’ve updated a few rows within just the last partition with a STATUS set to ‘OPEN’.
Let’s now create an index on this STATUS column and collect table statistics:
SQL> create index pink_floyd_status_i on pink_floyd(status); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'PINK_FLOYD', estimate_percent=>null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS SIZE 5'); PL/SQL procedure successfully completed. SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I'; INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN -------------------- ---------- ----------- ------- PINK_FLOYD_STATUS_I 3000000 9203 FULL
By default, an index will include all partitions in a table, regardless of the INDEXING table clause setting. So this index covers all 3M rows in the table and currently has 9203 leaf blocks. The new INDEXING column in DBA_INDEXES shows us that this index is a FULL (non-Partial) index.
We can of course get the data of interest (STATUS = ‘OPEN’) via this index now:
SQL> select * from pink_floyd where status = 'OPEN';
100 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 964 | 24100 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD | 964 | 24100 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | PINK_FLOYD_STATUS_I | 100 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
110 consistent gets
0 physical reads
0 redo size
4479 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
However, we can potentially also run a query based on just the last partition as all the ‘OPEN’ statuses of interest only reside in this last partition:
SQL> select * from pink_floyd where status = 'OPEN' and id > 2000001;
100 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2475 | 4 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD | 99 | 2475 | 4 (0)| 00:00:01 | 3 | 3 |
|* 2 | INDEX RANGE SCAN | PINK_FLOYD_STATUS_I | 100 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">2000001)
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
110 consistent gets
0 physical reads
0 redo size
2787 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
Currently however, the index includes data from all table partitions, even though we’re only really interested in using the index to retrieve the less common ‘OPEN’ status that resides in only the last table partition. With 12c, there is now the capability to only index those partitions that are of interest to us, which with proper design can also be implemented such that only those column values of interest are included within an index.
I’m going to drop and create the index as a “Partial” Index:
SQL> drop index pink_floyd_status_i; Index dropped. SQL> create index pink_floyd_status_i on pink_floyd(status) indexing partial; Index created. SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I'; INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN -------------------- ---------- ----------- ------- PINK_FLOYD_STATUS_I 1000000 3068 PARTIAL
The new INDEXING PARTIAL clause means only those table partitions with INDEXING ON are to be included within the index.
Notice how the index, which is a Global, Non-Partitioned Index, now only has 1M entries (not all 3M as previously) and with 3068 leaf blocks is only 1/3 of what it was previously. The INDEXING column now denotes this as a “Partial” index.
If we run the query again that only explicitly references the last “active” table partition:
SQL> select * from pink_floyd where status = 'OPEN' and id >= 2000001;
100 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2500 | 4 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD | 100 | 2500 | 4 (0)| 00:00:01 | 3 | 3 |
|* 2 | INDEX RANGE SCAN | PINK_FLOYD_STATUS_I | 33 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">=2000001)
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
110 consistent gets
0 physical reads
0 redo size
2787 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
We see that the index is used as it was previously. By stating with the ID > 2000001 predicate we’re only interested in data that can only reside in the last table partition, the partition with INDEXING ON, the CBO knows the index can be used to retrieve all the rows of interest. If we know the application will only extract data in this manner, all is well with our smaller, Partial index.
However, if it’s possible within the application to perhaps search for STATUS values from other partitions, that have INDEXING OFF:
SQL> select * from pink_floyd where status = 'OPEN';
100 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2500 | 2474 (1)| 00:00:01 | |
| 1 | VIEW | VW_TE_2 | 99 | 3465 | 2474 (1)| 00:00:01 | |
| 2 | UNION-ALL | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD | 33 | 825 | 4 (0)| 00:00:01 | ROWID | ROWID
|* 4 | INDEX RANGE SCAN | PINK_FLOYD_STATUS_I | 100 | | 3 (0)| 00:00:01 | |
| 5 | PARTITION RANGE ITERATOR | | 66 | 1650 | 2470 (1)| 00:00:01 | 1 | 2
|* 6 | TABLE ACCESS FULL | PINK_FLOYD | 66 | 1650 | 2470 (1)| 00:00:01 | 1 | 2
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PINK_FLOYD"."ID">=2000001 OR "PINK_FLOYD"."ID" IS NULL)
4 - access("STATUS"='OPEN')
6 - filter("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16341 consistent gets
8204 physical reads
0 redo size
2787 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
We notice the index is still used to efficiently find those rows of interest from the last partition, but a Full Table (Partition) Scan is performed to search for data from the other two partitions, for which with INDEXING OFF means the index does not contain entries that reference these partitions. As a result, this query is now much more expensive than it was previously as the index can not be used to exclusively find the rows of interest. The CBO within the one execution plan uses the index where it can and a full scan of the other partitions where it can’t use the index.
If however we were a little cleverer in how we designed our table and also created table subpartitions based on the STATUS column so that rows of interest resided in their own subpartitions and only set INDEXING ON for these subpartitions:
SQL> create table pink_floyd (id number, status varchar2(6), name varchar2(30))
indexing off
partition by range (id) subpartition by list(status)
subpartition template
(subpartition closed values ('CLOSED') indexing off, subpartition open values ('OPEN') indexing on)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001),
partition pf3 values less than (maxvalue))
enable row movement;
Table created.
Notice how only the subpartitions with a STATUS of ‘OPEN’ are now to be indexed. If we populate the table with the exact same data as before, we find the table and partial index have the follow characteristics:
SQL> select subpartition_position, subpartition_name, num_rows, indexing from dba_tab_subpartitions where table_name = 'PINK_FLOYD';
SUBPARTITION_POSITION SUBPARTITION_NAME NUM_ROWS IND
--------------------- -------------------- ---------- ---
1 PF1_CLOSED 1000000 OFF
2 PF1_OPEN 0 ON
1 PF2_CLOSED 1000000 OFF
2 PF2_OPEN 0 ON
1 PF3_CLOSED 999900 OFF
2 PF3_OPEN 100 ON
SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN
-------------------- ---------- ----------- -------
PINK_FLOYD_STATUS_I 100 1 PARTIAL
We can see that only the subpartitions with data of interest are now indexed. The resultant Partial global non-partitioned index is now tiny, with just the 100 index entries of interest residing in a single leaf block.
Just as importantly, a query searching for this data across the whole table is now extremely efficient and can be fully serviced by this tiny Partial index:
SQL> select * from pink_floyd where status = 'OPEN';
100 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2500 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD | 100 | 2500 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | PINK_FLOYD_STATUS_I | 100 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
4479 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
The query can now be fully serviced by the Partial index as all subpartitions that could contain data of interest are indexed and because the data of interest is all neatly clustered within the table subpartitions, can be retrieved with far fewer consistent gets than previously.
If we ever wanted to access those STATUS values of ‘CLOSED’, the CBO can only do so via a Full Table Scan as such values are not indexed. However, as these values represent the vast majority of rows in the table, the Full Table Scan would be the most appropriate and efficient manner to access these rows any-ways.
The new Partial Index capabilities introduced in Oracle 12c enables us to easily use global (and local) indexes to just index data of interest without having to change the application. Such a capability has many potential uses.
More on Partial Indexes to come soon in Part II.
12c: Intro To Multiple Indexes On Same Column List (Repetition) July 2, 2013
Posted by Richard Foote in 12c, Multiple Indexes, Oracle Indexes.6 comments
From an indexing perspective, one of the bigger ticket items introduced with Oracle Database 12c is the new capability to create multiple indexes on the same column list. It’s even a feature listed in the New Features Guide 🙂 This can be particularly useful when you want to change the index definition in some way but you want to reduce the time in which the corresponding columns might not indexed.
Previous to 12c, you could not create an index if the same column list is already indexed and would generate an ORA-01408: such column list already indexed error. So, if you wanted to change an index from being say a B-Tree index to a Bitmap index, or from being Unique to Non-Unique or from being Non-Partitioned to Partitioned in same manner, etc. then you had to first drop the index and re-create it again as required. This means for the period in which the index is being re-created (which could be a considerable period for a larger index), the column list is not covered by an index, which might prove to be problematic.
Here, we create a partitioned table and create a Non-Unique, Non-Partitioned index on the ID column:
SQL> create table ziggy (id number, name varchar2(30)) partition by range (id) (partition ziggy1 values less than (1000), partition ziggy2 values less than (2000), partition ziggy3 values less than (maxvalue)); Table created. SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level <=5000; 5000 rows created. SQL> commit; Commit complete. SQL> create index ziggy_id_i1 on ziggy(id); Index created. SQL> alter table ziggy add constraint ziggy_pk primary key(id); Table altered.
If we decide for whatever reason we want to have say a Unique index instead:
SQL> create unique index ziggy_id_i2 on ziggy(id); create unique index ziggy_id_i2 on ziggy(id) * ERROR at line 1: ORA-01408: such column list already indexed
No good, we can’t, even if we make it initially INVISIBLE:
SQL> create unique index ziggy_id_i2 on ziggy(id) invisible; create unique index ziggy_id_i2 on ziggy(id) invisible * ERROR at line 1: ORA-01408: such column list already indexed
Because the index is used to police a PK constraint, we can’t even just drop the index:
SQL> drop index ziggy_id_i1; drop index ziggy_id_i1 * ERROR at line 1: ORA-02429: cannot drop index used for enforcement of unique/primary key
We have to first drop or disable the PK constraint, then drop the index, then re-create the index.
SQL> alter table ziggy drop primary key; Table altered. SQL> drop index ziggy_id_i1; Index dropped. SQL> alter table ziggy add primary key(id) using index (create unique index ziggy_id_i2 on ziggy(id)); Table altered.
This means the constraint is not automatically enforced (unless we disable it with validate, thus locking the table) and the ID column is not available via an index during the entire duration of creating the new index.
The 12c database has given us more flexibility in this regard.
Providing we create the index as INVISIBLE (meaning there is only ever the one Visible index on the column list), we can now create multiple indexes on the same column list, providing the new index has a different characteristic to existing indexes. So with a 12c database:
SQL> create unique index ziggy_id_i2 on ziggy(id) invisible; Index created.
We now have two indexes on the same column list (the ID column). An attempt however to create or alter an index such that two visible indexes have the same column list will fail:
SQL> alter index ziggy_id_i2 visible; alter index ziggy_id_i2 visible * ERROR at line 1: ORA-14147: There is an existing VISIBLE index defined on the same set of columns.
This means we can now (say) replace the index policing the PK constraint quicker (or convert a B-Tree to a Bitmap index or convert a Non-Partitioned index to a Partitioned index, etc.) as we don’t now have to wait for the new index to be created first:
SQL> alter table ziggy drop primary key; Table altered. SQL> drop index ziggy_id_i1; Index dropped. SQL> alter index ziggy_id_i2 visible; Index altered. SQL> alter table ziggy add constraint ziggy_pk primary key(id); Table altered.
We can create as many indexes as we like on the ID, providing only one index is ever visible at a time and providing all the indexes have some form of differing characteristic. For example, all these indexes could potentially all co-exist:
SQL> create index ziggy_id_i3 on ziggy(id) local invisible; Index created. SQL> create bitmap index ziggy_id_i4 on ziggy(id) local invisible; Index created. SQL> create index ziggy_id_i5 on ziggy(id) reverse invisible; Index created.
Of course, having many multiple indexes on the same column list in this manner is very likely a very bad idea …
Richard Foote's Oracle Blog 