jump to navigation

12c Online Partitioned Table Reorganisation Part I (Prelude) January 7, 2014

Posted by Richard Foote in 12c, Oracle Indexes, Partitioning, Unusable Indexes, Update Indexes, Update Indexes Online.
2 comments

First post for 2014 !!

Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes.

If we look at the following example:

SQL> create table muse2 (id number, status varchar2(6), name varchar2(30));

Table created.

SQL> insert into muse2 select rownum, 'CLOSED', 'DAVID BOWIE'
     from dual connect by level <= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse2_id_pk on muse2(id);

Index created.

SQL> alter table muse2 add constraint muse2_id_pk primary key(id);

Table altered.

SQL> create index muse2_status_i on muse2(status);

Index created.

So we have a table with a couple of indexes. We can’t move the table using the ONLINE option as it’s only applicable for Index Organized Tables:

SQL> alter table muse2 move online;
alter table muse2 move online
 *
 ERROR at line 1:
 ORA-01735: invalid ALTER TABLE option

If in one session, we have a current transaction on the table (i.e. not committed):

SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

An attempt to MOVE the table in another session will fail with locking issues:

SQL> alter table muse2 move;
alter table muse2 move
 *
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

On the other hand, if the table MOVE command proceeds:

SQL> alter table muse2 move;

Table altered.

It in turn locks other transactions out during the duration and leaves all indexes in an UNUSABLE state:

SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST');
insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST')
 *
 ERROR at line 1:
 ORA-01502: index 'BOWIE.MUSE2_ID_PK' or partition of such index is in unusable state

SQL> select index_name, status from dba_indexes
     where table_name='MUSE2';

 INDEX_NAME      STATUS
 --------------- --------
 MUSE2_ID_PK     UNUSABLE
 MUSE2_STATUS_I  UNUSABLE

If we now look at a similar Partitioned Table example:

SQL> create table muse (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (1000001),
4 partition p2 values less than (2000001),
5 partition p3 values less than (maxvalue));

Table created.

SQL> insert into muse select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse_id_pk on muse(id);

Index created.

SQL> alter table muse add constraint muse_id_pk primary key(id);

Table altered.

SQL> create index muse_status_i on muse(status) local;

Index created.

Similar locking and indexing issues occur if we try and reorganise a partition, even if we UPDATE INDEXES on the fly. For example, if we have an active transaction in one session:

SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

While in another session:

SQL> alter table muse move partition p3 tablespace users update indexes;
alter table muse move partition p3 tablespace users update indexes
 *
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

We get the same old resource busy error. If we tried things the other way around, so in one session we first attempt to move a table partition:

SQL> alter table muse move partition p3 tablespace users update indexes;
alter table muse move partition p3 tablespace users update indexes
 *
 ERROR at line 1:
 ORA-14327: Some index [sub]partitions could not be rebuilt

We can eventually get the above error if in another session we then attempt to insert a new row into this partition:

SQL> insert into muse values (3000002, 'OPEN', 'ZIGGY STARDUST');

1 row created.

which in turn hangs for the period of time until the above error is generated.

The associated local index is now not a happy chappy:

SQL> select index_name, partition_name, status from dba_ind_partitions
where index_name='MUSE_STATUS_I'
union
select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME  STATUS
--------------- --------------- --------
MUSE_ID_PK                      VALID
MUSE_STATUS_I   P1              USABLE
MUSE_STATUS_I   P2              USABLE
MUSE_STATUS_I   P3              UNUSABLE

So Moving tables and table partitions around can all get a bit messy, especially if high availability is required.

The DBMS_REDEFINITION package is designed specifically to enable the online redefinition of objects, but it has its own issues and is not as clean and simplistic as a simple MOVE operation when we just want to perform a table re-org.

So what was has changed in Oracle Database 12c ?

We’ll see in the next post although the title here does rather give it away :)

12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model) August 7, 2013

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Global Indexes, Oracle Indexes, Partitioning, Unique Indexes.
3 comments

As I discussed previously in Part I, the space occupied by orphaned row entries associated with asynchronously maintained global indexes is not automatically reclaimed by subsequent DML operations within the index. Hence the need to clean out these orphaned index entries via the various options discussed in Part II.

However, a good question by Jason Bucata asked what about Unique indexes. “If the index entries aren’t marked deleted but are truly still “there” in the structure, does that mean you can’t use this feature if any global indexes are unique” ?

So now I need a Part III to answer this question :)

So same demo setup as before but this time with a Unique index on the ID column:

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.

SQL> create unique index muse_id_i on muse(id);

Index created.

Let’s now drop a table partition and confirm we indeed do have orphaned unique index entries:

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE';

INDEX_NAME       NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
-------------- ---------- ---------- ----------- -------- ---
MUSE_ID_I         3000000      11264        8216 VALID    YES

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                  9216    3000000     1000000

If we take a look at a partial block dump of the first (left-most) index leaf block at this stage:

Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0029239b
Leaf block dump
===============
header address 360728164=0x15804664
kdxcolev 0
KDXCOLEV Flags = – – -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 00
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 01
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 02
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 03
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 04
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 05
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 06
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 07
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 08
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 09
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11

As discussed previously, the index leaf block remains “untouched” after the drop table partition operation and has no index entries actually marked as “deleted”. However, just take a note of the rowid values of the first 10 rows. I’m now going to reinsert new rows with an ID between 1 and 10 that were previously deleted as part of dropping the table partition …

SQL> insert into muse select rownum, 42, 'ZIGGY STARDUST' from dual connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                  9216    3000000      999990

We can see that the number of so-called deleted leaf rows is now only 999990 and has decreased by the 10 rows we’ve inserted.

If we take a look now at the first index leaf block again:

Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0004.012.0000079d 0x014045c7.0122.44 –U- 10 fsc 0x0000.00292503
Leaf block dump
===============
header address 360612452=0x157e8264
kdxcolev 0
KDXCOLEV Flags = – – -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 86
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 87
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 88
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 89
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8a
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8b
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8c
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8d
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8e
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8f
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11

We notice that the first 10 index entries now have different rowids from the previous block dump.

So this is an exception to the rule. With a Unique index, Oracle will indeed reuse the storage occupied by the orphaned Unique index entry if the same unique value as an orphaned value is subsequently re-inserted. This is not the case with Non-Unique indexes, even if such Non-Unique indexes are used to police either a PK or Unique Key constraint.

So the new valid index entries and any existing orphaned entries can be read and/or ignored as necessary:

SQL> select * from muse where id between 1 and 100;

ID       CODE NAME
---------- ---------- --------------------
1         42 ZIGGY STARDUST
2         42 ZIGGY STARDUST
3         42 ZIGGY STARDUST
4         42 ZIGGY STARDUST
5         42 ZIGGY STARDUST
6         42 ZIGGY STARDUST
7         42 ZIGGY STARDUST
8         42 ZIGGY STARDUST
9         42 ZIGGY STARDUST
10        42 ZIGGY STARDUST

10 rows selected.

Execution Plan
----------------------------------------------------------

Plan hash value: 2515419874

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |     1 |    23 |     4   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE      |     1 |    23 |     4   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | MUSE_ID_I |   100 |       |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=1 AND "ID"<=100)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics
----------------------------------------------------------

5  recursive calls
0  db block gets
7  consistent gets
0  physical reads
0  redo size
974  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
10  rows processed

However, if new unique values are inserted into the table but with ID values that didn’t previously exist:

SQL> insert into muse select rownum+3000000, 42, 'ZIGGY STARDUST'
from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                 11264    4000000      999990

We notice that the number of so-called deleted leaf entries remains the same after inserting the 1M new rows.

So in this scenario, the effectively “empty” leaf blocks containing nothing but orphaned unique index entries are not re-cycled and reused by subsequent index block splits as they would have been had they contained nothing but deleted index entries.

So Unique indexes in the unlikely event that such unique values are subsequently reinserted are an exception to the general rule of orphaned global index entries having to be “cleaned out”.

12c Asynchronous Global Index Maintenance Part II (The Space Between) August 6, 2013

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Coalesce Cleanup, dbms_part.cleanup_gidx, Index Coalesce, Oracle Indexes, Partitioning.
7 comments

In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state.

If we look at a partial 11g global index block dump after dropping a table partition (eg. the MUSE_ID_I in the previous demo):

Block header dump: 0x01028750
Object id on Block? Y
seg/obj: 0x130ac csc: 0x00.3c7323 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x1028748 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0006.001.00000f91 0x00c03e16.0177.02 —- 378 fsc 0x1c0b.00000000
Leaf block dump
===============
header address 130573412=0x7c86464
kdxcolev 0
KDXCOLEV Flags = – – -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 378
kdxlenxt 16942929=0x1028751
kdxleprv 16942927=0x102874f
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3b
row#1[8002] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3c
row#2[7985] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3d
row#3[7968] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3e
row#4[7951] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3f

….

We notice that all index entries that reference the dropped table partition are marked as deleted. They all have a D (deleted) flag set and have been locked by the drop table partition transaction in ITL slot 2. So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.

However, if we look at a block dump of the same index in an Oracle 12c database following a table partition being dropped:

Block header dump: 0x018000e0
Object id on Block? Y
seg/obj: 0x16bbe csc: 0x00.26ae40 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x18000d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0026ae40
Leaf block dump
===============
header address 364741220=0x15bd8264
kdxcolev 0
KDXCOLEV Flags = – – -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25166049=0x18000e1
kdxleprv 25166047=0x18000df
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3b
row#1[8002] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3c
row#2[7985] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3d
row#3[7968] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3e
row#4[7951] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3f

We notice there are no deleted index entries, the index remains totally untouched by the drop table partition operation. So the good news is that dropping/truncating a table partition while updating global indexes is extremely fast and efficient while the indexes remain hunky dory as subsequent index range scans can ignore any rowids that don’t reference existing table partitions of interest.

However, the bad news is that during subsequent index DML operations, Oracle does not know which index entries are valid and which are not and so the space used by these “orphaned” index entries can not be automatically reclaimed and reused as it can with conventionally deleted index entries. Therefore, we need some other way to clean out the orphaned index entries.

There are a number of possible ways to do this. One way is to simply rebuild the global index (or index partition):

SQL> alter index muse_code_i rebuild partition code_p1;

Index altered.

SQL> select index_name, null partition_name, orphaned_entries, 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, orphaned_entries, 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  ORP  NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- ------------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO    1000000       2944        2758 USABLE
MUSE_CODE_I  CODE_P2         YES   1000000       4352        4177 USABLE
MUSE_ID_I                    YES   2000000       9216        5849 VALID

Effective, but relatively expensive as this requires the entire index structure to be rebuilt from scratch. Depending on the scale and distribution of the orphaned index entries, another possibly cheaper alternative is to use the new CLEANUP coalesce clause:

SQL> alter index muse_id_i coalesce cleanup;

Index altered.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MUSE_ID_I', estimate_percent=>null);

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, orphaned_entries, 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, orphaned_entries, 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  ORP  NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- --- --------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO    1000000       4224        4137 USABLE
MUSE_CODE_I  CODE_P2         YES   1000000       4352        4177 USABLE
MUSE_ID_I                    NO    2000000       9216        5849 VALID

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME            LF_ROWS DEL_LF_ROWS
------------ ---------- -----------
MUSE_ID_I       2000000           0

This visits each index leak block and removes all the orphaned index entries as part of the coalesce process. Note this is a more “powerful” version of coalesce as a standard coalesce is not aware of orphaned index entries and will only coalesce the index without actually removing the orphaned index.

Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes. Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job:

SQL> exec dbms_part.cleanup_gidx;

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, orphaned_entries, 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, orphaned_entries, 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  ORP   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- --- ---------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO     1000000       2944        2758 USABLE
MUSE_CODE_I  CODE_P2         NO     1000000       4352        4177 USABLE
MUSE_ID_I                    NO     2000000       9216        5849 VALID

We notice the last index partition has now been cleaned out and no longer has orphaned index entries.

So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience.

12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?) August 2, 2013

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Oracle Indexes, Partitioning.
4 comments

I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c.

Let’s start by creating the same partitioned table and global indexes as previously:

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> 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> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

If we look at the current state of affairs, all is currently hunky dory:

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries 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, orphaned_entries 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   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1500000       4224        4137 USABLE   NO
MUSE_CODE_I  CODE_P2            1500000       4352        4177 USABLE   NO
MUSE_ID_I                       3000000       9216        8633 VALID    NO

However, a difference to note here is a new data dictionary column called ORPHANED_ENTRIES which denotes whether the index currently has any orphaned index entries. What are these ? We shall see …

Let’s see how expensive it is to now drop the same table partition while updating the global indexes:

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=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129249
redo size                                                         105069544

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

Elapsed: 00:00:00.76

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=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129314
redo size                                                         105083724

As we can see, this is significantly different than before when this was a relatively slow and expensive exercise. At just 65 block gets and only 14180 bytes of redo, this is now about the same cost as dropping the partition without updating the global indexes. How can this be ?

If we now look at the status of our global indexes:

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
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, orphaned_entries
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   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1000000       4224        4137 USABLE   YES
MUSE_CODE_I  CODE_P2            1000000       4352        4177 USABLE   YES
MUSE_ID_I                       2000000       9216        5849 VALID    YES

We notice that indeed, the index entries have been reduced (for example, only 2M index entries now in MUSE_ID_I instead of 3M) as if the indexes have been updated. However, we also notice that although the indexes are both marked as now having orphaned entries, they’re still in a USABLE state.

Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly.

However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries.

So if we now select values via the ID column index that only spans data in the dropped table partition:

SQL> select * from muse where id between 42 and 420;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2515419874
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |     1 |    23 |     4   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE      |     1 |    23 |     4   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | MUSE_ID_I |     1 |       |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=42 AND "ID"<=420)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics
----------------------------------------------------------

0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
470  bytes sent via SQL*Net to client
532  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

We notice that quite correctly, no rows are now returned.

The partitioned global index on the CODE column likewise only returns valid data when accessed:

SQL> select * from muse where code=42;

ID       CODE NAME
---------- ---------- ------------------------------
1000042         42 DAVID BOWIE
1100042         42 DAVID BOWIE
1200042         42 DAVID BOWIE
1300042         42 DAVID BOWIE
1400042         42 DAVID BOWIE
1500042         42 DAVID BOWIE
1700042         42 DAVID BOWIE
1600042         42 DAVID BOWIE
1900042         42 DAVID BOWIE
1800042         42 DAVID BOWIE
2000042         42 DAVID BOWIE
2100042         42 DAVID BOWIE
2200042         42 DAVID BOWIE
2300042         42 DAVID BOWIE
2400042         42 DAVID BOWIE
2500042         42 DAVID BOWIE
2600042         42 DAVID BOWIE
2700042         42 DAVID BOWIE
2900042         42 DAVID BOWIE
2800042         42 DAVID BOWIE

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4070098220
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |    20 |   460 |    24   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                     |             |    20 |   460 |    24   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE        |    20 |   460 |    24   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                         | MUSE_CODE_I |    20 |       |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CODE"=42)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics

----------------------------------------------------------
1  recursive calls
0  db block gets
25  consistent gets
2  physical reads
0  redo size
1147  bytes sent via SQL*Net to client
554  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20  rows processed

As we can see, only valid data belonging to the non-dropped partitions are returned via the index, even though the index has orphaned index entries that reference the dropped table partition.

If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME            LF_ROWS DEL_LF_ROWS
------------ ---------- -----------
MUSE_ID_I       3000000     1000000

We see that the index statistics is indicating that there are 1M so-called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.

So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do. So what’s the catch ?

Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:

SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:03:56.52

Execution Plan
----------------------------------------------------------

Plan hash value: 1731520519
-------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | MUSE |       |            |          |
|   2 |   COUNT                        |      |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(LEVEL<=1000000)

Statistics
----------------------------------------------------------

700  recursive calls
758362  db block gets
53062  consistent gets
5069  physical reads
355165692  redo size
869  bytes sent via SQL*Net to client
903  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
75  sorts (memory)
0  sorts (disk)
1000000  rows processed

SQL> commit;

Commit complete.

This is notably slower and more expensive than if the index entries had actually been deleted because Oracle is not able to simply identify and overwrite the orphaned index entries during DML operations as they’re not physically marked as deleted. If we look at the INDEX_STATS after inserting these new rows:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                BLOCKS    LF_ROWS DEL_LF_ROWS
--------------- ---------- ---------- -----------
MUSE_ID_I            12288    4000000     1000000

SQL> analyze index muse_code_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                BLOCKS    LF_ROWS DEL_LF_ROWS
--------------- ---------- ---------- -----------
MUSE_CODE_I           9216    2000000      500000

We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead  that none of the space occupied by the orphaned rows has been reused. This in the end means accessing more index blocks, potentially performing more block splits, more newer blocks having to be generated and overall more work having to be performed than would have been necessary if they had just been plain deleted index entries.

So how we actually get rid of these orphaned index entries ? I look at a number of different techniques we can use in Part II. And yes, good old block dumps are on their way as well :)

Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed) July 26, 2013

Posted by Richard Foote in Global Indexes, Oracle Indexes, Partitioning.
1 comment so far

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 …

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.
2 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.
8 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.

Partition Pruning – Some Examples (Knives Out) September 3, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Partitioning.
4 comments

Following on from my last post where I looked at the use of partition pruning vs. the use of an index, I thought it might be worthwhile to just go through a few additional basic examples of where partition pruning can come into play.

I’ll also include a little example of how execution plans may not favour indexes as much with partitioned tables as the resultant cost of a Full Table Scan can be reduced significantly due to partition pruning.

All the examples use exactly the same tables as created in the previous post. For a full listing of the examples and resultant executions plans, look at this Partition Pruning Demo. You may want to open up the demo in a different browser window to make it easier to follow.

In Example 1:

SQL> select * from big_album_sales where release_date between ’01-JUN-2003′ and ’13-APR-2004′;

the CBO can determine that all the data can only belong in Partitions 3 and 4 (as only only years that need to be referenced are 2003 and 2004) and so not have to visit any of the other partitions. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 3 – 4.

In Example 2:

SQL> select * from big_album_sales where release_date > ’01-JUN-2006′;

All the possible values must exist from partition 6 which stores all the 2006 data and onwards through to the last partition in the table which is partition 8. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 6 – 8.

In Example 3:

SQL> select * from big_album_sales where release_date between ’01-JUN-2006′ and ’30-NOV-2006′ or release_date > ’14-JUN-2008′;

The only possible partitions the data can reside in are partition 6 or partition 8 (for the years 2006 or 2008). In this case, the CBO performs the PARTITION RANGE OR step which visits only these specific partitions.

In Example 4:

SQL> select * from big_album_sales where release_date is null;

All NULL values must reside in the last partition, as the table was created with the last partition having a MAXVALUE boundary. That being the case, the CBO need only visit the last partition (8) within the table to find all possible NULL values of RELEASE_DATE.

Because partition pruning can make performing a so-called Full Table Scan much more efficient as only those partitions with possible data need only be accessed, in some rare cases this can make the CBO appear to ignore reasonable indexes that may be used and accessed in an equivalent non-partitioned table.

Note that the data includes a series of RELEASE_DATE values but many of the dates actually have the same timestamp component as all the row values were determined and loaded in a very quick manner. As a result, the CBO considers there are actually 718 rows per distinct occurrence of a RELEASE_DATE value. This fact is significant when costing an equality predicate.

In Example 5:

SQL> select * from big_album_sales where release_date = ’01-JUN-2006′;

we’re only interested in the one specific date value but Oracle still favours the Full Table Scan (FTS). Note this value can only possibly exist in partition 6 associated with data from 2006 and so the FTS need only actually access partition 6. Oracle assumes it will retrieve approximately 718 rows which is too costly via the index as partition pruning can make the FTS relatively efficient. Note that the actual cost of the FTS is 303.

In Example 6:

SQL> select /*+ index(b) */ * from big_album_sales b where release_date = ’01-JUN-2006′;

a hint is used to force the use of the associated index on the RELEASE_DATE column. Note in this example, it’s actually a much cheaper option (there are far fewer consistent gets) as no rows are actually being retrieved but the cost of 722 is significantly greater than that of the FTS (303) as Oracle is expecting to retrieve and access 718 rows.

In Example 7:

SQL> select * from big_album_sales2 where release_date = ’01-JUN-2006′;

we run exactly the same statement but this time on the non-partitioned table and notice that the execution plan is totally different. Oracle is indeed using the index because in this example the FTS is so much more expensive as it needs to read the entire table and not just the one partition (or approximately 1/8 of the table) as it does with the partitioned table example.

Lots more on partitioning to come in the future …

Regular Index vs. Partitions (The Best Of Both Worlds) August 28, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle Indexes, Partitioning.
17 comments

When asking for requests and suggestions on what I could cover in the blog, Brian Tkatch asked “When is a partition better than just a regular index ?”

As it’s a very good question, I thought I might spend a few moments attempting to provide some kind of meaningful answer.

The first point I would make however, is that indexes and partitions are not at all mutually exclusive. A table of course could very well benefit from being both partitioned and indexed. Most partitioned tables have indexes and indeed indexes can in turn be partitioned.

The second point I would make is that there are two key advantages or reasons why it might be beneficial to partition a segment. They are:

  1. Better Manageability
  2. Better Performance

This post can be viewed as being but an introduction to the potential performance benefits of partitioned segments. I will leave the topic of better manageability for another time.

The point of Brian’s question is when can the performance benefits available to partitioned segments out perform and be a better option than that of a possible associated index.

As I’ve discussed a number of times, there are no magic numbers or ratios as to when an index is the most appropriate and cost effective access path. If fact, the link shows a theoretical example of when a Full Table Scan (FTS) is by far the cheaper option when selecting just 1% of data. Generally speaking though, the higher the percentage of returned rows, the more likely a FTS is going to be the more efficient option.

However, a Full Table Scan is just that, a FULL Table Scan. Oracle needs to read the entire table, each and every block below the segment High Water Mark. So if a query needed to retrieve just 5% of the data, it’s quite likely it would perform a FTS because it’s possibly a cheaper and less costly alternative than using an associated index (the above link explains why) but it would need to access 100% of the table to do so. That means it would need to effectively access 95% of the data and simply just throw away the rows so it can access the 5% of data it actually requires in a more efficient manner than possible using an index access path.

That’s potentially (say) 95% of the work and resources for no real reason other than Oracle has no choice but to access all the data when performing a FTS. That’s potentially 95% of wasted resources, 95% of wasted effort. Yes, a TFS might be more efficient than using an index but accessing unnecessarily say 95% of data sounds rather costly nonetheless.

Wouldn’t it be nice if somehow we could get the benefits of performing a FTS (multiblock reads, reading a specific block just the once, etc.) but only read the say 5% of the data that we were actually interested in (or something significantly less than the 100%)? Somehow combine the benefits of a FTS with the benefits provided by an index, that being only needing to access data that is of direct interest.

Enter Partitioning.

Partitioning provides Oracle with another level of granularity (or levels of granularity with composite partitions) when accessing a table. It’s no longer an question of using an index or reading the entire table. Oracle can now either use an index, access the entire table or with partitioning access just those partitions (or bits) of a table that can only possibly contain data of interest.

By partitioning a table, Oracle knows that data belonging to the partitioning keys must belong in a specific partition. The logical table is effectively broken down into multiple physical segments with each partition segment containing only data associated with the partitioning keys or columns.

If the say 5% of data that’s of interest only logically resides in the one or whatever number of partitions, then there’s no need for Oracle to access the data in all the other partitions, containing the other say 95% of unwanted data. The Cost based Optimizer (CBO) can automatically take advantage of “partition pruning” and only needs to access the partition or those partitions that only can contain the data of interest.

Oracle can now potentially perform a so-called “FTS” of the table, but only actually accesses and reads the 5% (or significantly less than the 100%) of data that’s potentially of direct interest by accessing just those partitions it needs to.

At a simplistic level, indexes are best when accessing a relatively “small” amount of data. A FTS is best when accessing a relatively “large” amount of the data and partitions can be extremely useful and beneficial when accessing an amount of data somewhere between the two. As such, partitioning is more likely to be beneficial in a Data Warehouse or Reporting environment where accessing a relatively high percentage of data is common.

This demo of indexes vs. a FTS vs. partition pruning shows how a FTS outperforms an index when accessing approximately 1/8 of data in a table but an appropriately partitioned table out performs both options.

More on partitioning in the future.

Local Index Issue With Partitioned PK and Unique Key Constraints December 20, 2007

Posted by Richard Foote in Constraints, Index Access Path, Local Indexes, Oracle Indexes, Partitioning, Performance Tuning, Unique Indexes.
11 comments

Nuno Souto (Noons) also asked a really interesting question on my Differences between Unique and Non-Unique Indexes blog entry (comment 4) that I thought it worthy of a separate blog entry to do the answer justice. The question was:

“Isn’t it still the case that unique indexes cannot be locally partitioned unless the partition key is part of the index key? Not sure if 11g removes this. If still so, that would weigh heavily in favour of non-unique indexing for PK on a table potentially requiring local index partitions.”

Simplistically, the answer to the first part is Yes it is still the case, even in 11g and the answer to the second part is No, it wouldn’t weigh heavily in favour of non-unique indexing for PK on a table requiring local index partitions. It wouldn’t actually be a consideration at all.

Let me explain why.

Firstly, there is a really really good reason why Oracle doesn’t allow us to create a Unique Index in which the Partition key is not part of a Local Index. It’s called protecting us from ourselves !!

Let’s start by mentioning constraints again.

Remember, the main reason we have indexes policing PK and Unique constraints is so that Oracle can very quickly and efficiently determine whether or not a new value already exists. Do a quick index look-up, is the value there, yes or no, allow the insert (or update), yes or no.

Just imagine for one moment what would happen if Oracle actually allowed us to create a Unique Local index in which the index didn’t include the partitioned column(s).

Lets say a table is Range Partitioned on column ‘A’ and we try and create a Unique Local index on just column ‘B’. Let’s assume we have (say) 500 table partitions meaning we must therefore have 500 local index partitions as well. When we insert a new value for our unique index for value B, it will attempt to do so in the corresponding local index partition as governed by the value A for the new row. However Oracle can’t just check this one index partition for uniqueness to ensure value of column B doesn’t already exist, Oracle would need to check all 500 index partitions because it would be possible for our new value of column B to potentially have previously been inserted into any of the other 499 partitions !!

Each and every insert into our partitioned table (partitioned by column A) therefore would require Oracle to check all (say)500 index partitions each and every time to check for duplicates of column B. Again, it’s important to understand that any given value of column B could potentially be in any of the 500 partitions, IF Oracle allowed us to create a Local Partitioned Index just on column B.

Checking all 500 index partitions looking for a specific value of column B would obviously be impractical, inefficient and totally un-scalable. Therefore Oracle doesn’t allow us to do this. It doesn’t allow us to create a Local index in which the indexed columns does’t include the partitioning columns as well.

This is actually a good thing.

If you want to create a Unique index in a partitioned table, you MUST either add all the partitioned columns and make it part of the LOCAL unique index (so that way each and every insert would only have to check the one local partition as this value is known now it’s part of the index) or you must create it as a GLOBAL index (in which again, Oracle only has to check the one index structure).

It actually makes a lot of sense to do this.

Moving onto the second part of the question. Let’s just use a Local Non-Unique index to police our PK constraints then.

Fortunately this isn’t allowed either for exactly the same reasons. You can’t create a Local Non-unique index to police a PK (or Unique) constraint if the Constraint does not also include the partitioned columns. Otherwise again, Oracle would need to check each and every index partition to determine whether the constraint has been violated or not.

If you attempt to use an existing Local Non-Unique index to police a PK or Unique constraint that does not contain the partitioned columns, you will get an error saying it can’t create the (by default Global index) because the useless Local Non-Unique index (from a policing the constraint point of view) already exists.

Again if you want to create a Non-Unique index to police a PK or Unique constraint you must either ensure the constraint includes all the partitioned columns in which case it can be Local or you must use a Global Non-Unique index.

In other words, the rules apply equally to both Unique and Non-Unique indexes.

So it’s not really a case of Oracle not allowing one to create a Local Unique index without including the partitioned columns (although that’s of course true) but really a case of Oracle not allowing a PK or Unique *constraint*  to be policed via *any* Local index (whether Unique or Non-Unique), unless the partitioned columns are also included.

Little demo to illustrate: Local Index Issue With Partitioned PK and Unique Key Constraints

Follow

Get every new post delivered to your Inbox.

Join 1,852 other followers