jump to navigation

12c Index Like Table Statistics Collection (Wearing The Inside Out) July 9, 2014

Posted by Richard Foote in 12c, Automatic Table Statistics, Index statistics, Oracle Indexes.
add a comment

This change introduced in 12c has caught me out on a number of occasions.

If you were to create a new table:

SQL> create table thin_white_duke1 (id number, code number, name varchar2(30));

Table created.

And then populate it with a conventional insert:

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

1000000 rows created.

SQL> commit;

Commit complete.

We find there are no statistics associated with the table until we explicitly collect them:

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed from dba_tables where table_name='THIN_WHITE_DUKE1';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE1

But if we were to now create an index on this table:

SQL> create index thin_white_duke1_code_i on thin_white_duke1(code);

Index created.

We find that we now do indeed have index statistics collected by default (since 9i days anyways):

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE1_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE1_CODE_I    1000000          2        1936            10

OK, if we now create another table and index:

SQL> create table thin_white_duke2 (id number, code number, name varchar2(30));

Table created.

SQL> create index thin_white_duke2_code_i on thin_white_duke2(code);

Index created.

And populate it with a bulk load parallel, append insert:

SQL> insert /*+ append */ into thin_white_duke2 select rownum, mod(rownum,10), 'DAVID BOWIE' from dual connect by level <=1000000;

1000000 rows created.

 SQL> commit;

Commit complete.

If we now look at the table statistics:

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
from dba_tables where table_name='THIN_WHITE_DUKE2';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE2    1000000       3511          0          20 09-JUL-14

SQL> select column_name, num_distinct, density, num_buckets from dba_tab_columns
 where table_name='THIN_WHITE_DUKE2';

COLUMN_NAME NUM_DISTINCT    DENSITY NUM_BUCKETS
----------- ------------ ---------- -----------
NAME                   1          1           1
CODE                  10         .1           1
ID               1000000    .000001           1

SQL> select column_name, num_distinct, density, histogram, notes
from dba_tab_col_statistics where table_name='THIN_WHITE_DUKE2';

COLUMN_NAME NUM_DISTINCT    DENSITY HISTOGRAM  NOTES
----------- ------------ ---------- ---------- -------------
NAME                   1          1 NONE       STATS_ON_LOAD
CODE                  10         .1 NONE       STATS_ON_LOAD
ID               1000000    .000001 NONE       STATS_ON_LOAD

 

We notice that in 12c, they’re automatically populated and accurate. The NOTES column in dba_tab_col_statistics highlights that the statistics were collected via STATS_ON_LOAD.

If however we look at the current state of the index statistics:

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys from dba_indexes where index_name='THIN_WHITE_DUKE2_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE2_CODE_I          0          0           0             0

We noticed they haven’t changed since the index was initially created. So by populating an empty table with an append insert, table statistics are now collected on the fly, but not the statistics on any existing indexes.

If we want to now collect just the index statistics and potentially any useful histograms on the table (as histograms are not collect during the bulk load operation), we can use the new GATHER_AUTO option with the dbms_stats.gather_table_stats procedure without having to re-gather base table statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=>user,
tabname=>'THIN_WHITE_DUKE2', options=>'GATHER AUTO');

PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, blevel, leaf_blocks, distinct_keys
from dba_indexes where index_name='THIN_WHITE_DUKE2_CODE_I';

INDEX_NAME                NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
----------------------- ---------- ---------- ----------- -------------
THIN_WHITE_DUKE2_CODE_I    1000000          2        1739            10

 The same automatic table statistics gathering occurs when we create a table via a sub-select clause:

SQL> create table thin_white_duke3
as select rownum id, mod(rownum,1000) code, 'DAVID BOWIE' name
from dual connect by level <= 1000000;

Table created.

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
from dba_tables where table_name='THIN_WHITE_DUKE3';

TABLE_NAME         NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
---------------- ---------- ---------- ---------- ----------- ---------
THIN_WHITE_DUKE3    1000000       3787          0          22 09-JUL-14

Of course, sometimes when creating test tables as I regularly do, one sometimes forgets these statistics are now automatically collected !!

Indexing Foreign Key Constraints With Invisible Indexes (Invisible People) April 22, 2014

Posted by Richard Foote in 12c, Block Dumps, Foreign Keys, Invisible Indexes, Oracle Indexes.
1 comment so far

In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks.

Last year, I discussed how it was possible in Oracle Database 12c to have multiple indexes on the same column list.

Quite some time ago, I discussed how so-called Invisible Indexes can indeed still be visible in various scenarios, including when policing FK constraints.

Well, lets put all these three topics together :)

First, let use the same basic setup as the last post:

SQL> create table bowie_dad (id number, dad_name varchar2(30)); 

Table created.

SQL> insert into bowie_dad values (1, 'DAVID BOWIE'); 

1 row created.

SQL> insert into bowie_dad values (2, 'ZIGGY STARDUST'); 

1 row created.

SQL> insert into bowie_dad values (3, 'MAJOR TOM');

1 row created.

SQL> insert into bowie_dad values (4, 'THIN WHITE DUKE');

1 row created.

SQL> commit;

Commit complete.

SQL> create table bowie_kid (id number, kid_name varchar2(30), dad_id number);

Table created.

SQL> insert into bowie_kid select rownum, 'ALADDIN SANE', mod(rownum,3)+2 from dual connect by level <=1000000; 

1000000 rows created.

SQL> commit;

Commit complete.

SQL> alter table bowie_dad add primary key(id);

Table altered.

SQL> alter table bowie_kid add constraint bowie_kid_fk foreign key(dad_id) references bowie_dad(id);

Table altered.

 

We’re now going to create two indexes concurrently on the FK constraint on the DAD_ID column, a Bitmap Index and an invisible B-Tree Index as is now possible since Oracle Database 12c:

SQL> create bitmap index bowie_kid_fk_i on bowie_kid(dad_id); 

Index created.

SQL> create index bowie_kid_fk2_i on bowie_kid(dad_id) invisible;

Index created.

Oracle Database 12c allows us to now create multiple indexes on the same column list, providing only one index is visible at a time.

Let’s look at a partial block dump of the first leaf block of each index. First the Bitmap Index:

Block header dump:  0x0180805c
 Object id on Block? Y
 seg/obj: 0x16f45  csc: 0×00.36bc54  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0×1808058 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.0036bc54
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[4499] flag: ——-, lock: 0, len=3537
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 00
col 2; len 6; (6):  01 80 80 2c 00 3f
col 3; len 3516; (3516):
 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
 49 92 24 49 cb 92 24 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24
 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49
 92 24 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49

 

Note the indexed value is c1 03, denoting the lowest DAD_ID=2 currently in the table.

Now the  partial block dump of the invisible B-Tree Index:

 

Block header dump:  0x0181b724
 Object id on Block? Y
 seg/obj: 0x16f46  csc: 0×00.36bc78  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x181b720 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.0036bc78
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0×426
kdxcofeo 1880=0×758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 01
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 04
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 07

Again as expected the first index entry is C1 03.

With only a visible Bitmap Index in place, does that mean we’ll have table locking issues if we delete a parent row with current transactions in place ? Let’s check it out.

In one session, we have a current transaction on the child table:

SQL> insert into bowie_kid values (1000001, 'LOW', 4); 

1 row created.

 

In another session, we attempt to delete a parent row (with an ID = 1 which doesn’t currently exist with the child table):

SQL> delete bowie_dad where id = 1; 

1 row deleted.

We note the DML was successful and didn’t hang. This means the B-Tree index is clearly being used to police this constraint, even though it’s currently invisible.

In a third session, we now attempt to insert a child row using a FK value that’s in the process of being deleted:

SQL> insert into bowie_kid values (1000003, 'HEROES', 1);

 

As expected, it hangs as it’s currently effectively waiting on the row level lock made possible by the index entry in the B-Tree index as invisible indexes are still maintained behind the scenes. If we look at a fresh block dump of both indexes, beginning with the Bitmap Index:

Block header dump:  0x0180805c
 Object id on Block? Y
 seg/obj: 0x16f45  csc: 0×00.36bc54  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0×1808058 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.0036bc54
Leaf block dump
===============
header address 402948708=0×18048264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[4499] flag: ——-, lock: 0, len=3537
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 00
col 2; len 6; (6):  01 80 80 2c 00 3f
col 3; len 3516; (3516):
 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
 49 92 24 49 cb 92 24 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24
 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49
 92 24 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49

 

We note the Bitmap Index has not been updated. It still lists the C1 03 value as the minimum indexed value.

However, if we look at the invisible B-Tree index:

Block header dump:  0x0181b724
 Object id on Block? Y
 seg/obj: 0x16f46  csc: 0×00.36bc78  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x181b720 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0×0008.015.00000b86  0x014316ab.01c5.42  —-    1  fsc 0×0000.00000000
Leaf block dump
===============
header address 402948708=0×18048264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 514
kdxcofbo 1064=0×428
kdxcofeo 1868=0x74c
kdxcoavs 804
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[1868] flag: ——-, lock: 2, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 81 b6 f3 00 00
row#1[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 01
row#2[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 7f d3 00 04
row#3[8000] flag: ——-, lock: 0, len=12

It has been updated and lists a new index entry C1 02 as the minimum value now in the index.

So the B-Tree index can be used to successfully police the FK index and prevent the possible table level locking issues associated with deleting parent rows, even though it’s invisible and there is an equivalent visible Bitmap index in place. Invisible indexes are simply not considered as viable execution paths by the Cost Based Optimizer, but may still be “visible” in a variety of scenarios such as quietly policing constraints behind the scenes.

Do I recommend creating two such indexes in Oracle Database 12c. Well, no as the costs of maintaining both indexes need to be considered. But I certainly do caution simply making indexes invisible and expecting the database to behave in exactly the same manner if the index were to be subsequently dropped.

Because rolling back all the above and then dropping the invisible index:

SQL> drop index bowie_kid_fk2_i;

Index dropped.

SQL> insert into bowie_kid values (1000001, 'LOW', 4);

1 row created.

 

Means in another session the parent delete operation will now hang without the B-Tree index being in place:

SQL> delete bowie_dad where id = 1;

 

 

 

 

 

 

Modify Primary Key Using Index (Learning To Fly) February 27, 2014

Posted by Richard Foote in 12c, Modify Primary Key Using Index, Oracle Indexes, Primary Key, Richard's Musings.
add a comment

One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of these days, I might blog about a presentation I put together a while ago on the common habits of highly successful DBAs (or technologists in general). One of these key habits I believe is the constant need to keep learning and to keep growing professionally.

One of the places I constantly turn to in order to learn something new is Jonathan Lewis’s Oracle Scratchpad blog. I doubt many folks who read my blog don’t already know what a fantastic source of information this is. Reading a recent posting of his on Modifying Primary Keys was one such moment where I went “wow, I didn’t know you could do that” !!

I previously blogged about the new 12c capability of having Multiple Indexes On The Same Column List and demonstrated how it was now possible to “quickly” swap the index on say a Primary Key constraint by pre-building a new index (say a Unique index to replace an existing Non-Unique index), then drop/disable the constraint and old index, make the new index visible and re-applying the PK constraint.

Well, as Jonathan described, there’s an easier alternative that doesn’t require so much stuffing around with the PK constraint.

I’m just going to setup the same demo as I used in the initial multiple indexes discussion where I have a Non-Unique index policing the PK constraint of a table:

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.

I then subsequently decide to replace the existing Non-Unique index policing the PK constraint with say a Unique Index instead. (Note the following also applies for Unique constraints as well). With 12c, I can pre-create another index with a different characteristic on the same column, I just have to initially make it Invisible:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible online;  

Index created.

Now comes the interesting bit (for me anyways). I can simply modify the PK or Unique Key constraint to use the new index with the USING INDEX clause:

SQL> alter table ziggy modify constraint ziggy_pk using index ziggy_id_i2;

Table altered.

And then switch the visibility of the two indexes:

SQL> alter index ziggy_id_i1 invisible;

Index altered.

SQL> alter index ziggy_id_i2 visible;

Index altered.

SQL> drop index ziggy_id_i1 online;

Index dropped.

Thereby changing the index policing the PK constraint without having to drop/disable the PK constraint in the process. I do have both indexes invisible for a brief period of time, so still exercise some caution, although the PK business rule is safe at all times without having to lock the table.

Obviously, if you wish to switch the index with one that uses a different column list (for example, if you wish to remove an unnecessary column from the policing index), then the indexes can simply be switched without having to alter their visibility attributes.

As Jonathan pointed out in his post, this capability dates back to later versions of 9i.

I wonder what I’ll learn tomorrow …

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation) February 17, 2014

Posted by Richard Foote in 12c, Drop Index, Invisible Indexes, Online DDL, Oracle Indexes, Unusable Indexes.
1 comment so far

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX INVISIBLE/VISIBLE ONLINE

ALTER INDEX UNUSABLE ONLINE

So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

SQL> create table radiohead (id number, code number, name varchar2(30));

Table created.

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

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_code_i on radiohead(code);

Index created.

If we now insert a new row in one session but not commit:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

And then attempt any of the following DDL commands in another session:

SQL> drop index radiohead_code_i;

drop index radiohead_code_i
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i invisible;

alter index radiohead_code_i invisible
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i unusable;

alter index radiohead_code_i unusable
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

They all get the well-known “ORA-00054: resource busy” error.

If on the other hand, one of these DDL statements is already running in a session:

SQL> alter index radiohead_code_i unusable;

All DML statements in other sessions will hang until the DDL completes:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

Once the index is finally made unusable:

SQL> alter index radiohead_code_i unusable;

Index altered.

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

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_CODE_I               UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

no rows selected

We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.

So these commands prior to the Oracle 12c Database previously had locking related issues.

If we now perform the same setup in 12c and again have an outstanding transaction in a session:

SQL> drop index radiohead_code_i online;

The Drop Index command doesn’t now get the Ora-00054: resource busy, but rather hangs until all prior transactions complete.

However, while the Drop Index command hangs, it doesn’t in turn lock out transactions within other sessions. In another session:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

1 row created.

And in yet other session:

SQL> delete radiohead where id = 42;

1 row deleted.

SQL> commit;

Commit complete.

These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.

SQL> drop index radiohead_code_i online;

Index dropped.

Another more subtle difference in behaviour with 12c. If there’s an existing transaction when you decide to make an index unusable:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.
SQL> alter index radiohead_code_i unusable online;

As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:

SQL> commit;

Commit complete.1

1SQL> alter index radiohead_code_i unusable online;

Index altered.

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

INDEX_NAME                STATUS
------------------------- --------
RADIOHEAD_CODE_I          UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

SEGMENT_NAME         BLOCKS    EXTENTS
---------------- ---------- ----------
RADIOHEAD_CODE_I       2176         32

We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.

12c Online Partitioned Table Reorganisation Part II (Move On) January 15, 2014

Posted by Richard Foote in 12c, Move Partitions, Oracle Indexes, Update Indexes Online.
14 comments

In Part I, we looked at some of the issues associated with locking and unusable indexes when Moving both tables and table partitions.

The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.

If we look at the same demo as with the previous post:

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.

If in one session we have an active transaction (i.e. not yet committed):

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

1 row created.

while we move a table partition in another session as we did previously:

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 resource busy error.

However, if we now use the new 12c UPDATE INDEXES ONLINE clause:

SQL> alter table muse move partition p3 tablespace users update indexes online;

The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.

Once we commit in session one:

SQL> commit;

Commit complete.

The Move Partition operation can obtain the necessary table partition lock and complete successfully:

SQL> alter table muse move partition p3 tablespace users update indexes online;

Table altered.

The indexes remain in a USABLE state throughout:

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                   USABLE

If we perform these operations the other way around with the Move Partition first:

SQL> alter table muse move partition p3 tablespace users update indexes online;

While in session two we now insert a new row into the partition being moved:

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

1 row created.

The insert operation is now not locked out by the Move Partition operation and can proceed. The Move Partition operation in turn requires a brief lock at the end of the process (in much the same way as a normal online index rebuild) and so will hang if there are any outstanding transactions on the partition being moved at that time. And again like an online index rebuild, this will not in turn lock out other DML transactions.

Once the commit is performed in session two:

SQL> commit;

Commit complete.

The Move Partition can complete:

SQL> alter table muse4 move partition p3 tablespace users update indexes online;

Table altered.

And importantly, all the associated indexes again remain in a USABLE state:

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

INDEX_NAME      PARTITION_NAME       STATUS
--------------- -------------------- --------
MUSE_CODE_I     P1                   USABLE
MUSE_CODE_I     P2                   USABLE
MUSE_CODE_I     P3                   USABLE
MUSE_ID_PK                           VALID

Now this is only applicable to the online move of table partitions. Moving a non-partitioned table still has the same issues and restrictions as before (as discussed in Part I).

Therefore for those with the Partitioning option, serious consideration could be given to converting tables to partitioned tables, even if the table only has the one partition:

SQL> create table muse3 (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (maxvalue));

Table created.

Such a table is effectively the equivalent of a non-partitioned table, but can now take advantage of the flexibility that the new online move partition capability provides :)

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 Invisible Columns (The Invisible Man) November 19, 2013

Posted by Richard Foote in 12c, Invisible Columns, Oracle Indexes.
2 comments

Oracle 12c introduced the ability to make user specified columns “Invisible” such that they’re invisible by default and have to be explicitly referenced to be accessed. So a simple SELECT * won’t display columns defined as invisible, but they will be displayed if explicitly referenced in the SELECT column list.

From an indexing perspective, columns can still be indexed and considered by the cost based optimizer regardless of whether the column is invisible or not. So don’t be confused by an invisible index with an index on an invisible column, they’re two entirely different concepts.

Unless specified otherwise, a column is visible by default. So the following BOWIE table is defined with three visible columns:

SQL> create table bowie (id number, code number, name varchar2(30));

Table created.

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

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

Let’s now create an index on the CODE column:

SQL> create index bowie_code_i on bowie(code);

Index created.

Each column in the table is defined with a unique COLUMN_ID, which determines the logical ordering of the column within the table:

SQL> desc bowie

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
CODE                                               NUMBER
NAME                                               VARCHAR2(30)

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         1 ID
         2 CODE
         3 NAME

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         1                 1                  1 ID              NO  NO
         2                 2                  2 CODE            NO  NO
         3                 3                  3 NAME            NO  NO

If we now run a simple query on BOWIE, we notice the index is used by the CBO and the columns are listed in the order of COLUMN_ID:

SQL> select * from bowie where code=42;

ID         CODE       NAME
---------- ---------- ------------------------------
     90042         42 DAVID BOWIE
     70042         42 DAVID BOWIE
     80042         42 DAVID BOWIE
        42         42 DAVID BOWIE
     10042         42 DAVID BOWIE
     20042         42 DAVID BOWIE
     30042         42 DAVID BOWIE
     60042         42 DAVID BOWIE
     50042         42 DAVID BOWIE
     40042         42 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Let’s now make the CODE column INVISIBLE:

SQL> alter table bowie modify code invisible;

Table altered.

We notice the CODE column no longer has a COLUMN_ID defined and that all columns that follow the CODE column (in this case just the NAME column) have the COLUMN_ID value decremented by 1:

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         2 NAME
           CODE
         1 ID

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         1                 1                  1 ID              NO  NO
                           2                  2 CODE            YES NO
         2                 3                  3 NAME            NO  NO

If we now re-run the same SELECT * query:

SQL> select * from bowie where code=42;

ID         NAME
---------- ------------------------------
     20042 DAVID BOWIE
     90042 DAVID BOWIE
     70042 DAVID BOWIE
     80042 DAVID BOWIE
     30042 DAVID BOWIE
     60042 DAVID BOWIE
     50042 DAVID BOWIE
     40042 DAVID BOWIE
        42 DAVID BOWIE
     10042 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

We notice the CODE column is no longer displayed even though it’s still explicitly reference in the WHERE condition and that the index on the now invisible CODE column is still used by the CBO.

To display the CODE column, we need to set colinvisible on in sql*plus, explicitly reference it in the column list, explicitly reference it when inserting a row with a CODE value, etc. etc. :

SQL> set colinvisible on

SQL> desc bowie

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)
CODE (INVISIBLE)                          NOT NULL NUMBER

SQL> select id, code, name from bowie where code = 42;

ID         CODE       NAME
---------- ---------- ------------------------------
     20042         42 DAVID BOWIE
     90042         42 DAVID BOWIE
     70042         42 DAVID BOWIE
     10042         42 DAVID BOWIE
     80042         42 DAVID BOWIE
     30042         42 DAVID BOWIE
     60042         42 DAVID BOWIE
     50042         42 DAVID BOWIE
     40042         42 DAVID BOWIE
       42          42 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

SQL> insert into bowie (id,code,name) values (100001, 42, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

So a column is only “invisible” by default, it can still be referenced and accessed by those who have knowledge of its existence.

If we now make the column VISIBLE again:

SQL> alter table bowie modify code visible;

Table altered.

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         2 NAME
         3 CODE
         1 ID

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         2                 3                  3 NAME            NO  NO
         3                 2                  2 CODE            NO  NO
         1                 1                  1 ID              NO  NO

We notice the CODE column has been assigned a COLUMN_ID again but not the same one as it was previously. Basically, when a column is made visible, it’s assigned the next highest available COLUMN_ID, in this case the value 3.

Note the original, internal physical column order is still maintained via INTERNAL_COLUMN_ID (e.g. a value of 2 for the CODE column) as Oracle of course still needs a way to determine which column physically belongs where within the table.

So by making the previously non-last column invisible and then visible again, we have effectively changed the logical order of the column within the table:

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)
CODE                                               NUMBER

SQL> select * from bowie where code = 42;

ID         NAME                                 CODE
---------- ------------------------------ ----------
     90042 DAVID BOWIE                            42
    100001 ZIGGY STARDUST                         42
     70042 DAVID BOWIE                            42
     80042 DAVID BOWIE                            42
        42 DAVID BOWIE                            42
     10042 DAVID BOWIE                            42
     20042 DAVID BOWIE                            42
     30042 DAVID BOWIE                            42
     60042 DAVID BOWIE                            42
     50042 DAVID BOWIE                            42
     40042 DAVID BOWIE                            42

11 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

We notice the CODE column is now displayed last by default.

They are some limitations to invisible columns, in that they can’t be used for external, cluster or index organized tables.

Making columns invisible is a neat way to now hide the existence columns within a table. It’s also a neat way to now easily change the default ordering of columns within a table …

12c Indexing Extended Data Types Part II (15 Steps) November 14, 2013

Posted by Richard Foote in 12c, Extended Data Types, Function Based Indexes, Oracle Indexes.
1 comment so far

Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !!

As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the maximum length of an index entry.

A function-based index on the hash of the column value as previously demonstrated can be used for equality based predicates but not for ranged based requirements.

If index accesses are required for ranged based predicates, then a simple sub-string function-based index can be considered. Using the same set-up and demo as in Part I, let’s create a function-based index that stores the first (say) 1000 characters of an extended data type column. This should provide more than enough detail of the column contents to be sufficiently selective in most practical scenarios.

SQL> create index bowie_substr_text_i on bowie(substr(text,1,1000));

Index created.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_SUBSTR_TEXT_I';

INDEX_NAME             NUM_ROWS LEAF_BLOCKS
-------------------- ---------- -----------
BOWIE_SUBSTR_TEXT_I      100000         306

Such a substr function-based index is viable not only with equality based predicates:

SQL> select * from bowie where text = '42BOWIE';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    16 |    80   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     1 |    16 |    80   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   400 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access(SUBSTR("TEXT",1,1000)='42BOWIE')

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

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

But unlike the hash function-based index in my previous post, it can also be considered in a range (pun fully intended) of ranged-based predicates as well, for example:

SQL> select * from bowie where text between '4299BOWIE' and '42BOWIE';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     2 |    32 |    92   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     2 |    32 |    92   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   450 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"<='42BOWIE' AND "TEXT">='4299BOWIE')
2 - access(SUBSTR("TEXT",1,1000)>='4299BOWIE' AND SUBSTR("TEXT",1,1000)<='42BOWIE')

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

0  recursive calls
0  db block gets
6  consistent gets
0  physical reads
0  redo size
693  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
3  rows processed
SQL> select * from bowie where text > 'C';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   900 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">'C')
2 - access(SUBSTR("TEXT",1,1000)>='C')

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

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

12c Indexing Extended Data Types Part I (A Big Hurt) September 12, 2013

Posted by Richard Foote in 12c, Extended Data Types, Function Based Indexes, Oracle Indexes, Unique Indexes.
10 comments

The maximum size for VARCHAR2, NVARCHAR and RAW columns has been extended to 32767 bytes with the Oracle 12c Database. However, indexing such columns with standard indexes comes with some challenges.

These extended data types are not enabled by default within the database but can easily be done so by following these steps:

  1. Restart the database in UPGRADE mode
  2. Change the setting of MAX_STRING_SIZE to EXTENDED
  3. Run the rdbms/admin/utl32k.sql script as sysdba
  4. Restart the database

We can now create a table with a larger than 4000 byte VARCHAR2 column (Note such larger column values are actually stored out of line from the rest of the table, I might discuss this in another post) :

SQL> create table bowie (id number, text varchar2(32000));
Table created.

However, if we try now to create an index on such a column:

SQL> create index bowie_text_i on bowie(text);
create index bowie_text_i on bowie(text)
 *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

We find Oracle complains that the possible index length is going to be too large for my (8K) block sized index. So, is it possible to index such extended columns ?

Let’s populate this table with some data:

SQL> insert into bowie (id, text) values (1, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.

SQL> commit;

Commit complete.

SQL> select length(text) from bowie;

LENGTH(TEXT)
------------
        1110

SQL> insert into bowie (id, text)
     select 2, text||text||text||text||text||text||text||text||text||text
     from bowie;

1 row created.

SQL> commit;

Commit complete.

SQL> select length(text) from bowie;

LENGTH(TEXT)
------------
        1110
       11100

SQL> insert into bowie (id, text)
     select rownum+2, to_char(rownum)||'BOWIE'
     from dual connect by level<=99998;

99998 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

So yes, we definitely have at least one very large Text value (some 11100 bytes) in our table. How cool. One method of creating a valid index on this extended column is to use a function-based index based on a hash value of this column. For example:

SQL> create index bowie_hash_text_i on bowie(standard_hash(text));

Index created.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_HASH_TEXT_I';

INDEX_NAME             NUM_ROWS LEAF_BLOCKS
-------------------- ---------- -----------
BOWIE_HASH_TEXT_I        100000         447

This index can now be used effectively for subsequent equality based predicates, for example:

SQL> select * from bowie where text = '42BOWIE';

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

Plan hash value: 1900956348
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    16|   203   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE             |     1 |    16|   203   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_HASH_TEXT_I |   400 |      |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access(STANDARD_HASH("TEXT")=HEXTORAW('A2C98939EDB479BC3EB0CDC560DDCD1575D47F62'))

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
610  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

1  rows processed

So the index has been used to very efficiently retrieve data based on an equality predicate on the extended TEXT column.

However, range based predicates are problematic as Oracle has no easy way to find and retrieve all such data via the index when the data in the index is effectively randomised hashed values. For example:

SQL> select * from bowie where text like 'aaaaaaaaaaaaaaaaaaaaaa%';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    16 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     1 |    16 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select * from bowie where text between '4299BOWIE' and '42BOWIE';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |    32 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     2 |    32 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select * from bowie where text > 'zzz';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    17 |   219   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     1 |    17 |   219   (2)| 00:00:01 |
---------------------------------------------------------------------------

The above are all examples of predicates that can’t use our hash based function-based index, even though the CBO is estimating very few rows to be returned.

If we try now to make this extended column unique via a constraint:

SQL> alter table bowie add constraint bowie_text_unq unique (text);
alter table bowie add constraint bowie_text_unq unique (text)
*
ERROR at line 1:

ORA-01450: maximum key length (6398) exceeded

We hit our problem again. Oracle tries to make a unique index on the Text column, but it can’t because the extended column definition could potentially exceed the maximum allowable key length.

We can get around this in a similar fashion, but by adding a virtual hash column to the table and basing the Unique constraint on this column instead:

SQL> drop index bowie_hash_text_i;

Index dropped.

SQL> alter table bowie add (text_hash as (standard_hash(text)));

Table altered.

SQL> alter table bowie add constraint bowie_text_unq unique (text_hash);

Table altered.

This can now be used to effectively protect the uniqueness of the original Text column:

SQL> insert into bowie (id, text) values (1000001, '42BOWIE');
insert into bowie (id, text) values (1000001, '42BOWIE')
*
ERROR at line 1:

ORA-00001: unique constraint (BOWIE.BOWIE_TEXT_UNQ) violated

This index can now be used in a similar manner as before for equality based predicates:

SQL> select * from bowie where text = '42BOWIE';

ID  TEXT       TEXT_HASH
--- ---------- ----------------------------------------
44     42BOWIE A2C98939EDB479BC3EB0CDC560DDCD1575D47F62

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

Plan hash value: 2691947611
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    16 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| BOWIE          |     1 |    16 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | BOWIE_TEXT_UNQ |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access("BOWIE"."TEXT_HASH"=HEXTORAW('A2C98939EDB479BC3EB0CDC560DDCD1575D47F62'))

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

But with the same restrictions with range based predicates:

SQL> select * from bowie where text between '429BOWIE' and '42BOWIE';

ID  TEXT       TEXT_HASH
--- ---------- ----------------------------------------
44     42BOWIE A2C98939EDB479BC3EB0CDC560DDCD1575D47F62
431   429BOWIE A7E2B59E1429DB4964225E7A98A19998BC3D2AFD

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

Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |    32 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     2 |    32 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"<='42BOWIE' AND "TEXT">='429BOWIE')

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
758  consistent gets
0  physical reads
0  redo size
787  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed

I’ll look at other indexing options with these new extended columns in Part II.

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: 0×00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.0029239b
Leaf block dump
===============
header address 360728164=0×15804664
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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: 0×00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0004.012.0000079d 0x014045c7.0122.44 –U- 10 fsc 0×0000.00292503
Leaf block dump
===============
header address 360612452=0x157e8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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: 0×01028750
Object id on Block? Y
seg/obj: 0x130ac csc: 0×00.3c7323 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0×1028748 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0006.001.00000f91 0x00c03e16.0177.02 —- 378 fsc 0x1c0b.00000000
Leaf block dump
===============
header address 130573412=0x7c86464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0×318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 378
kdxlenxt 16942929=0×1028751
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: 0×00.26ae40 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x18000d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.0026ae40
Leaf block dump
===============
header address 364741220=0x15bd8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0×318
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 :)

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

Details and Registration

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

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers