jump to navigation

Indexing and Transparent Data Encryption Part II (Hide Away) May 20, 2015

Posted by Richard Foote in Block Dumps, Oracle Indexes, TDE.
add a comment

In Part I, I quickly ran through how to setup an encrypted tablespace using Transparent Data Encryption and to take care creating indexes outside of these tablespaces.

Another method of encrypting data in the Oracle database is to just encrypt selected columns. Although the advantage here is that we can just encrypt sensitive columns of interest (and that the data remains encrypted within the buffer cache), this method has a number of major restrictions, especially in relation to indexing.

To first set the scene, I’ll start by creating and populating an unencrypted table:

SQL> create table bowie (id number, code number, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

I’ll now create an index on the (effectively unique) ID column:

SQL> create index bowie_id_i on bowie(id);

Index created.

We can effectively use this index on some basic range based predicates:

SQL> select * from bowie where id between 42 and 43;

ID       CODE     SALARY TEXT
---------- ---------- ---------- ------------------------------
42         42         42 BOWIE
43         42         43 BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     2 |   112 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |     2 |   112 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL> select * from bowie where id > 10000000000;

no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes |  Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    56 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |     1 |    56 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

So all is well with our index here and is used as expected.

If we perform a block dump and have a look at the first table block containing rows (following are excerpts from the block dump):

Block header dump:  0x020001dc
Object id on Block? Y
seg/obj: 0x16cda  csc: 0x00.2b3abc  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x20001d8 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.006.000005dd  0x014004e3.00bb.17  –U-  330  fsc 0x0000.002b3aca
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x020001dc
data_block_dump,data header at 0x1c72e64
===============
tsiz: 0x1f98
hsiz: 0x2a6
pbl: 0x01c72e64
76543210
flag=——–
ntab=1
nrow=330
frre=-1
fsbo=0x2a6
fseo=0x5d6
avsp=0x330
tosp=0x330
0xe:pti[0] nrow=330 offs=0

We notice the block contains 330 rows. Note this number as we’ll check back on it later.

If we look further on in the dump to the section listing some of the rows within the block:

block_row_dump:
tab 0, row 0, @0x181a
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 47
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 47
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x182e
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 48
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 48
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1842
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 49
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 49
col  3: [ 5]  42 4f 57 49 45
tab 0, row 3, @0x1856

…..

We can see the 4 columns of each row and note that the length of the ID and CODE columns are 3 and 2 bytes respectively. We can also see that the hex values of the CODE column (col 1) are all the same: c1 2b (as they all have a value of 42).

OK, time to encrypt some columns. I’ll re-create the table but this time encrypt both the ID and CODE columns using all the default settings:

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number encrypt, code number encrypt, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

ID                                                 NUMBER ENCRYPT
CODE                                               NUMBER ENCRYPT
SALARY                                             NUMBER
TEXT                                               VARCHAR2(30)

 

If we look at a dump of this table we notice a number of key differences:

 

Block header dump:  0x02000464
Object id on Block? Y
seg/obj: 0x16cef  csc: 0x00.2e6e1f  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2000460 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.001.00000671  0x01409e62.00ec.19  –U-   60  fsc 0x0000.002e6e7b
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x02000464
data_block_dump,data header at 0x1d82e64
===============
tsiz: 0x1f98
hsiz: 0x8a
pbl: 0x01d82e64
76543210
flag=——–
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x3dc
avsp=0x352
tosp=0x352
0xe:pti[0] nrow=60 offs=0

….

 

The first difference is that where previously we had 330 rows, the block now only contains 60 rows of data.

If we look further on to the rows themselves:

tab 0, row 0, @0x1b72
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
bd 33 16 9a aa 6a 68 cf f7 a2 99 6c 3d b0 0c 1f 11 3d 42 cb 76 20 52 cc 18
8a 96 fd 0e 59 41 f2 a0 0f da 96 37 40 9b 28 dd 93 82 29 74 32 f1 53 c5 27
e8 e1
col  1: [52]
57 0c 63 c8 4f a9 42 f7 61 c3 63 b2 1b 0e f9 bc 74 5b 74 46 87 08 26 23 6b
c9 ae 52 ca 0e 31 9e ac 54 79 f7 2d f9 64 41 30 e5 6e 11 00 a3 55 d4 81 42
a4 19
col  2: [ 2]  c1 3e
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x1be8
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
c3 13 d5 f7 a8 1f 46 8b bd 2b a9 e0 25 8e b3 15 2d 52 77 f5 fa 8a 52 46 f6
1d 0d 0a 58 8f 68 51 ea 2f 8c 0e 56 9d 2b cf 6f 86 7c d4 a9 d9 2a 25 fd a1
6e 7d
col  1: [52]
49 19 8f 40 da 0e ad fb 7a 79 6d 50 61 c9 9e 33 90 3f 73 c9 64 0a f0 98 c6
2f 8c c1 3f 47 fd 78 55 be 5c e5 df 1f 94 ab c0 3b a2 e3 37 65 f4 8e 80 c0
f4 66
col  2: [ 2]  c1 3f
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1c5e
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
1e 9d 90 85 70 e8 50 39 c9 64 9f 85 6f c6 e6 03 44 8c 59 73 9a 0e a6 cb 3c
ff 41 91 42 2a 36 f1 98 b7 54 61 24 1a 62 87 3f b9 21 5e d7 43 f6 39 14 7a
d0 34
col  1: [52]
e5 a0 4a 82 06 12 88 08 fb df db b7 00 19 60 cc d1 da 98 34 1d 24 44 64 79
04 48 29 7a 2c 5d 26 06 0d f3 5a 42 1c 34 59 65 14 85 53 e7 07 ac ee 11 73
82 5f
col  2: [ 2]  c1 40
col  3: [ 5]  42 4f 57 49 45

….

We see the reason why we have fewer rows per block is that the encrypted columns have significantly increased in size. Where previously they were just 3 and 2 bytes respectively, both the ID and CODE columns are now 52 bytes in length. The actual size would in part depend on the encryption algorithm used (some algorithms round to the next 8 bytes), in this example I used the default AES192.

With AES192, the length of the column is rounded to the next 16 bytes. However, if we simply encrypt a columns as is, it would mean a column value would be encrypted to the same value when using the same encryption key. This means a malicious person could potentially attempt to reverse engineer a value by inserting known column values and seeing if the generated encrypted values are the same as those in the table. To prevent this, Oracle by default adds “Salt”, which is basically a random string, to the column value being encrypted to make it now impossible to reverse engineer the inserted value. This adds another 16 bytes to the length of the column value. If we look at the second CODE column (col 1) in the block dump, we notice they all have different encrypted values even though they all have the same actual value of 42 within the table.

So that’s 32 bytes accounted for. The remaining 20 bytes is a result of TDE adding a Message Authentication Code (MAC) to each encrypted value for integrity checking purposes.

Clearly, having columns that increase so significantly due to encryption will also have an impact on any associated indexes as they will likewise not be able to contain as many entries per index block and hence be significantly larger.

However, the more pressing issue is that by adding salt to the encryption process, there is no easy deterministic way Oracle can associate an actual indexed value with the encrypted value when accessing and modifying the index. As a result, Oracle simply doesn’t allow an index to be created on any column that has been encrypted with salt.

SQL> create index bowie_id_i on bowie(id);
create index bowie_id_i on bowie(id)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

SQL> alter table bowie add primary key(id);
alter table bowie add primary key(id)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

If we want to encrypt a column and have the column indexed, we must encrypt the column without salt. Additionally, if you want to make the index more efficient without the overheads associated with MAC integrity checks, you may also want to encrypt the columns with the NOMAC option.

Let’s re-create the table without these encryption defaults:

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number encrypt 'nomac' no salt, code number encrypt 'nomac' no salt, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

If we look at a block dump of this table:

Block header dump:  0x020000e4
Object id on Block? Y
seg/obj: 0x16cf0  csc: 0x00.2e75a9  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x20000e0 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.00a.00000872  0x0140b74e.00f0.09  –U-  148  fsc 0x0000.002e75bb
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x020000e4
data_block_dump,data header at 0x1dc2e64
===============
tsiz: 0x1f98
hsiz: 0x13a
pbl: 0x01dc2e64
76543210
flag=——–
ntab=1
nrow=148
frre=-1
fsbo=0x13a
fseo=0x46d
avsp=0x333
tosp=0x333
0xe:pti[0] nrow=148 offs=0

We notice the number of rows in the table has somewhat bounced back up to 148 rows.

If we look at some of the rows:

block_row_dump:
tab 0, row 0, @0x1b01
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  de 59 69 48 5e 65 d8 21 da 49 bd ba 19 d5 1f 80
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 34
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x1b30
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  9e 96 cc e1 57 07 74 61 64 19 99 4a ad 08 d9 93
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 35
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1b5f
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  f8 cf 58 ba f2 1b f7 a6 2d 59 1c c7 2c e4 28 86
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 36
col  3: [ 5]  42 4f 57 49 45

 

We see the length of the encrypted columns has dropped back down to 16 bytes, still more than the unencrypted columns but less than the 52 bytes required for the encrypted columns with both salt and MAC enabled.

Note though that the CODE column (col 1) while encrypted all now have the same encrypted hex value (9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61) without the salt applied. So the encrypted data is that little bit less secure but we can now successfully create B-tree indexes on these encrypted columns:

SQL> create index bowie_id_i on bowie(id);

Index created.

 

This however doesn’t end the various restrictions associated with indexing column encrypted columns as we’ll see in the next post.

Indexing and Transparent Data Encryption Part I (The Secret Life of Arabia) May 19, 2015

Posted by Richard Foote in Oracle Indexes.
Tags:
1 comment so far

Database security has been a really hot topic recently so I thought I might write a few posts in relation to indexing and Transparent Data Encryption (TDE) which is available as part of the Oracle Advanced Security option.

To protect the database from unauthorized “backed-door” accesses, the data within the actual database files can be encrypted. This helps prevent privileged users such as Database and System Administrators who have direct access to these files from being able to inappropriately access confidential data by simply viewing the data directly with OS editors and the such.

There are two basic ways one can use Oracle TDE to encrypt data within an Oracle database; via Column or Tablespace encryption. Column-based encryption has a number of restrictions, especially in relation to indexing which I’ll cover in later posts. To start with, my focus will be on Tablespace encryption.

With Tablespace encryption, all objects within the tablespace are encrypted in the same manner using the same encryption key and so has far fewer restrictions and issues.

It’s relatively easy to setup up tablespace encryption although note you must have the Oracle Advanced Security Option.

First, ensure you have an entry in your sqlnet.ora that identifies the location of your Oracle Security Wallet, such as:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=D:\wallet)))

As a user with ADMINISTER KEY MANAGEMENT privileges, create a password protected keystore wallet in the location referenced in the sqlnet.ora file such as:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'd:\wallet' identified by bowie;

keystore altered.

This wallet contains the master key used in turn to encrypt the various column and tablespace encryption keys used within the database to encrypt the actual data. Do not lose this wallet as without it, you will not be able to access any of your encrypted data !!

Next, open the database keystore using the password specified above such as:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY bowie;

keystore altered.

Finally, create an encrypted tablespace to hold all your sensitive data, such as:

SQL> CREATE TABLESPACE safe DATAFILE 'd:\app\rfoote\oradata\orcl\safe01.dbf ' size 10m ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

OK, to set the scene, we’re going to first create and populate a table in an unencrypted tablespace (note I’ve created tiny tablespaces here just so I can quickly open the file with a basic windows notepad editor):

SQL> create table bowie (id number, salary number, text varchar2(30)) tablespace unsafe;

Table created.

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

100000 rows created.

SQL> commit;

Commit complete.

I now flush the buffer cache to ensure all the data is written to disk:

SQL> alter system flush buffer_cache;

System altered.

If I now simply opened the tablespace data file with a text or hex editor, I can easily see the contents of the table, for example:

DAVID BOWIE,Ã]Ã]
DAVID BOWIE,Ã^Ã^
DAVID BOWIE,Ã_Ã_
DAVID BOWIE,Ã`Ã`
DAVID BOWIE,ÃaÃa
DAVID BOWIE,ÃbÃb
DAVID BOWIE,ÃcÃc
DAVID BOWIE,ÃdÃd
DAVID BOWIE,ÃÃ
DAVID BOWIE,ÃÃ
DAVID BOWIE,ÃÃ

If I however create the same data but store the table in the encrypted tablespace:

SQL> create table bowie2 (id number, salary number, text varchar2(30)) tablespace safe;

Table created.

SQL> insert into bowie2 select rownum, rownum, 'DAVID BOWIE' from dual connect by level <=100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

If I now try to view the contents of the encrypted data file, I find there’s nothing that’s in a readable format. For example:

4VûÇÓÓž|²÷hTîç:qz-XÒåPÏœkNþr#ßto¢€p$oÉ<÷¨q뉸øÊ^rìÔó­š< Ï›”ÕïÑꖸ轫P6″²Ý÷FE›Û!涷½:|WÞÍ)᪞!
W:ë¿®¡i':5g”=FE÷W’97ð˜g2Å1h›ö{ž—êûò×ø\ßÓð¶§
£õÉÔS‡DþÚ)?ÿ\‹L<ԏ#^@iIØ—Nkå#_}Í5BüŒèÚVµÄ?Àö•*¡”õtX
”osø

So good luck to any dodgy DBA or Sys Admin folk who tries to circumvent database security by directly viewing the database files or backups or stolen copies of the files, etc.

If we now also create an index on this table within the encrypted tablespace:

SQL> create index bowie2_idx on bowie2(id, text) tablespace safe;

Index created.

SQL> alter system flush buffer_cache;

System altered.

Again, a hunt for meaningful data in the data file will be fruitless. And because the data is encrypted at the tablespace layer, the index can be used with no restrictions:

SQL> set autotrace on
SQL> select * from bowie2 where id between 42 and 46;

        ID     SALARY TEXT
---------- ---------- ------------------------------
        42         42 DAVID BOWIE
        43         43 DAVID BOWIE
        44         44 DAVID BOWIE
        45         45 DAVID BOWIE
        46         46 DAVID BOWIE

Execution Plan
----------------------------------------------------------
Plan hash value: 3132122149

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes |  Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     5 |   215 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE2     |     5 |   215 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE2_IDX |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


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

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
         78  consistent gets
        311  physical reads
          0  redo size
        831  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Now comes the first trap.

If we were to replicate this data in an unencrypted tablespace, then of course the associated data would be visible within the database data files again. And it’s of course very easy to potentially replicate table data, either accidently or maliciously, via the creation of database indexes. For example, the default tablespace of a user might be in an unencrypted tablespace or a privileged user who we are trying to protect ourselves from has the create any index privilege.

So if we create an index in an unencrypted tablespace (I’m also adding a new row to easily identify it from the previous values inserted with the initial BOWIE table):

SQL> insert into bowie2 values (100001, 42, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

SQL> create index bowie2_all_i on bowie2(id, salary, text) tablespace unsafe;

Index created.

SQL> alter system flush buffer_cache;

System altered.

We can search all we want in the encrypted tablespace for meaningful data with little success but all the confidential data can now be seen via the index data, stored in the unencrypted tablespace:

ÃbVÃbVDAVID BOWIE@ <    ÃÁ+ZIGGY STARDUST@¡   ÃÃDAVID BOWIE@¡

Don’t let down your good TDE security intentions by being a little lazy in how you manage and police your database indexes.

Singapore Maths Question Solution and Very Interesting Observation (The Trickster) April 23, 2015

Posted by Richard Foote in Oracle Indexes.
13 comments

OK, time to reveal the solution to the somewhat tricky Singapore maths exam question I blogged previously.

Remember, there were 10 dates:

May 13   May 15   May 19

June 13   June 14

July 16   July 18

August 14   August 15   August 16

Bowie only knew the month of my birthday, Ziggy only knew the day.

Bowie begins by saying “I don’t know when your birthday is, but I know Ziggy does not know too.”

Now the first part is obvious, Bowie only knows the month and there are at least 2 possible dates for every month, so there is no way of Bowie knowing my birthday based on just the month. However, the second part is new information, Bowie knows that Ziggy doesn’t know either. How can he claim this ?

Well, there are 2 special dates on the list, May 19 and July 18 as these are the only dates that have a unique day. If Ziggy was given one of these days (18 or 19), he would instantly know my birthday. However, Bowie who only knows the month knows that Ziggy doesn’t know my birthday. He can only claim this if the month is not May and not July because if the month he was given was one of these months, Ziggy might then know my birthday.

So my birthday must be in either June or August and we can eliminate both May and July from further consideration.

Now Ziggy can likewise perform this logic and he now knows the month of my birthday must be either June or August. He now says “At first I don’t know when your birthday is, but now I know”.

The first part is again obvious as Bowie has already stated that Ziggy doesn’t at first know my birthday. But the second part is new information, he now knows. The only way he can now know is if my birthday is one of June 13, August 15 or August 16 as these are the only dates in June and August that have a unique day. If my birthday was on either June 14 or August 14, there is no way Ziggy could now know my birthday as they both have the same day.

So we’re now down to just these 3 possible dates.

Now Bowie can also likewise perform this logic and that my birthday must be one of June 13, August 15 or August 16. The final piece of information is Bowie saying “Then I also know when your birthday is.”. The only way he can say this is if my birthday is on June 13 as this is the only remaining date with a unique month. If my birthday had been either August 15 or August 16 then there is no way for Bowie is now know.

Therefore my birthday must indeed be June 13.

So congratulations to all of you who managed to get the correct answer :)

An Interesting Observation

Now here’s an interesting thing. 8 people got the correct answer. But equal on top of popular answers with 8 responses also was July 16. Why was July 16 such a popular answer, when the month of July was actually one of the earliest set of dates we could eliminate from consideration. It also wasn’t one of the unique day dates which is also a common answer ? What’s so special about July 16 that so many people picked this answer ?

Well, the original Singapore question I referenced in my original blog piece in which the students had to work out Cheryl’s birthday had a correct answer of, you guessed it, July 16. You see, not only did I change the names from the original question, but I also changed the dates as well.  However, I made sure that July 16 was one of the possible 10 dates :)

Now I don’t know how everyone got to their answer but it strikes me as an interesting coincidence that so many people got the same answer as to the original Singapore exam question. I wonder how many people looked up the answer from the Singapore question and just assumed the same answer fit here as well ? How many people saw enough information in the question for them to assume an answer, without being careful enough regarding the actual information laid out before them ? These are two similar but yet different questions which have two different answers.

And isn’t this such a common trait, especially in the IT world when trying to diagnose a performance issue. You see a problem in which you’ve seen a similar symptom before and assume that the root cause was the same as it might have been previously, even though the information at hand (if only you looked carefully enough) is entirely different and doesn’t actually lend itself to the same root cause.

Just because the reason for lots of log file sync waits before was slow write performance to the redo log files doesn’t necessarily mean it’s the same reason today. Just because you have slow RAC cluster waits previously due to interconnect issues doesn’t necessarily mean it’s now the root cause. Quickly jumping to the wrong conclusion is such an easy mistake to make when trying to diagnose a performance problem. One needs to look carefully at all the facts on hand and with an Oracle database, there is no excuse to not look at all the relevant, appropriate facts when diagnosing a performance problem.

No, just because someone previously had a brain tumour when they had a headache doesn’t necessarily mean your headache is the result of a brain tumour :)

Hope you had some fun, I certainly did !! :)

Good Singapore Maths Students Would Likely Make Good Oracle DBAs (Problems) April 15, 2015

Posted by Richard Foote in Oracle Indexes.
32 comments

An interesting mathematics based question from a Singapore high school exam has been doing the internet rounds in the past few days. Considering it’s aimed at high school students, it’s a tricky one and obviously designed to filter out the better students, in a country with a very good reputation for churning out mathematically gifted students.

I enjoy solving problems and this one took me a few minutes to work it out. However, at the end of the process, it occurred to me that I used a similar process to how I’ve often solved performance issues with Oracle databases. In fact, this question kinda reminded me of a performance issue that I had only just recently been asked by a customer to help resolve.

One needs to clearly understand the question being asked. One also needs to focus and understand the data at hand. Then use a process of elimination to both rule out and just as importantly rule in possible answers (or root causes to performance issues). Eventually, one can then narrow down and pinpoint things down to a specific solution (or set of answers).

So for example, the database is running exceptionally slow globally at certain times, why ?  So it looks like it’s because there are lots of cluster related waits at these times, why ? So it looks like it’s because writing to the redo logs is really slow at these times, why ? And so on and so on.

If you can work out the solution to this problem in a reasonably timely manner, then in all likelihood you have good problem solving skills and the makings of a good Oracle DBA. You just need to also like donuts and good whiskies :)

I’ve reproduced the question here, changing the names to protect the innocent.

“Bowie and Ziggy just become friends with me, and they want to know when my birthday is. I give them 10 possible dates:

May 13   May 15   May 19

June 13   June 14

July 16   July 18

August 14   August 15   August 16

I then tell Bowie and Ziggy separately the month and the day of my birthday respectively.

Bowie: I don’t know when your birthday is, but I know Ziggy does not know too.

Ziggy: At first I don’t know when your birthday is, but now I know.

Bowie: Then I also know when your birthday is.

So when is my birthday ?”

Feel free to comment on what you think the answer is but please don’t give away how you might have worked it out. For those interested (or for those that don’t check out the solution on the web first :) ), I’ll explain how to get to the answer in a few days time.

Like I said, if you get it right, you should consider a career as an Oracle DBA !! And here’s a link to an excellent whisky: Sullivans Cove Whisky :)

12.1.0.2 Introduction to Zone Maps Part III (Little By Little) November 24, 2014

Posted by Richard Foote in 12c, Attribute Clustering, Oracle Indexes, Zone Maps.
1 comment so far

I’ve previously discussed the new Zone Map database feature and how they work in a similar manner to Exadata Storage indexes.

Just like Storage Indexes (and conventional indexes for that manner), they work best when the data is well clustered in relation to the Zone Map or index. By having the data in the table ordered in the same manner as the Zone Map, the ranges of the min/max values for each 8M “zone” in the table can be as narrow as possible, making them more likely to eliminate zone accesses.

On the other hand, if the data in the table is not well clustered, then the min/max ranges within the Zone Map can be extremely wide, making their effectiveness limited.

In my previous example on the ALBUM_ID column in my first article on this subject, the data was extremely well clustered and so the associated Zone Map was very effective. But what if the data is poorly clustered ?

To illustrate, I’m going to create a Zone Map based on the poorly clustered ARTIST_ID column, which has its values randomly distributed throughout the whole table:

SQL> create materialized zonemap big_bowie_artist_id_zm on big_bowie(artist_id);
 create materialized zonemap big_bowie_artist_id_zm on big_bowie(artist_id)
 *
 ERROR at line 1:
 ORA-31958: fact table "BOWIE"."BIG_BOWIE" already has a zonemap
 "BOWIE"."BIG_BOWIE_ALBUM_ID_ZM" on it

Another difference between an index and Zone Map is that there can only be the one Zone Map defined per table, but a Zone Map can include multiple columns. As I already have a Zone Map defined on just the ALBUM_ID column, I can’t just create another.

So I’ll drop the current Zone Map and create a new one based on both the ARTIST_ID and ALBUM_ID columns:

SQL> drop materialized zonemap big_bowie_album_id_zm;

Materialized zonemap dropped.

SQL> create materialized zonemap big_bowie_zm on big_bowie(album_id, artist_id);

Materialized zonemap created.
    
 SQL> select measure, position_in_select, agg_function, agg_column_name
 from dba_zonemap_measures where zonemap_name='BIG_BOWIE_ZM';

MEASURE              POSITION_IN_SELECT AGG_FUNCTION  AGG_COLUMN_NAME
 -------------------- ------------------ ------------- --------------------
 "BOWIE"."BIG_BOWIE".                  5 MAX           MAX_2_ARTIST_ID
 "ARTIST_ID"

"BOWIE"."BIG_BOWIE".                  4 MIN           MIN_2_ARTIST_ID
 "ARTIST_ID"

"BOWIE"."BIG_BOWIE".                  3 MAX           MAX_1_ALBUM_ID
 "ALBUM_ID"

"BOWIE"."BIG_BOWIE".                  2 MIN           MIN_1_ALBUM_ID
 "ALBUM_ID"

So this new Zone Map has min/max details on each zone in the table for both the ARTIST_ID and ALBUM_ID columns.

The min/max ranges of a Zone Map provides an excellent visual representation of the clustering of the data. If I select Zone Map details of the ALBUM_ID column (see partial listing below):

SQL> select zone_id$, min_1_album_id, max_1_album_id, zone_rows$ from big_bowie_zm;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_ROWS$
 ---------- -------------- -------------- ----------
 3.8586E+11              1              2      66234
 3.8586E+11              5              6      56715
 3.8586E+11              7              7      76562
 3.8586E+11              7              8      76632
 3.8586E+11              8              9      76633
 3.8586E+11             21             22      75615
 3.8586E+11             29             29      75582
 3.8586E+11             31             32      75545
 3.8586E+11             35             36      75617
 3.8586E+11             43             44      75615
 ...

3.8586E+11             76             77      75615
 3.8586E+11             79             80      75615
 3.8586E+11             86             87      75616
 3.8586E+11             88             89      75618
 3.8586E+11             97             97      75771
 3.8586E+11            100            100      15871

134 rows selected.

As the data in the table is effectively ordered based on the ALBUM_ID column (and so is extremely well clustered in relation to this column), the min/max ranges for each zone is extremely narrow. Each zone basically only contains one or two different values of ALBUM_ID and so if I’m just after a specific ALBUM_ID value, the Zone Map is very effective in eliminating zones from having to be accessed. Just what we want.

However, if we look at the Zone Map details of the poorly clustered ARTIST_ID column (again just a partial listing):

SQL> select zone_id$, min_2_artist_id, max_2_artist_id, zone_rows$ from big_bowie_zm;

ZONE_ID$ MIN_2_ARTIST_ID MAX_2_ARTIST_ID ZONE_ROWS$
 ---------- --------------- --------------- ----------
 3.8586E+11            3661           98244      66234
 3.8586E+11               1          100000      56715
 3.8586E+11            5273           81834      76562
 3.8586E+11               1          100000      76632
 3.8586E+11               1          100000      76633
 3.8586E+11               1          100000      75615
 3.8586E+11            2383           77964      75582
 3.8586E+11               1          100000      75545
 3.8586E+11               1          100000      75617
 3.8586E+11               1          100000      75615
 ...

3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75616
 3.8586E+11               1          100000      75618
 3.8586E+11            4848           80618      75771
 3.8586E+11           84130          100000      15871

134 rows selected.

We notice the ranges for most of the zones is extremely large, with many actually having a min value of 1 (the actual minimum) and a max of 100000 (the actual maximum). This is a worst case scenario as a specific required value could potentially reside in most of the zones, thereby  forcing Oracle to visit most zones and making the Zone Map totally ineffective.

If we run a query searching for a specific ARTIST_ID:

SQL> select * from big_bowie where artist_id=42;

100 rows selected.

Elapsed: 00:00:00.69

Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
 | Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                       |           |    99 |  9108 |  3291  (13)| 00:00:01 |
 |*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |    99 |  9108 |  3291  (13)| 00:00:01 |
 ----------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------

1 - storage("ARTIST_ID"=42)
 filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
 BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_2_ARTIST_ID" > :1 OR
 zm."MAX_2_ARTIST_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."BIG_BOWIE_ZM" zm WHERE
 zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND
 "ARTIST_ID"=42)

      
 Statistics
 ----------------------------------------------------------
 141  recursive calls
 0  db block gets
 101614  consistent gets
 0  physical reads
 0  redo size
 5190  bytes sent via SQL*Net to client
 618  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 we are forced to perform a very high number of consistent gets (101,614) when returning just 100 rows, much higher than the 2,364 consistent gets required to return a full 100,000 rows for a specific ALBUM_ID and not far from the 135,085 consistent gets when performing a full table scan.

We need to improve the performance of these queries based on the ARTIST_ID column …

Let’s drop this zone map:

SQL> drop materialized zonemap big_bowie_zm;

Materialized zonemap dropped.

and change the physical clustering of the data in the table so that the data is primarily now clustered in ARTIST_ID order:

 

SQL> alter table big_bowie add clustering by linear order(artist_id, album_id) with materialized zonemap;

Table altered.

So we have added a clustering attribute to this table (previously discussed here) and based a new Zone Map on this clustering at the same time.

SQL> select zonemap_name from dba_zonemaps where fact_table='BIG_BOWIE';

ZONEMAP_NAME
---------------
ZMAP$_BIG_BOWIE

SQL> select zonemap_name, pruning, with_clustering, invalid, stale, unusable
from dba_zonemaps where zonemap_name = 'ZMAP$_BIG_BOWIE';

ZONEMAP_NAME    PRUNING  WITH_CLUSTERING INVALID STALE   UNUSABLE
--------------- -------- --------------- ------- ------- --------
ZMAP$_BIG_BOWIE ENABLED  YES             NO      NO      NO

However, as we haven’t actually reorganized the table, the rows in the table are still clustered the same as before:

SQL> select zone_id$, min_2_album_id, max_2_album_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_2_ALBUM_ID MAX_2_ALBUM_ID ZONE_ROWS$
---------- -------------- -------------- ----------
3.8586E+11             43             44      75615
3.8586E+11              1              2      66234
3.8586E+11             81             82      75615
3.8586E+11             29             29      75582
3.8586E+11             50             50      75481
3.8586E+11             90             91      75484
3.8586E+11              5              6      56715
3.8586E+11              7              8      76632
3.8586E+11              8              9      76633
3.8586E+11             16             16      75481
...

3.8586E+11             44             44      75480
3.8586E+11             82             83      75616
3.8586E+11            100            100      15871
3.8586E+11             34             35      75576
3.8586E+11             14             15      75615
3.8586E+11             33             34      75616
3.8586E+11              3              5      75707

134 rows selected.

SQL> select zone_id$, min_1_artist_id, max_1_artist_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_1_ARTIST_ID MAX_1_ARTIST_ID ZONE_ROWS$
---------- --------------- --------------- ----------
3.8586E+11               1          100000      75545
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75617
3.8586E+11               1          100000      75911
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75615
3.8586E+11               1          100000      75616
3.8586E+11             132           75743      75612
3.8586E+11               1          100000      75615
...

3.8586E+11               1          100000      66296
3.8586E+11               1          100000      75615
3.8586E+11            2360           96960      75701
3.8586E+11               1          100000      75615
3.8586E+11               1          100000      75616
3.8586E+11           23432           98911      75480
3.8586E+11               1          100000      75791
3.8586E+11           21104           96583      75480

134 rows selected.

But if we now reorganise the table so that the clustering attribute can take effect:

SQL> alter table big_bowie move;

Table altered.

We notice the characteristics of the Zone Map has change dramatically. The previously well clustered ALBUM_ID now has a totally ineffective Zone Map with all the ranges effectively consisting of the full min/max values:

SQL> select zone_id$, min_2_album_id, max_2_album_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_2_ALBUM_ID MAX_2_ALBUM_ID ZONE_ROWS$
---------- -------------- -------------- ----------
3.9704E+11              1            142      21185
3.9704E+11              1            100       9452
3.9704E+11              1            100      76516
3.9704E+11              1            100      75501
3.9704E+11              1            100      75497
3.9704E+11              1            100      75501
3.9704E+11              1            100      75499
3.9704E+11              1            100      75504
3.9704E+11              1            100      75500
3.9704E+11              1            100      75501
...

3.9704E+11              1            100      75503
3.9704E+11              1            100      75498
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75794

144 rows selected.

While the previously ineffective Zone Map on the ARTIST_ID column is now much more effective with significantly smaller min/max ranges for each zone:

SQL> select zone_id$, min_1_artist_id, max_1_artist_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_1_ARTIST_ID MAX_1_ARTIST_ID ZONE_ROWS$
---------- --------------- --------------- ----------
3.9704E+11              67            1036      21185
3.9704E+11            2359            2453       9452
3.9704E+11            8341            9106      76516
3.9704E+11           18933           19688      75501
3.9704E+11           22708           23463      75497
3.9704E+11           26483           27238      75501
3.9704E+11           27238           27993      75499
3.9704E+11           33278           34033      75504
3.9704E+11           36674           40449      75500
3.9704E+11           38563           39318      75501
...

3.9704E+11           49888           50643      75503
3.9704E+11           62723           63478      75498
3.9704E+11           77824           78579      75501
3.9704E+11           82354           83109      75501
3.9704E+11           88394           89149      75501
3.9704E+11           93679           94434      75501
3.9704E+11           98211           98969      75794

144 rows selected.

The same query now runs so much faster as the Zone Map can eliminate almost all zones from being accessed:

SQL> select * from big_bowie where artist_id=42;

100 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |    99 |  9108 |  3291  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |    99 |  9108 |  3291  (13)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ARTIST_ID"=42)
filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_1_ARTIST_ID" > :1 OR
zm."MAX_1_ARTIST_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."ZMAP$_BIG_BOWIE" zm WHERE
zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND
"ARTIST_ID"=42)
Statistics
----------------------------------------------------------
187  recursive calls
0  db block gets
175  consistent gets
0  physical reads
0  redo size
5190  bytes sent via SQL*Net to client
618  bytes received via SQL*Net from client
8  SQL*Net roundtrips to/from client
9  sorts (memory)
0  sorts (disk)
100  rows processed

Consistent gets has reduced dramatically down to just 175 from the previously massive 101,614.

As is common with changing the clustering of data, what improves one thing makes something else significantly worse. The previously efficient accesses based on the ALBUM_ID column is now nowhere near as efficient as before:

SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:01.27

Execution Plan
----------------------------------------------------------
Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |   100K|  8984K|  3269  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |   100K|  8984K|  3269  (12)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ALBUM_ID"=42)
filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_2_ALBUM_ID" > :1 OR
zm."MAX_2_ALBUM_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."ZMAP$_BIG_BOWIE" zm WHERE
zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND "ALBUM_ID"=42)

Statistics
----------------------------------------------------------
187  recursive calls
0  db block gets
141568  consistent gets
0  physical reads
0  redo size
4399566  bytes sent via SQL*Net to client
73878  bytes received via SQL*Net from client
6668  SQL*Net roundtrips to/from client
9  sorts (memory)
0  sorts (disk)
100000  rows processed

We now have to perform a whopping 141,568 consistent gets up from the previous 2,364 consistent gets.

So Zone Maps, like database indexes and Exadata Storage Indexes, can be extremely beneficial in reducing I/O but their effectiveness is very much dependant on the clustering of the underlining data.

Index Advanced Compression vs. Bitmap Indexes (Candidate) October 31, 2014

Posted by Richard Foote in 12c, Advanced Index Compression, Bitmap Indexes, Oracle Indexes.
6 comments

A good question from Robert Thorneycroft I thought warranted its own post. He asked:

I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 million row table’ would still be a viable scenario for deploying bitmapped indexes over non-compressed b-tree indexes.

Now b-tree index compression is common, especially with the release of Advanced Index Compression how does this affect your conclusion? Are there still any rules of thumb which can be used to determine when to deploy bitmapped indexes instead of compressed b-tree indexes or has index compression made bitmapped indexes largely redundant?”

 

If you’re not familiar with Bitmap Indexes, it might be worth having a read of my previous posts on the subject.

Now Advanced Index Compression introduced in 12.1.0.2 has certainly made compressing indexes a lot easier and in many scenarios, more efficient than was previously possible. Does that indeed mean Bitmap Indexes, that are relatively small and automatically compressed, are now largely redundant ?

The answer is no, Bitmap Indexes are still highly relevant in Data Warehouse environments as they have a number of key advantages in the manner they get compressed over B-Tree Indexes.

Compression of a B-Tree index is performed within a leaf block where Oracle effectively de-duplicates the index entries (or parts thereof). This means that a highly repeated index value might need to be stored repeatedly in each leaf block. Bitmap index entries on the other hand can potentially span the entire table and only need to be split if the overall size of the index entries exceeds 1/2 a block. Therefore, the number of indexed values stored in a Bitmap Index can be far less than with a B-tree.

However, it’s in the area of storing the associated rowids where Bitmap Indexes can have the main advantage. With a B-tree index, even when highly compressed, each and every index entry must have an associated rowid stored in the index. If you have say 1 million index entries, that’s 1 million rowids that need to be stored, regardless of the compression ratio. With a Bitmap Index, an index entry has 2 rowids to specify the range of rows covered by the index entry, but this might be sufficient to cover the entire table. So depending on the number of distinct values being indexed in say a million row table, there may be dramatically fewer than 1 million rowids stored in the Bitmap Index.

To show how Bitmap Indexes are generally much smaller than corresponding compressed B-Tree indexes, a few simple examples.

In example 1, I’m going to create a B-Tree Index that is perfect candidate for compression. This index has very large indexed values that are all duplicates and so will compress very effectively:

SQL> create table ziggy (id number, weird varchar2(100));

Table created.

SQL> insert into ziggy select rownum, 'THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'
     from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy_weird_i on ziggy(weird) pctfree 0;

Index created.

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

INDEX_NAME        BLEVEL LEAF_BLOCKS   NUM_ROWS
------------- ---------- ----------- ----------
ZIGGY_WEIRD_I          2        9175    1000000

SQL> drop index ziggy_weird_i2;

Index dropped.

SQL> create index ziggy_weird_i on ziggy(weird) pctfree 0 compress advanced low;

Index created.

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

INDEX_NAME        BLEVEL LEAF_BLOCKS   NUM_ROWS
------------- ---------- ----------- ----------
ZIGGY_WEIRD_I          2        1389    1000000

 

So this index has compressed down from 9175 leaf blocks to just 1389. That’s impressive.

However, this scenario is also the perfect case for a Bitmap Index with large, highly repeated index entries. If we compare the compressed B-Tree Index with a corresponding Bitmap index:

SQL> create bitmap index ziggy_weird_i on ziggy(weird) pctfree 0;

Index created.

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

INDEX_NAME        BLEVEL LEAF_BLOCKS   NUM_ROWS
------------- ---------- ----------- ----------
ZIGGY_WEIRD_I          1          21         42

 

At just a tiny 21 leaf blocks, the Bitmap Index wins by a mile.

In example 2, I’m going to create an index that still almost a perfect case for compressing a B-Tree Index, but far less so for a Bitmap Index. I’m going to create enough duplicate entries to just about fill a specific leaf block, so that each leaf block only has 1 or 2 distinct index values. However, as we’ll have many more distinct indexed values overall, this means we’ll need more index entries in the corresponding Bitmap Index.

SQL> create table ziggy2 (id number, weird varchar2(100));

Table created.

SQL> insert into ziggy2 select rownum, 'THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'||mod(rownum,1385)
     from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.
SQL> create index ziggy2_weird_i on ziggy2(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY2_WEIRD_I          2        9568    1000000

SQL> drop index ziggy2_weird_i;

Index dropped.

SQL> create index ziggy2_weird_i on ziggy2(weird) pctfree 0 compress advanced low;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY2_WEIRD_I          2        1401    1000000

 

So we have a relatively large indexed column that has some 1385 distinct values but each value just about fills out a compress leaf block. If we look at the compression of the index, we have reduced the index down from 9568 leaf blocks to just 1401 leaf blocks. Again, a very impressive compression ratio.

Unlike the previous example where we had just the one value, we now have some 1385 index entries that need to be created as a minimum for our Bitmap Index. So how does it compare now ?

SQL> drop index ziggy2_weird_I;

Index dropped.

SQL> create bitmap index ziggy2_weird_i on ziggy2(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY2_WEIRD_I          2         462       1385

 

Although the Bitmap Index is much larger than it was in the previous example, at just 464 leaf blocks it’s still significantly smaller than the corresponding compressed 1401 leaf block B-Tree index.

OK, example 3, we’re going to go into territory where no Bitmap Index should tread (or so many myths would suggest). We going to index a column in which each value only has the one duplicate. So for our 1 million row table, the column will have some 500,000 distinct values.

With relatively few duplicate column values, the compression of our B-Tree Indexes is not going to be as impressive. However, because the indexed values are still relatively large, any reduction here would likely have some overall impact:

SQL> create table ziggy3 (id number, weird varchar2(100));

Table created.

SQL> insert into ziggy3 select rownum, 'THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS'||mod(rownum,500000)
     from dual connect by level<=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy3_weird_i on ziggy3(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY3_WEIRD_I          2        9891    1000000

SQL> drop index ziggy3_weird_i;

Index dropped.

SQL> create index ziggy3_weird_i on ziggy3(weird) pctfree 0 compress advanced low;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY3_WEIRD_I          2        6017    1000000

 

So the compression ratio is not as good now, coming down to 6017 leaf blocks from 9891. However, this will surely be better than a Bitmap Index with 500,000 distinct values …

 

SQL> drop index ziggy3_weird_i;

Index dropped.

SQL> create bitmap index ziggy3_weird_i on ziggy3(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY3_WEIRD_I          2        5740     500000

 

So even in this extreme example, the Bitmap Index at 5740 leaf blocks is still smaller than the corresponding compressed B-Tree Index at 6017 leaf blocks.

In this last example 4, it’s a scenario similar to the last one, except the index entries themselves are going to be much smaller (a few byte number column vs. the 60 odd byte varchar2). Therefore, the rowids of the index entries will be a much larger proportion of the overall index entry size. Reducing the storage of index values via compression will be far less effective, considering the prefix table in a compressed index comes with some overhead.

SQL> create table ziggy4 (id number, weird number);

Table created.

SQL> insert into ziggy4 select rownum, mod(rownum,500000) from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy4_weird_i on ziggy4(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY4_WEIRD_I          2        1998    1000000

SQL> drop index ziggy4_weird_i;

Index dropped.

SQL> create index ziggy4_weird_i on ziggy4(weird) pctfree 0 compress advanced low;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY4_WEIRD_I          2        1998    1000000

 

So Index Advanced Compression has decided against compressing this index, it’s just not worth the effort. If we force compression:

 

SQL> drop index ziggy4_weird_i;

Index dropped.

SQL> create index ziggy4_weird_i on ziggy4(weird) pctfree 0 compress;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY4_WEIRD_I          2        2065    1000000

 

We notice the index has actually increased in size, up to 2065 leaf blocks from 1998. The overheads of the prefix table over-ride the small efficiencies of reducing the duplicate number indexed values.

Meanwhile the corresponding Bitmap Index:

SQL> drop index ziggy4_weird_i;

Index dropped.

SQL> create bitmap index ziggy4_weird_i on ziggy4(weird) pctfree 0;

Index created.

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

INDEX_NAME         BLEVEL LEAF_BLOCKS   NUM_ROWS
-------------- ---------- ----------- ----------
ZIGGY4_WEIRD_I          2        1817     500000

 

Is still smaller at 1817 leaf blocks than the best B-Tree index has to offer.

So the answer is no, Bitmap Indexes are not now redundant now we have Index Advanced Compression. In Data Warehouse environments, as long as they don’t reference column values that are approaching uniqueness,  Bitmap Indexes are likely going to be smaller than corresponding compressed B-Tree indexes.

12.1.0.2 Introduction to Zone Maps Part II (Changes) October 30, 2014

Posted by Richard Foote in 12c, Exadata, Oracle Indexes, Zone Maps.
add a comment

In Part I, I discussed how Zone Maps are new index like structures, similar to Exadata Storage Indexes, that enables the “pruning” of disk blocks during accesses of the table by storing the min and max values of selected columns for each “zone” of a table. A Zone being a range of contiguous (8M) blocks.

I showed how a Zone Map was relatively tiny but very effective in reducing the number of consistent gets for a well clustered column (ALBUM_ID).

In this post, we’re going to continue with the demo and look at what happens when we update data in the table with a Zone Map in place.

So lets update the ALBUM_ID column (which currently has a Zone Map defined) for a few rows. The value of ALBUM_ID was previously 1 for all these rows (the full range of values is currently between 1 and 100) but we’re going to update them to 142:

SQL> update big_bowie set album_id=142 where id between 1 and 100;

100 rows updated.

SQL> commit;

Commit complete.

 

So the maximum value of ALBUM_ID is now 142, not 100. If we look at the maximum value as currently listed in the Zone Map:

 

SQL> select max(max_1_album_id) from  big_bowie_album_id_zm;

MAX(MAX_1_ALBUM_ID)
-------------------
100

 

We notice the maximum is still defined as being 100. So the update on the table has not actually updated the contents of the Zone Map. So this is a big difference between Zone Maps and conventional indexes, indexes are automatically updated during DML operations, Zone Maps are not (unless the REFRESH ON COMMIT option is specified).

If we look at the state of Zone Map entries that have a minimum of 1 (the previous values of ALBUM_ID before the update):

SQL> select * from big_bowie_album_id_zm where min_1_album_id = 1;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$
---------- -------------- -------------- ----------- ----------- ----------
3.8586E+11              1              2           0           0      66234
3.8586E+11              1              2           0           1      65787
3.8586E+11              1              2           0           0      66223

 

We notice that one of the entries has a status of 1, meaning that a specific zone has been marked as stale. However, all the other zones are still OK.

If we look at the status of the overall Zone Map:

SQL> select zonemap_name, pruning, refresh_mode, invalid, stale, unusable
from dba_zonemaps where zonemap_name='BIG_BOWIE_ALBUM_ID_ZM';

ZONEMAP_NAME              PRUNING  REFRESH_MODE      INVALID STALE   UNUSABLE
------------------------- -------- ----------------- ------- ------- --------
BIG_BOWIE_ALBUM_ID_ZM     ENABLED  LOAD DATAMOVEMENT NO      NO      NO

 

We notice that the Zone Map is still “hunky dory” after the update.

If we now re-run the query we ran in Part I:

 

SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |   100K|  8984K|  3269  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |   100K|  8984K|  3269  (12)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ALBUM_ID"=42)
filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_1_ALBUM_ID" > :1 OR
zm."MAX_1_ALBUM_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."BIG_BOWIE_ALBUM_ID_ZM" zm
WHERE zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND "ALBUM_ID"=42)
Statistics
----------------------------------------------------------
141  recursive calls
0  db block gets
3238  consistent gets
0  physical reads
0  redo size
3130019  bytes sent via SQL*Net to client
761  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

 

We see the Zone Map was still used by the CBO. The number of consistent gets has increased (up from 2364 to 3238) as we now have to additional access all the blocks associated with this stale zone, but it’s still more efficient that reading all the blocks from the entire table.

If we want to remove the stale zone entries, we can refresh the Zone Map or rebuild it (for ON DEMAND refresh):

 

SQL> alter materialized zonemap big_bowie_album_id_zm rebuild;

Materialized zonemap altered.

 

If we now look at the Zone Map entry:

 

SQL> select * from big_bowie_album_id_zm where min_1_album_id = 1;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$
---------- -------------- -------------- ----------- ----------- ----------
3.8586E+11              1              2           0           0      66234
3.8586E+11              1            142           0           0      65787
3.8586E+11              1              2           0           0      66223

 

We see that the entry is no longer stale and now correctly reflects the actual maximum value within the zone (142).

If we now re-run the query:

SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |   100K|  8984K|  3269  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |   100K|  8984K|  3269  (12)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ALBUM_ID"=42)
filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_1_ALBUM_ID" > :1 OR
zm."MAX_1_ALBUM_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."BIG_BOWIE_ALBUM_ID_ZM" zm
WHERE zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND "ALBUM_ID"=42)
Statistics
----------------------------------------------------------
141  recursive calls
0  db block gets
3238  consistent gets
0  physical reads
0  redo size
3130019  bytes sent via SQL*Net to client
761  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

 

We notice nothing has appreciably changed, the Zone Map is still being used but the number of consistent gets remains the same as before. Why haven’t we returned back to our previous 2364 consistent gets ?

Well, as the range of possible values within the updated zone is now between 1 and 142, the required value of 42 could potentially be found within this zone and so still needs to be accessed just in case. We know that the value of 42 doesn’t exist within this zone, but Oracle has no way of knowing this based on the possible 1 to 142 range.

Hence Zone Maps work best when the data is well clustered and the Min/Max ranges of each zone can be used to limit which zones need to be accessed. If the data was not well clustered and the values within each zone mostly had ranges between the min and max values, then Oracle wouldn’t be able to effectively prune many/any zone and the Zone Map would be useless.

As we’ll see in Part III :)

Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl) October 9, 2014

Posted by Richard Foote in 12c, Advanced Index Compression, Block Dumps, Index Compression, Oracle Indexes.
5 comments

Sometimes, a few pictures (or in this case index block dumps) is better than a whole bunch of words :)

In my previous post, I introduced the new Advanced Index Compression feature, whereby Oracle automatically determines how to best compress an index. I showed a simple example of an indexed column that had sections of index entries that were basically unique (and so don’t benefit from compression) and other sections with index entries that had many duplicates (that do compress well). Advanced Index Compression enables Oracle to automatically just compress those index leaf blocks where compression is beneficial.

If we look at a couple of partial block dumps from this index, first a dump from a leaf block that did have duplicate index entries:

Leaf block dump
===============
header address 216542820=0xce82e64
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 651
kdxcofbo 1346=0x542
kdxcofeo 2172=0x87c
kdxcoavs 826
kdxlespl 0
kdxlende 0
kdxlenxt 25166046=0x18000de
kdxleprv 25166044=0x18000dc
kdxledsz 0
kdxlebksz 8036
kdxlepnro 1
kdxlepnco 1 (Adaptive)
prefix row#0[8031] flag: -P—–, lock: 0, len=5
col 0; len 2; (2):  c1 2b
prc 651
row#0[8022] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 86 00 5c
psno 0
row#1[8013] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 86 00 5d
psno 0
row#2[8004] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 86 00 5e
psno 0
row#3[7995] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 86 00 5f
psno 0
row#4[7986] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 86 00 60
psno 0

row#650[2172] flag: ——-, lock: 0, len=9
col 0; len 6; (6):  01 80 1e 8d 00 10
psno 0
—– end of leaf block Logical dump —–

The red section is a portion of the index header that determines the number of rows in the prefix table of the index (kdxlepnro 1). The prefix table basically lists all the distinct column values in the leaf blocks that are to be compressed. The value 1 denotes there is actually only just the 1 distinct column value in this specific leaf block (i.e. all index entries have the same indexed value). This section also denotes how many of the indexed columns are to be compressed (kdxlepnco 1). As this index only has the one column, it also has a value of 1. Note this value can potentially be anything between 0 (no columns compressed) up to the number of columns in the index.  The (Adaptive) reference tells us that Index Advanced Compression has been used and that the values here can change from leaf block to leaf block depending on the data characteristics of the index entries within each leaf block (a dump of a basic compressed index will not have the “Adaptive” reference).

The green section is the compression prefix table and details all the unique combinations of index entries to be compressed within the leaf block. As all indexed values are the same in this index (value 42, internally represented as c1 2b hex), the prefix table only has the one row. prc 651 denotes that all 651 index entries in this leaf block have this specific indexed value.

Next follows all the actual index entries, which now only consist of the rowid (the 6 byte col 0 column) as they all reference psno 0, which is the unique row id of the only row within the prefix table (row#0).

So rather than storing the indexed value 651 times, we can just store the index value (42) just the once within the prefix table and simply reference it from within the actual index entries. This is why index compression can save us storage, storing something once within a leaf block rather than multiple times.

If we now look at a partial block dump of another index leaf block within the index, that consists of many differing (basically unique) index entries:

Leaf block dump
===============
header address 216542820=0xce82e64
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 449
kdxcofbo 938=0x3aa
kdxcofeo 1754=0x6da
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 25168667=0x1800b1b
kdxleprv 25168665=0x1800b19
kdxledsz 0
kdxlebksz 8036
kdxlepnro 0
kdxlepnco 0 (Adaptive)
row#0[8022] flag: ——-, lock: 0, len=14
col 0; len 4; (4):  c3 58 3d 2c
col 1; len 6; (6):  01 80 12 e6 00 41
row#1[8008] flag: ——-, lock: 0, len=14
col 0; len 4; (4):  c3 58 3d 2d
col 1; len 6; (6):  01 80 12 e6 00 42
row#2[7994] flag: ——-, lock: 0, len=14
col 0; len 4; (4):  c3 58 3d 2e
col 1; len 6; (6):  01 80 12 e6 00 43


row#448[1754] flag: ——-, lock: 0, len=14
col 0; len 4; (4):  c3 58 41 5c
col 1; len 6; (6):  01 80 12 ee 00 1d
—– end of leaf block Logical dump —–

We notice that in the red section, both kdxlepnro 0 and kdxlepnco 0 (Adaptive) have a value of 0, meaning we have no rows and no columns within the prefix table. As such, we have no prefix table at all here and that this leaf block has simply not been compressed.

If we look at the actual index entries, they all have an additional column now in blue, that being the actual indexed value as all the index values in this leaf block are different from each other. Without some form of index entry duplication, there would be no benefit from compression and Index Advanced Compression has automatically determined this and not bothered to compress this leaf block. An attempt to compress this block would have actually increased the necessary overall storage for these index entries, due to the additional overheads associated with the prefix table (note it has an additional 2 byes of overhead per row within the prefix table).

I’ll next look at an example of a multi-column index and how Index Advanced Compression handles which columns in the index to compress.

Index Compression Part V: 12c Advanced Index Compression (Little Wonder) October 2, 2014

Posted by Richard Foote in 12c, Advanced Index Compression, Index Compression, Oracle Indexes.
2 comments

I’ve finally managed to find some free time in the evening to write a new blog piece :)

This will have to be the record for the longest time between parts in a series, having written Part IV of this Index Compression series way way back in February 2008 !! Here are the links to the previous articles in the series:

Index Compression Part I (Low)

Index Compression Part II (Down Is The New Up)

Index Compression Part III (2+2=5)

Index Compression Part IV (Packt Like Sardines In a Crushd Tin Box)

 

As I’ve previously discussed, compressing an index can be an excellent way to permanently reduce the size of an index in a very cost effective manner. Index entries with many duplicate values (or duplicate leading columns within the index) can be “compressed” by Oracle to reduce both storage overheads and potentially access overheads for large index scans. Oracle basically de-duplicates repeated indexed column values within each individual leaf block by storing each unique occurrence in a prefix section within the block, as I explain in the above links.

But it’s important to compress the right indexes in the right manner. If indexes do not have enough repeated data, it’s quite possible to make certain indexes larger rather than smaller when using compression (as the overheads of having the prefix section in the index block outweighs the benefits of limited reduction of repeated values). So one needs to be very selective on which indexes to compress and take care to compress the correct number of columns within the index. Oracle will only protect you from yourself if you attempt to compress all columns in a unique index, as in this scenario there can be no duplicate values to compress. This is all discussed in Part II and Part III of the series.

So, wouldn’t it be nice if Oracle made it all a lot easier for us and automatically decided which indexes to compress, which columns within the index to compress and which indexes to simply not bother compressing at all. Additionally, rather than an all or nothing approach in which all index leaf blocks are compressed in the same manner, wouldn’t it be nice if Oracle decided for each and every individual leaf block within the index how to best compress it. For those index leaf block that have no duplicate entries, do nothing, for those with some repeated columns just compress them and for those leaf blocks with lots of repeated columns and values to compress all of them as efficiently as possible.

Well, wish no more :)

With the recent release of Oracle Database 12.1.0.2, one of the really cool new features that got introduced was Advanced Index Compression. Now a warning from the get-go. The use of Advanced Index Compression requires the Advanced Compression Option and this option is automatically enabled with Enterprise Edition. So only use this feature if you are licensed to do so :)

The best way as always to see this new feature in action is via a simple little demo.

To begin, I’ll create a table with a CODE column that is populated with unique values:

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

Table created.

SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000;

1000000 rows created.

I’ll now create a section of data within the table in which we have many repeated values:

SQL> update bowie set code = 42 where id between 250000 and 499999;

250000 rows updated.

SQL> commit;

Commit complete.

So I’ve fabricated the data such that the values in the CODE column are effectively unique within 75% of the table but the other 25% consists of repeated values.

From an index compression perspective, this index really isn’t a good candidate for normal compression as most of the CODE data contains unique data that doesn’t compress. However, it’s a shame that we can’t easily just compress the 25% of the index that would benefit from compression (without using partitioning or some such).

If we create a normal B-Tree index on the CODE column without compression:

SQL> create index bowie_code_i on bowie(code);

Index created.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME           LEAF_BLOCKS COMPRESSION
-------------------- ----------- -------------
BOWIE_CODE_I                2157 DISABLED

 

We notice the index consists of 2157 leaf blocks.

If we now try to use normal compression on the index:

SQL> alter index bowie_code_i rebuild compress;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME           LEAF_BLOCKS COMPRESSION
-------------------- ----------- -------------
BOWIE_CODE_I                2684 ENABLED

 

We notice that the compressed index rather than decrease in size has actually increased in size, up to 2684 leaf blocks. So the index has grown by some 25% due to the fact the index predominately contains unique values which don’t compress at all and the resultant prefix section in the leaf blocks becomes nothing more than additional overhead. The 25% section of the index containing all the repeated values has indeed compressed effectively but these savings are more than offset by the increase in size associated with the other 75% of the index where the index entries had no duplication.

However, if we use the new advanced index compression capability via the COMPRESS ADVANCED LOW clause:

SQL> alter index bowie_code_i rebuild compress advanced low;

Index altered.

SQL> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE';

INDEX_NAME           LEAF_BLOCKS COMPRESSION
-------------------- ----------- -------------
BOWIE_CODE_I                2054 ADVANCED LOW

 

We notice the index has now indeed decreased in size from the original 2157 leaf blocks down to 2054. Oracle has effectively ignored all those leaf blocks where compression wasn’t viable and compressed just the 25% of the index where compression was effective. Obviously, the larger the key values (remembering the rowids associated with the index entries can’t be compressed) and the larger the percentage of repeated data, the larger the overall compression returns.

With Advanced Index Compression, it’s viable to simply set it on for all your B-Tree indexes and Oracle will uniquely compress automatically each individual index leaf block for each and every index as effectively as it can for the life of the index.

12.1.0.2 Introduction to Attribute Clustering (The Division Bell) August 26, 2014

Posted by Richard Foote in 12c, Attribute Clustering, Clustering Factor, Oracle Indexes.
5 comments

One of the really cool new features introduced in 12.1.0.2 is Attribute Clustering. This new table based attribute allows you to very easily cluster data in close physical proximity based on the content of specific columns.

As I’ve discussed many times, indexes love table data that is physically clustered in a similar manner to the index as it can significantly improve the efficiency of such indexes. A low Clustering Factor (CF) makes an index more viable and is one of the more important considerations in CBO calculations.

But not only database indexes benefit from well cluster data. Other index structures such as Exadata Storage Indexes and the new Zone Maps (to be discussed in future articles) all benefit from well clustered data. Additionally, compression is likely to be much more effective with data that is well clustered and this in turns also impacts the efficiency of In-memory data (again, to be discussed in future articles).

So having the capability to now easily cluster data in regular heap tables has potentially many benefits.

To illustrate, I’m first going to create a table with data that is not well clustered at all. The CODE column has data that is basically evenly distributed throughout the whole table structure:

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

Table created.

SQL> insert into ziggy select rownum, mod(rownum,100), 'DAVID BOWIE' from dual connect by level >= 2000000;

2000000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

I’ll next create an index on this CODE column and check out its default CF:

SQL> create index ziggy_code_i on ziggy(code);

Index created.

SQL> select index_name, clustering_factor, num_rows from user_indexes
where index_name='ZIGGY_CODE_I';

INDEX_NAME           CLUSTERING_FACTOR   NUM_ROWS
-------------------- ----------------- ----------
ZIGGY_CODE_I                    703133    2000000

For a table with 2 million rows, a CF of some 703,133 is very high and the index is going to be very inefficient when retrieving high numbers of rows.

Let’s run a query that returns a specific CODE value, approx. 1% of all the data (note I’ve set a large arraysize to minimize unnecessary fetches and resultant consistent  gets):

SQL> set arraysize 5000

SQL> select * from ziggy where code = 42;

20000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2421001569

-----------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       | 20000 |   390K|   383  (17)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| ZIGGY | 20000 |   390K|   383  (17)| 00:00:01 |
-----------------------------------------------------------------------------------

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

1 - storage("CODE"=42)
filter("CODE"=42)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
15212  consistent gets
0  physical reads
0  redo size
211208  bytes sent via SQL*Net to client
585  bytes received via SQL*Net from client
5  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20000  rows processed

The CBO has chosen a Full Table Scan and has decided to not use the index. If we hint the SQL:

SQL> select /*+ index (ziggy, ziggy_code_i) */ * from ziggy where code = 42;

20000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3294205578

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              | 20000 |   390K|  7081   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZIGGY        | 20000 |   390K|  7081   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ZIGGY_CODE_I | 20000 |       |    43   (3)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
7081  consistent gets
41  physical reads
0  redo size
511195  bytes sent via SQL*Net to client
585  bytes received via SQL*Net from client
5  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20000  rows processed

At a cost of 7081, the index is way more expensive than the 383 cost for the FTS. The poor clustering of the CODE data within the table has made the index non viable.

Let’s now create another table, but this one with a clustering attribute set on the CODE column:

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

clustering by linear order (code) without materialized zonemap;

Table created.

The CLUSTERING BY LINEAR ORDER clause orders data in the table based on the specified columns, in this case the CODE column. Up to 10 columns can be included using this particular technique (there are other attribute clustering options which I’ll again cover in later articles, yes I’ll be writing quite a few new articles) :) WITHOUT MATERIALIZED ZONEMAP means I don’t want to create these new Zone Maps index structures at this stage which could potentially reduce the amount of table storage needed to be accessed (again, I’ll discuss these at another time).

You must use a direct path insert to make use of attribute clustering (or reorganize the table as we’ll see).

So lets insert the exact same data into this new ZIGGY2 table via a straight direct path sub-select:

SQL> insert /*+ append */ into ziggy2 select * from ziggy;

2000000 rows created.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0arqdyc9vznpg, child number 0
-------------------------------------
insert /*+ append */ into ziggy2 select * from ziggy

Plan hash value: 1975011999

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes |TempSpc| Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |       |       |       |       | 10596 (100)|          |
|   1 |  LOAD AS SELECT                  |       |       |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |       |  2000K|    38M|       | 10596   (3)| 00:00:01 |
|   3 |    SORT ORDER BY                 |       |  2000K|    38M|    61M| 10596   (3)| 00:00:01 |
|   4 |     TABLE ACCESS STORAGE FULL    | ZIGGY |  2000K|    38M|       |   376  (16)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL> commit;

Commit complete.

Notice the SORT ORDER BY step in the insert execution plan. This implicitly sorts the incoming data in CODE order to satisfy the attribute clustering requirement.

If we create an index on this table and examine the CF:

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

PL/SQL procedure successfully completed.

SQL> select index_name, clustering_factor, num_rows
from user_indexes where index_name='ZIGGY2_CODE_I';

INDEX_NAME           CLUSTERING_FACTOR   NUM_ROWS
-------------------- ----------------- ----------
ZIGGY2_CODE_I                     7072    2000000

We notice the default CF is indeed significantly lower at just 7072 than the previous value of 703133.

If we now run the equivalent query as before on this table:

SQL> select * from ziggy2 where code=42;

20000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 16801974

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               | 20000 |   390K|   114   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZIGGY2        | 20000 |   390K|   114   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ZIGGY2_CODE_I | 20000 |       |    43   (3)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
121  consistent gets
41  physical reads
0  redo size
511195  bytes sent via SQL*Net to client
585  bytes received via SQL*Net from client
5  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20000  rows processed

We notice the CBO has now decided to use the index. This is due to the cost of the index based execution plan being just 114, significantly lower than the previous index cost of 7081 or the FTS at a cost of 383. Just as importantly, the resultant number of consistent gets has also significantly reduced to just 121, significantly less than the previous 7081 consistent gets when using the index. So the index is indeed much more efficient to use and the CBO costs for this is just about spot on. The end result is that performance has improved.

So how to now likewise improve the performance of the first table? Simple add the attribute clustering and reorganize the table:

SQL> alter table ziggy add clustering by linear order(code) without materialized zonemap;

Table altered.

SQL> alter table ziggy move;

Table altered.

SQL> alter index ziggy_code_i rebuild;

Index altered.

SQL> select index_name, clustering_factor, num_rows from user_indexes where index_name='ZIGGY_CODE_I';

INDEX_NAME      CLUSTERING_FACTOR   NUM_ROWS
--------------- ----------------- ----------
ZIGGY_CODE_I                 7134    2000000

So as expected, the CF has likewise reduced. So if we now run the query:

SQL> select * from ziggy where code=42;

20000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3294205578

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              | 20000 |   390K|   115   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZIGGY        | 20000 |   390K|   115   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ZIGGY_CODE_I | 20000 |       |    43   (3)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
121  consistent gets
0  physical reads
0  redo size
511195  bytes sent via SQL*Net to client
585  bytes received via SQL*Net from client
5  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20000  rows processed

The query likewise uses the index and with far less consistent gets and performance is significantly better.

So attribute clustering provides a nice mechanism by which data in a heap table (or importantly within a partition or sub-partition) can be physically clustered in a manner that can be potentially beneficial in various scenarios. Of course, the decision on how to actually cluster the data and on which columns is somewhat crucial :)

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

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

It was 12 years ago today… July 8, 2014

Posted by Richard Foote in Oracle Indexes.
4 comments

It was exactly 12 years ago today that I first presented my Index Internals – Rebuilding The Truth presentation at a local ACT Oracle User Group event.

And so my association with Oracle indexes started. It would be an interesting statistic to know how many people have subsequently read the presentation :) It would no doubt result in a few 50-50 block splits !!

How time flies :)

 

Estimate Index Size With Explain Plan (I Can’t Explain) April 24, 2014

Posted by Richard Foote in Estimate Index Size, Explain Plan For Index, Oracle Indexes.
9 comments

I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes.

Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime ago but was re- reminded of it when I recently came across this entry in Connor McDonald’s excellent blog.

I’ll start by creating a table with a bunch of rows:

SQL> create table ziggy as select o.* from dba_objects o, dba_users;

Table created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> select count(*) from ziggy;

  COUNT(*)
----------
   3939187

I’m thinking of creating an index on the OBJECT_NAME column, but I’m unsure if I’ll have enough free space in my tablespace. So let’s quickly get an estimate of the index size by simply generating the explain plan of the CREATE INDEX statement:

SQL> explain plan for create index ziggy_object_name_i on ziggy(object_name);

Explained.

Elapsed: 00:00:00.09

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1219136602

----------------------------------------------------------------------------------------------

| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                     |  3939K|    93M| 22032   (3)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| ZIGGY_OBJECT_NAME_I |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                     |  3939K|    93M|            |          |
|   3 |    TABLE ACCESS FULL   | ZIGGY               |  3939K|    93M| 17199   (4)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 159M bytes

 

Notice the little note below the execution plan. Oracle has estimated an index size of approximately 159M bytes and it’s only taken it 0.09 seconds to do so. A trace of the session highlights how Oracle simply uses the table statistics in its determination of the estimated index size.

Well, that’s OK I have sufficient space for an index of that size.  Let’s create the physical index and check out its actual size:

SQL> create index ziggy_object_name_i on ziggy(object_name);

Index created.

SQL> select bytes from dba_segments where segment_name='ZIGGY_OBJECT_NAME_I';

     BYTES
----------
 163577856

SQL> analyze index ziggy_object_name_i validate structure;

Index analyzed.

SQL> select btree_space from index_stats;

BTREE_SPACE
-----------
  157875040

 

Not bad at all, the estimate and actual index sizes are pretty well spot on.

There are some limitations however. Let’s pick another column, SUBOBJECT_NAME, which has a large number of NULL values:

SQL> select count(*) from ziggy where subobject_name is not null;

  COUNT(*)
----------
     33669

SQL> explain plan for create index ziggy_subobject_name_i on ziggy(subobject_name);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 4065057084

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                        |  3939K|  7693K| 20132  (4)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| ZIGGY_SUBOBJECT_NAME_I |       |       |     |          |
|   2 |   SORT CREATE INDEX    |                        |  3939K|  7693K|     |          |
|   3 |    TABLE ACCESS FULL   | ZIGGY                  |  3939K|  7693K| 17238  (4)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 100M bytes

The SUBOBJECT_NAME column only has a relatively few (33,669) values that are not null, but the explain plan is still estimating the index to have the full 3.9 million rows (remembering that fully null indexed values are not indexed in a B-Tree index). The estimated index size of 100M is therefore not going to be particularly accurate.

SQL> create index ziggy_subobject_name_i on ziggy(subobject_name);

Index created.

SQL> select bytes from dba_segments where segment_name='ZIGGY_SUBOBJECT_NAME_I';

      
     BYTES
----------
   1048576

SQL> analyze index ziggy_subobject_name_i validate structure;

Index analyzed.

SQL> select btree_space from index_stats;

BTREE_SPACE
-----------
     928032

So in this example, the estimated index size is indeed way off. This method doesn’t seem to cater for null index values and assumes the index to be fully populated.

However, if we simply take the known ratio of  not null values (in this example, 33669 not null rows /3939187 total rows =0.00855) and then apply it to the calculated estimate (100M x .00855 = 0.855M), where are now back into accurate ballpark territory again.

Of course, such estimates are based on the accuracy of the table statistics. If we have stale statistics, we’ll have stale index size estimates.

Let’s insert more rows and double the size of the table and associated index: 

SQL> insert into ziggy select * from ziggy;

3939187 rows created.

SQL> commit;

Commit complete.

If we re-run the index creation explain plan:

SQL> explain plan for create index ziggy_object_name_i on ziggy(object_name);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 746589531

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                     |  3939K|    93M| 22032   (3)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| ZIGGY_OBJECT_NAME_I |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                     |  3939K|    93M|            |          |
|   3 |    INDEX FAST FULL SCAN| ZIGGY_OBJECT_NAME_I |       |       |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 159M bytes

We get the same estimate as before. We need to update the table statistics in order to get an updated and more accurate index size estimate:

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> explain plan for create index ziggy_object_name_i on ziggy(object_name);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 746589531

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |                     |  7878K|   187M| 45811   (3)| 00:00:02 |
|   1 |  INDEX BUILD NON UNIQUE| ZIGGY_OBJECT_NAME_I |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                     |  7878K|   187M|            |          |
|   3 |    INDEX FAST FULL SCAN| ZIGGY_OBJECT_NAME_I |       |       |            |          |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
Note
-----
   - estimated index size: 318M bytes

Both the estimated index entries and  index size are now much more accurate.

The number of expected index entries is therefore a useful guide as to the potential accuracy of the size estimate.

So the next time you’re wondering whether an index is significantly larger than it should or whether you have sufficient space for a new index, this is a useful, simple technique to get a quick estimate.

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: 0x00.36bc54  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1808058 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0036bc54
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0x0
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: 0x00.36bc78  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x181b720 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0036bc78
Leaf block dump
===============
header address 32801380=0x1f48264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0x0
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: 0x00.36bc54  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1808058 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0036bc54
Leaf block dump
===============
header address 402948708=0x18048264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 959=0x3bf
kdxcoavs 919
kdxlespl 0
kdxlende 0
kdxlenxt 25198685=0x180805d
kdxleprv 0=0x0
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: 0x00.36bc78  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x181b720 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0x0008.015.00000b86  0x014316ab.01c5.42  —-    1  fsc 0x0000.00000000
Leaf block dump
===============
header address 402948708=0x18048264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 514
kdxcofbo 1064=0x428
kdxcofeo 1868=0x74c
kdxcoavs 804
kdxlespl 0
kdxlende 0
kdxlenxt 25278245=0x181b725
kdxleprv 0=0x0
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;

 

 

 

 

 

 

Indexing Foreign Key Constraints With Bitmap Indexes (Locked Out) April 17, 2014

Posted by Richard Foote in Bitmap Indexes, Block Dumps, Foreign Keys, Index Internals, Oracle Indexes.
6 comments

Franck Pachot made a very valid comment in my previous entry on Indexing Foreign Keys (FK) that the use of a Bitmap Index on the FK columns does not avoid the table locks associated with deleting rows from the parent table. Thought I might discuss why this is the case and why only a B-Tree index does the trick.

Let’s first setup some very simple Parent-Child tables:

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.

OK, so we have a small parent table (BOWIE_DAD) and a much larger child table (BOWIE_KID) with all the necessary constraints in place. Note we don’t actually have a child row with a  FK DAD_ID = 1. So we can potentially delete this row from the BOWIE_DAD table (where ID = 1).

Let’s begin by creating a B-Tree  index on the FK column (DAD_ID) and have a look a partial block dump of the first leaf block in the index:

SQL> create index bowie_kid_fk_i on bowie_kid(dad_id);

Index created.

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0x00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0035f861
Leaf block dump
===============
header address 360809060=0x15818264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0x0
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 52 73 00 00
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 06
…..

 

We’ll compare future block dumps with this one but for now just note that the first index entry has a value of (hex) C1 03, which corresponds to the minimum value for DAD_ID = 2 we currently have in this table/index.

If we insert a new child record in one session (but not yet commit);

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

1 row created.

In a second session, we can delete (but not yet commit) the unwanted parent row without any locking implications thanks to this index on the FK column:

SQL> delete bowie_dad where id = 1;

1 row deleted.

In a third session, we can insert another child record again with no locking implications, providing we don’t attempt to use the parent value the second session is in the process of deleting:

SQL> insert into bowie_kid values (1000002, 'LOW', 3);

1 row created.

But if we do try to insert a new child row with a FK value for which the parent is in the process of being deleted:

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

The statement hangs and it will do so until the transaction deleting the parent record commits (in which case it will receive an ORA-02291 integrity constraint error) or the transaction rolls back (in which case the insert will succeed).

If we take a fresh dump of the first leaf block (which must contain the associated index entry as it’s the minimum value now in the table):

 Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0x00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0x0008.004.00000b8a  0x01431602.01c5.14  —-    1  fsc 0x0000.00000000
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 514
kdxcofbo 1064=0x428
kdxcofeo 1868=0x74c
kdxcoavs 804
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0x0
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 80 7f 38 00 00
row#1[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
row#2[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03

 

We notice we indeed do have a new index entry (highlighted above), with all the associated locking information in ITL slot 2 for the new row in which the session is locked. So the key point here is that the index is indeed updated and Oracle can proceed or not depending on what happens with the transaction on the parent table. The overhead of this new index entry is minimal and locking can be easily policed and restricted to just the index entries with this specific value (hex) C1 02 which corresponds to DAD_ID = 1.

If we do indeed proceed with the delete on the parent table:

SQL> commit;

Commit complete.

 

The session attempting to insert the now deleted parent FK value indeed fails:

 

SQL> insert into bowie_kid values (1000002, 'HEROES', 1);
insert into bowie_kid values (1000002, 'HEROES', 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (BOWIE.BOWIE_KID_FK) violated - parent key not
found

 

And we notice with a fresh block dump that the index entry has been removed by the now unlocked session:

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0x00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.0035f861
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0x0
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 52 73 00 00
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 06

Everything is back to the way it was previously.

 

OK, let’s now re-insert the parent row, drop the FK index and replace it with a Bitmap Index instead:

 

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

1 row created.

SQL> commit;

Commit complete.

SQL> drop index bowie_kid_fk_i;

Index dropped.

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

Index created.

 

If we take a look at a partial block dump of the first leaf block of this Bitmap Index:

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f14  csc: 0x00.3602fc  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.003602fc
Leaf block dump
===============
header address 360809060=0x15818264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 958=0x3be
kdxcoavs 918
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[4498] flag: ——-, lock: 0, len=3538
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
col 2; len 6; (6):  01 80 6e cc 00 3f
col 3; len 3517; (3517):
 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32 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 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 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32
 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 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24
 01 ff 32 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

….

 

We notice the first key difference here in that these Bitmap Index entries are potentially HUGE, with just the 2 index entries in this block. The other thing to note is the combination of Bitmap indexes and DMLs can result in locking hell because if an index entry needs to be modified (resulting in a change in the compressed bitmap string), all rows between the rowid ranges specified within the Bitmap Index entry are effectively locked. So Bitmap Indexes introduce severe locking issues, regardless of the Parent/Child update issue highlighted above.

If we insert a child row in one session:

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

1 row created.

And in another session insert another row with the same FK value:

SQL> insert into bowie_kid values (1000002, 'HEROES', 4);

The session hangs until the transaction in the first session completes because of the locking implications introduced with the Bitmap Index.

 

Therefore, with a Bitmap Index in place, the last of our worries will be locking issues associated with deleting a parent row. After rolling back the above, we attempt the following. In one session, we insert a child record:

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

1 row created.

In a second session, we delete the unwanted parent row:

SQL> delete bowie_dad where id = 1;

and it hangs. The Bitmap Index is not effective in preventing this lock as it was with the B-Tree Index.

In a third session, we attempt to insert a child row with the soon to be deleted parent key:

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

and it hangs as well. So the Bitmap Index on the FK does not prevent the locking hell such parent deletes can introduce into our environments.

If we roll all this back and simply have one session delete a parent row:

SQL> delete bowie_dad where id = 1;

1 row deleted.

And in another session insert a child row with the FK about to be deleted, the insert hangs as expected with an exclusive transaction lock:

SQL> insert into bowie_kid values (1000001, 'BOWIE', 1);

 

However, if we look at a fresh partial block dump of the first Bitmap Index leaf block:

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f14  csc: 0x00.3602fc  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.003602fc
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 958=0x3be
kdxcoavs 918
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[4498] flag: ——-, lock: 0, len=3538
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
col 2; len 6; (6):  01 80 6e cc 00 3f
col 3; len 3517; (3517):
 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32 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 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 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32
 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 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 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24
 01 ff 32 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

…..

 

Unlike the B-Tree index which was updated, the Bitmap index has remained unchanged. No attempt was made by Oracle at this stage to insert the index entry as such a new Bitmap Index entry would likely generate too much overheads and not appreciably reduce the locking implications of these DML statements with these Bitmap Indexes in place anyways. The actual index update is delayed until such as change is possible with the rollback of the parent deletion.

However, in a third session, an insert into the child table with a FK that’s not to be deleted is successful:

SQL> insert into bowie_kid values (1000002, 'BOWIE', 4);

1 row created.

Bitmap indexes are simply not designed with concurrency in mind and have efficiencies that make it easier for single sessions to load data in Data Warehouses environments where they are indeed suitable.

One advantage of the Bitmap index is that at least Oracle doesn’t have to perform a FTS on the (potentially huge) child table when checking for the existence of any associated child FK values. Oracle can quickly use the index to determine whether the parent delete can proceed or not. If we roll everything back and just attempt to delete a parent row:

SQL> delete bowie_dad where id = 1;

1 row deleted.

       
Execution Plan
----------------------------------------------------------
Plan hash value: 2571176721

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | BOWIE_DAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010356 |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access('ID'=1)

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

 

We notice at just 3 consistent gets, the potentially expensive FTS on the child table has been avoided. Drop the Bitmap index and the FTS must be performed to ensure no current FK values would violate the constraint when the parent row is deleted:

SQL> drop index bowie_kid_fk_i;

Index dropped.

   
SQL> delete bowie_dad where id = 1;

1 row deleted.

    
Execution Plan
----------------------------------------------------------
Plan hash value: 2571176721

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | BOWIE_DAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010356 |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access('ID'=1)

    
Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
       3629  consistent gets
          0  physical reads
        676  redo size
        863  bytes sent via SQL*Net to client
        830  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

We notice without the Bitmap Index in place, we are now performing many more (3629) consistent gets due to the necessary FTS.

So using a Bitmap Index to police a FK constraint doesn’t reduce the locking implications associated with deleting parent rows (with Bitmap indexes, we have locking hell regardless if there’s much DML) but it does at least reduce the overheads of checking the associated child table.

Follow

Get every new post delivered to your Inbox.

Join 2,042 other followers