Quiz Time. Why Do Deletes Cause An Index To Grow ? (Solution) June 29, 2015
Posted by Richard Foote in Oracle Indexes.add a comment
OK, time to reveal how a couple of simple deletes can cause an index to double in size.
If we go back and look at the tree dump before the delete operation:
—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 19, level: 1)
leaf: 0x180050c 25167116 (-1: row:540.540 avs:4)
leaf: 0x180050d 25167117 (0: row:533.533 avs:10)
leaf: 0x180050e 25167118 (1: row:533.533 avs:11)
leaf: 0x180050f 25167119 (2: row:533.533 avs:10)
leaf: 0x1800510 25167120 (3: row:533.533 avs:10)
leaf: 0x1800511 25167121 (4: row:533.533 avs:11)
leaf: 0x1800512 25167122 (5: row:533.533 avs:10)
leaf: 0x1800513 25167123 (6: row:533.533 avs:10)
leaf: 0x1800514 25167124 (7: row:533.533 avs:11)
leaf: 0x1800515 25167125 (8: row:533.533 avs:10)
leaf: 0x1800516 25167126 (9: row:533.533 avs:10)
leaf: 0x1800517 25167127 (10: row:533.533 avs:11)
leaf: 0x1800519 25167129 (11: row:533.533 avs:10)
leaf: 0x180051a 25167130 (12: row:533.533 avs:10)
leaf: 0x180051b 25167131 (13: row:533.533 avs:11)
leaf: 0x180051c 25167132 (14: row:533.533 avs:10)
leaf: 0x180051d 25167133 (15: row:533.533 avs:10)
leaf: 0x180051e 25167134 (16: row:533.533 avs:11)
leaf: 0x180051f 25167135 (17: row:399.399 avs:2019)
—– end tree dump
We notice there’s only 10 or 11 free bytes available (the avs) in most of the leaf blocks as we created the index with a pctfree of 0.
If we look at a partial block dump of the first leaf block after the deletes:
Block header dump: 0x0180050c Object id on Block? Y seg/obj: 0x18244 csc: 0x00.4b9940 itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x1800508 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.000.0000097f 0x0140010a.0186.01 -BU- 1 fsc 0x0000.004b9956 0x02 0x0005.001.00000a03 0x01400ba7.0156.28 ---- 0 fsc 0x0000.00000000 Leaf block dump =============== header address 37170788=0x2372e64 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 1 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 1 kdxconro 278 kdxcofbo 592=0x250 kdxcofeo 4523=0x11ab kdxcoavs 3931 kdxlespl 0 kdxlende 0 kdxlenxt 25167140=0x1800524 kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8036 row#0[4523] flag: -------, lock: 0, len=12 col 0; len 2; (2): c1 02 col 1; len 6; (6): 01 80 04 e7 00 00 row#1[4535] flag: -------, lock: 0, len=12 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 80 04 e7 00 01 .....
The key thing to note here is that the leaf block has two Interested Transaction List (ITL) slots, each of which use 24 bytes. Two is the default number of ITL slots in an index leaf block (index branch blocks only have 1 by default) and are used by transactions to store vital information such as transaction id, locking information, location of undo and SCN details. However, the first slot (No. 1) is only used by recursive transactions such as those required to perform index block splits and can’t be used for standard user-based transactions. I discuss this in my (in)famous Rebuilding The Truth presentation.
Now my quiz demo had two concurrent delete transactions occurring within the same leaf block(s) but with effectively just the one free ITL slot available for the two transactions. Ordinarily, Oracle would just allocate another ITL slot so both transactions can both concurrently delete the different index entries within the same leaf block. However, Oracle is unable to simply add another ITL slot in this scenario as it requires 24 bytes of free space and there is only the 10 or 11 bytes free in our leaf blocks.
In a similar scenario with a table segment, being unable to allocate another ITL slot like this would result in a nasty ITL wait event for one of the transactions. But for indexes, there is a “naturally occurring” event that results in plenty of additional free space as required.
The index block split.
So rather than have one transaction having to hang and wait for a ITL slot to become available (i.e. for the other transaction to complete), Oracle simply performs a 50-50 block split and allocates the additional ITL slot as necessary, if both transactions still occur within the same leaf block after the block split.
In my quiz demo, both delete transactions were actually performed on index entries that existed in the other half of the block split. Therefore, the number of ITL slots in the first leaf block remains at just the default two and the kdxlende value which denotes deleted index entries remains at 0.
If we look at a partial block dump of the other half of the leaf block split, now the second logical leaf block (as identified by kdxlenxt 25167140=0x1800524):
Block header dump: 0x01800524 Object id on Block? Y seg/obj: 0x18244 csc: 0x00.4b9956 itc: 3 flg: E typ: 2 - INDEX brn: 1 bdba: 0x1800518 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.000.0000097f 0x0140010c.0186.01 CB-- 0 scn 0x0000.004b9956 0x02 0x0005.001.00000a03 0x01400ba7.0156.28 ---- 1 fsc 0x000f.00000000 0x03 0x0003.01b.00000944 0x01400dfd.01f1.19 --U- 1 fsc 0x000e.004b9970 Leaf block dump =============== header address 37170812=0x2372e7c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 1 kdxconro 262 kdxcofbo 560=0x230 kdxcofeo 4609=0x1201 kdxcoavs 4049 kdxlespl 0 kdxlende 2 kdxlenxt 25167117=0x180050d kdxleprv 25167116=0x180050c kdxledsz 0 kdxlebksz 8012 row#0[4609] flag: -------, lock: 0, len=13 col 0; len 3; (3): c2 03 50 col 1; len 6; (6): 01 80 04 e7 01 16 row#1[4622] flag: -------, lock: 0, len=13 col 0; len 3; (3): c2 03 51 col 1; len 6; (6): 01 80 04 e7 01 17 .... row#219[7454] flag: -------, lock: 0, len=13 col 0; len 3; (3): c2 05 63 col 1; len 6; (6): 01 80 04 e3 00 93 row#220[7467] flag: ---DS--, lock: 2, len=13 col 0; len 3; (3): c2 05 64 col 1; len 6; (6): 01 80 04 e3 00 94 row#221[7480] flag: ---D---, lock: 3, len=12 col 0; len 2; (2): c2 06 col 1; len 6; (6): 01 80 04 e3 00 95 ....
The first thing we notice is that this leaf block as three, not the default two ITL slots. As both concurrent delete transactions deleted entries from this particular leaf block, an additional ITL slot was allocated as we now have plenty of free space.
The kdxlende value is set to 2 as we now have the two index entries marked as deleted (these are index entries 220 and 221 within the block). Index entry 220 was deleted by the transaction logged in ITL slot 2 and index entry 221 was deleted by the transaction logged in the new ITL slot 3.
So having two concurrent transactions wanting to delete from the same “full” leaf block resulted in the leaf block performing a 50-50 block split with a new leaf block being added to the index in order to accommodate the additional required ITL slot.
I was very careful when deleting rows from the table to cause maximum “damage” to the index. Both delete transactions in my demo effectively deleted every 500th pair of index entries. As there was previously approx. 533 index entries per leaf block, this resulted in every leaf block in the index splitting in this exact manner as all the leaf blocks had two index entries deleted. This is why the deletes resulted in the index practically doubling in size.
The only index leaf block that didn’t have to split was the very last leaf block as it had plenty of free space (2019 bytes) to accommodate the additional ITL slot. This last leaf block only had 1995 bytes of free space after the deleted, as it lost the 24 bytes due to the additional ITL slot being allocated. You can see these numbers in the tree dumps (following is the tree dump after the delete operations):
—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 37, level: 1)
leaf: 0x180050c 25167116 (-1: row:278.278 avs:3931)
leaf: 0x1800524 25167140 (0: row:262.260 avs:4049)
leaf: 0x180050d 25167117 (1: row:271.271 avs:3938)
leaf: 0x1800525 25167141 (2: row:262.260 avs:4048)
leaf: 0x180050e 25167118 (3: row:271.271 avs:3938)
leaf: 0x1800526 25167142 (4: row:262.260 avs:4049)
leaf: 0x180050f 25167119 (5: row:271.271 avs:3937)
leaf: 0x1800527 25167143 (6: row:262.260 avs:4049)
leaf: 0x1800510 25167120 (7: row:271.271 avs:3938)
leaf: 0x1800520 25167136 (8: row:262.260 avs:4048)
leaf: 0x1800511 25167121 (9: row:271.271 avs:3938)
leaf: 0x1800521 25167137 (10: row:262.260 avs:4049)
leaf: 0x1800512 25167122 (11: row:271.271 avs:3937)
leaf: 0x1800522 25167138 (12: row:262.260 avs:4049)
leaf: 0x1800513 25167123 (13: row:271.269 avs:3914)
leaf: 0x1800523 25167139 (14: row:262.262 avs:4072)
leaf: 0x1800514 25167124 (15: row:271.269 avs:3914)
leaf: 0x1800529 25167145 (16: row:262.262 avs:4073)
leaf: 0x1800515 25167125 (17: row:271.269 avs:3913)
leaf: 0x180052a 25167146 (18: row:262.262 avs:4073)
leaf: 0x1800516 25167126 (19: row:271.269 avs:3914)
leaf: 0x180052e 25167150 (20: row:262.262 avs:4072)
leaf: 0x1800517 25167127 (21: row:271.269 avs:3914)
leaf: 0x180052f 25167151 (22: row:262.262 avs:4073)
leaf: 0x1800519 25167129 (23: row:271.269 avs:3913)
leaf: 0x180052b 25167147 (24: row:262.262 avs:4073)
leaf: 0x180051a 25167130 (25: row:271.269 avs:3914)
leaf: 0x180052c 25167148 (26: row:262.262 avs:4072)
leaf: 0x180051b 25167131 (27: row:271.269 avs:3914)
leaf: 0x180052d 25167149 (28: row:262.260 avs:4049)
leaf: 0x180051c 25167132 (29: row:271.271 avs:3937)
leaf: 0x1800534 25167156 (30: row:262.260 avs:4049)
leaf: 0x180051d 25167133 (31: row:264.264 avs:4042)
leaf: 0x1800535 25167157 (32: row:269.267 avs:3944)
leaf: 0x180051e 25167134 (33: row:271.271 avs:3938)
leaf: 0x1800536 25167158 (34: row:262.260 avs:4049)
leaf: 0x180051f 25167135 (35: row:399.397 avs:1995)
—– end tree dump
Be very careful allocating a pctfree of 0 to indexes as it may not ultimately help in keeping the indexes as compact as you might have hoped, even if you don’t insert new index entries into the existing full portions of the index.
Thanks to all of those that had a go at the quiz and well done to those that got it right 🙂
Quiz Time. Why Do Deletes Cause An Index To Grow ? (Up The Hill Backwards) June 25, 2015
Posted by Richard Foote in Oracle Indexes.14 comments
OK, time for a little quiz.
One of the things I’ve seen at a number of sites is the almost fanatical drive to make indexes as small as possible because indexes that are larger than necessary both waste storage and hurt performance.
Or so the theory goes … 🙂
In many cases, this drives DBAs to create or rebuild indexes with a PCTFREE set to 0 as this will make the index as compact and small as possible.
Of course, this is often the very worst setting for an index to remain small because the insert of a new index entry is likely to cause a 50-50 block split and result in two 1/2 empty leaf blocks (unless the index entry is the maximum current value). Before very long, the index is back to a bloated state and in some sad scenarios, the process is repeated again and again.
A point that is often missed though is that it doesn’t even take an insert to cause the index to expand out. A few delete statements is all that’s required.
To illustrate I create my favorite little table and populate it with a few rows:
SQL> create table bowie (id number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete.
OK, time to create an index but because I’m paranoid about having indexes larger than necessary, I’ll create it with a pctfree of 0:
SQL> create index bowie_id_i on bowie(id) pctfree 0; Index created.
If we look at a tree dump of the index:
SQL> select object_id from dba_objects where object_name='BOWIE_ID_I'; OBJECT_ID ---------- 98884 SQL> alter session set events 'immediate trace name treedump level 98884'; Session altered.
—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 19, level: 1)
leaf: 0x180050c 25167116 (-1: row:540.540 avs:4)
leaf: 0x180050d 25167117 (0: row:533.533 avs:10)
leaf: 0x180050e 25167118 (1: row:533.533 avs:11)
leaf: 0x180050f 25167119 (2: row:533.533 avs:10)
leaf: 0x1800510 25167120 (3: row:533.533 avs:10)
leaf: 0x1800511 25167121 (4: row:533.533 avs:11)
leaf: 0x1800512 25167122 (5: row:533.533 avs:10)
leaf: 0x1800513 25167123 (6: row:533.533 avs:10)
leaf: 0x1800514 25167124 (7: row:533.533 avs:11)
leaf: 0x1800515 25167125 (8: row:533.533 avs:10)
leaf: 0x1800516 25167126 (9: row:533.533 avs:10)
leaf: 0x1800517 25167127 (10: row:533.533 avs:11)
leaf: 0x1800519 25167129 (11: row:533.533 avs:10)
leaf: 0x180051a 25167130 (12: row:533.533 avs:10)
leaf: 0x180051b 25167131 (13: row:533.533 avs:11)
leaf: 0x180051c 25167132 (14: row:533.533 avs:10)
leaf: 0x180051d 25167133 (15: row:533.533 avs:10)
leaf: 0x180051e 25167134 (16: row:533.533 avs:11)
leaf: 0x180051f 25167135 (17: row:399.399 avs:2019)
—– end tree dump
We note the index only has 19 leaf blocks and that most leaf blocks have 533 index entries and only an avs (available free space) of some 10 or 11 bytes. Only the last leaf block is partly full with some 2019 free bytes.
That’s fantastic, the index really is a small as can be. Trying to use index compression will be futile as the indexed values are effectively unique.
I’m now going to delete just a few rows. Surely deleting rows from the table (and hence entries from the index) can only have a positive impact (if any) on the index structure.
In one session, I delete some 20 odd rows:
SQL> delete bowie where id in (select rownum*499 from dual connect by level<=20); 20 rows deleted.
Meanwhile, in another session, I delete another 20 different rows:
SQL> delete bowie where id in (select rownum*500 from dual connect by level<=20); 20 rows deleted.
I then commit the delete statement in both sessions.
So in total, I’ve just deleted some 40 rows in total out of the 10000 rows in the table.
If I look at a tree dump of the index now:
—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 37, level: 1)
leaf: 0x180050c 25167116 (-1: row:278.278 avs:3931)
leaf: 0x1800524 25167140 (0: row:262.260 avs:4049)
leaf: 0x180050d 25167117 (1: row:271.271 avs:3938)
leaf: 0x1800525 25167141 (2: row:262.260 avs:4048)
leaf: 0x180050e 25167118 (3: row:271.271 avs:3938)
leaf: 0x1800526 25167142 (4: row:262.260 avs:4049)
leaf: 0x180050f 25167119 (5: row:271.271 avs:3937)
leaf: 0x1800527 25167143 (6: row:262.260 avs:4049)
leaf: 0x1800510 25167120 (7: row:271.271 avs:3938)
leaf: 0x1800520 25167136 (8: row:262.260 avs:4048)
leaf: 0x1800511 25167121 (9: row:271.271 avs:3938)
leaf: 0x1800521 25167137 (10: row:262.260 avs:4049)
leaf: 0x1800512 25167122 (11: row:271.271 avs:3937)
leaf: 0x1800522 25167138 (12: row:262.260 avs:4049)
leaf: 0x1800513 25167123 (13: row:271.269 avs:3914)
leaf: 0x1800523 25167139 (14: row:262.262 avs:4072)
leaf: 0x1800514 25167124 (15: row:271.269 avs:3914)
leaf: 0x1800529 25167145 (16: row:262.262 avs:4073)
leaf: 0x1800515 25167125 (17: row:271.269 avs:3913)
leaf: 0x180052a 25167146 (18: row:262.262 avs:4073)
leaf: 0x1800516 25167126 (19: row:271.269 avs:3914)
leaf: 0x180052e 25167150 (20: row:262.262 avs:4072)
leaf: 0x1800517 25167127 (21: row:271.269 avs:3914)
leaf: 0x180052f 25167151 (22: row:262.262 avs:4073)
leaf: 0x1800519 25167129 (23: row:271.269 avs:3913)
leaf: 0x180052b 25167147 (24: row:262.262 avs:4073)
leaf: 0x180051a 25167130 (25: row:271.269 avs:3914)
leaf: 0x180052c 25167148 (26: row:262.262 avs:4072)
leaf: 0x180051b 25167131 (27: row:271.269 avs:3914)
leaf: 0x180052d 25167149 (28: row:262.260 avs:4049)
leaf: 0x180051c 25167132 (29: row:271.271 avs:3937)
leaf: 0x1800534 25167156 (30: row:262.260 avs:4049)
leaf: 0x180051d 25167133 (31: row:264.264 avs:4042)
leaf: 0x1800535 25167157 (32: row:269.267 avs:3944)
leaf: 0x180051e 25167134 (33: row:271.271 avs:3938)
leaf: 0x1800536 25167158 (34: row:262.260 avs:4049)
leaf: 0x180051f 25167135 (35: row:399.397 avs:1995)
—– end tree dump
I notice the index has basically doubled in size (37 leaf blocks up from the original 19), with most index leaf blocks now 1/2 empty.
So the key question. How could just deleting a handful of rows result in my beautifully compact index bloating to double the size ???
I’ve given away one clue within the post. I’ll post the answer in the coming days 🙂
Empty Leaf Blocks After Rollback Part II (Editions of You) June 24, 2015
Posted by Richard Foote in Block Dumps, Index Internals, Oracle Indexes, Tree Dumps, Unique Indexes, Update Indexes.5 comments
In my last post, I discussed how both 1/2 empty and totally empty leaf blocks can be generated by rolling back a bulk update operation.
An important point I made within the comments of the previous post is that almost the exact scenario would have taken place had the transaction committed rather than rolled back. A commit would also have resulted with the leaf blocks being 1/2 empty in the first example (with the previous index entries now all marked as deleted) and with effectively empty leaf blocks in the second example (with the previous leaf blocks all now containing index entries marked as deleted). The important aspect here is not the rollback but the fact that update statements result in the deletion of the previous indexed value and the re-insertion of the new value. (BTW, it’s always a useful exercise to read through the comments on this blog as this is often where some of the best learning takes place due to some of the really nice discussions) 🙂
That said, the previous post used a Non-Unique index. Let’s now repeat the same scenario but this time use a Unique Index instead.
So let’s start with another table with the same data but this time with a unique index on the ID column:
SQL> create table ziggy (id number, name varchar2(42)); Table created. SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create unique index ziggy_id_i on ziggy(id); Index created.
OK, let’s have a look at a tree dump of this index:
—– begin tree dump
branch: 0x180017b 25166203 (0: nrow: 20, level: 1)
leaf: 0x180017c 25166204 (-1: row:520.520 avs:824)
leaf: 0x180017d 25166205 (0: row:513.513 avs:823)
leaf: 0x180017e 25166206 (1: row:513.513 avs:823)
leaf: 0x180017f 25166207 (2: row:513.513 avs:823)
leaf: 0x18004c8 25167048 (3: row:513.513 avs:823)
leaf: 0x18004c9 25167049 (4: row:513.513 avs:823)
leaf: 0x18004ca 25167050 (5: row:513.513 avs:823)
leaf: 0x18004cb 25167051 (6: row:513.513 avs:824)
leaf: 0x18004cc 25167052 (7: row:513.513 avs:823)
leaf: 0x18004cd 25167053 (8: row:513.513 avs:823)
leaf: 0x18004ce 25167054 (9: row:513.513 avs:823)
leaf: 0x18004cf 25167055 (10: row:513.513 avs:823)
leaf: 0x18004d1 25167057 (11: row:513.513 avs:823)
leaf: 0x18004d2 25167058 (12: row:513.513 avs:823)
leaf: 0x18004d3 25167059 (13: row:513.513 avs:824)
leaf: 0x18004d4 25167060 (14: row:513.513 avs:823)
leaf: 0x18004d5 25167061 (15: row:513.513 avs:823)
leaf: 0x18004d6 25167062 (16: row:513.513 avs:823)
leaf: 0x18004d7 25167063 (17: row:513.513 avs:823)
leaf: 0x18004d8 25167064 (18: row:246.246 avs:4559)
—– end tree dump
Now we notice a bit of a difference already. Here, the index consists of 20 leaf blocks with 513 index entries in most leaf blocks whereas the non-unique index had 21 leaf blocks and just 479 index entries per leaf block. One of the advantages of unique indexes over non-unique as I’ve discussed previously.
Let’s now perform our first bulk update where I increment the ID of each value by 1:
SQL> update ziggy set id=id+1; 10000 rows updated. SQL> rollback; Rollback complete.
Now with the non-unique index, this resulted in the index doubling in size as we created an additional index entry for each and every row. After the rollback, we were effectively left with an index that not only was twice the size but had only 1/2 empty leaf blocks.
With a unique index though, things differ. The most important characteristic of a unique index of course is that each index value can only ever exist once, each index entry must be unique. So for a unique index, the rowid is not actually part of the indexed column list, but treated as additional “overhead” or metadata associated with the index entry.
When we perform our update here, we’re effectively replicating each value, except for the very last ID value where 10001 doesn’t exist. But with the first row, when the ID=1 becomes 2 after the update, we already have an index entry with an ID value of 2 (the second row). So Oracle can mark the first index entry as deleted (as ID=1 no longer exists) but rather than insert a new index entry simply update the rowid associated with the unique index entry with the ID of 2. Oracle then updates the rowid of the index entry with a value of 3 with the rowid of that previously referenced ID=2 . And so on and so on for all the other index entries except for index value 100001 which has to be inserted as it didn’t previously exist. So Oracle nicely maintains the consistency of the index during the single update operation by effectively recycling the existing index entries.
The net result is that the index remains the same size as the index entries are not reinserted as they are for a non-unique index. The effective change that occurs during this update is that the first index entry is marked as deleted and one new index entry is added at the very end.
If we look at a partial block dump of the first leaf block before the rollback operation:
Leaf block dump
===============
header address 375991908=0x16692e64
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 520
kdxcofbo 1076=0x434
kdxcofeo 1900=0x76c
kdxcoavs 824
kdxlespl 0
kdxlende 1
kdxlenxt 25166205=0x180017d
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8036
row#0[8025] flag: —D—, lock: 2, len=11, data:(6): 01 80 01 57 00 00
col 0; len 2; (2): c1 02
row#1[8014] flag: ——-, lock: 2, len=11, data:(6): 01 80 01 57 00 00
col 0; len 2; (2): c1 03
row#2[8003] flag: ——-, lock: 2, len=11, data:(6): 01 80 01 57 00 01
col 0; len 2; (2): c1 04
row#3[7992] flag: ——-, lock: 2, len=11, data:(6): 01 80 01 57 00 02
col 0; len 2; (2): c1 05
row#4[7981] flag: ——-, lock: 2, len=11, data:(6): 01 80 01 57 00 03
col 0; len 2; (2): c1 06
row#5[7970] flag: ——-, lock: 2, len=11, data:(6): 01 80 01 57 00 04
col 0; len 2; (2): c1 07
We notice that the first index entry is marked as deleted (as we now no longer have an ID=1) but all the other index entries have been “recycled” with their updated rowids. Note how the rowid of the deleted index entry (01 80 01 57 00 00) is now associated with the second index entry (which is effectively now the first index entry now).
If we look at a tree dump after the rollback was performed:
—– begin tree dump
branch: 0x180017b 25166203 (0: nrow: 20, level: 1)
leaf: 0x180017c 25166204 (-1: row:520.520 avs:824)
leaf: 0x180017d 25166205 (0: row:513.513 avs:823)
leaf: 0x180017e 25166206 (1: row:513.513 avs:823)
leaf: 0x180017f 25166207 (2: row:513.513 avs:823)
leaf: 0x18004c8 25167048 (3: row:513.513 avs:823)
leaf: 0x18004c9 25167049 (4: row:513.513 avs:823)
leaf: 0x18004ca 25167050 (5: row:513.513 avs:823)
leaf: 0x18004cb 25167051 (6: row:513.513 avs:824)
leaf: 0x18004cc 25167052 (7: row:513.513 avs:823)
leaf: 0x18004cd 25167053 (8: row:513.513 avs:823)
leaf: 0x18004ce 25167054 (9: row:513.513 avs:823)
leaf: 0x18004cf 25167055 (10: row:513.513 avs:823)
leaf: 0x18004d1 25167057 (11: row:513.513 avs:823)
leaf: 0x18004d2 25167058 (12: row:513.513 avs:823)
leaf: 0x18004d3 25167059 (13: row:513.513 avs:824)
leaf: 0x18004d4 25167060 (14: row:513.513 avs:823)
leaf: 0x18004d5 25167061 (15: row:513.513 avs:823)
leaf: 0x18004d6 25167062 (16: row:513.513 avs:823)
leaf: 0x18004d7 25167063 (17: row:513.513 avs:823)
leaf: 0x18004d8 25167064 (18: row:246.246 avs:4559)
—– end tree dump
We notice that it’s exactly the same size as before and we don’t have the same issues with a bloated index as we did in the previous non-unique index example.
However, if we perform the second update which effectively changes all the ID values to those which don’t currently exist within the table:
SQL> update ziggy set id=id+10000; 10000 rows updated. SQL> rollback; Rollback complete.
Now Oracle can’t recycle the existing index entries as the new values don’t currently exist within the index. So Oracle is indeed forced to mark all the existing index entries as deleted and insert new index entries into the index. These new index entries all exist in the right hand most side of the index, resulting in 90-10 block splits with additional index leaf blocks being added to the index. If we rollback this transaction, it will result in all the new index entries being removed, leaving behind these new empty leaf blocks just as with the non-unique index example.
A new tree dump will confirm this:
—– begin tree dump
branch: 0x180017b 25166203 (0: nrow: 47, level: 1)
leaf: 0x180017c 25166204 (-1: row:520.520 avs:824)
leaf: 0x180017d 25166205 (0: row:513.513 avs:823)
leaf: 0x180017e 25166206 (1: row:513.513 avs:823)
leaf: 0x180017f 25166207 (2: row:513.513 avs:823)
leaf: 0x18004c8 25167048 (3: row:513.513 avs:823)
leaf: 0x18004c9 25167049 (4: row:513.513 avs:823)
leaf: 0x18004ca 25167050 (5: row:513.513 avs:823)
leaf: 0x18004cb 25167051 (6: row:513.513 avs:824)
leaf: 0x18004cc 25167052 (7: row:513.513 avs:823)
leaf: 0x18004cd 25167053 (8: row:513.513 avs:823)
leaf: 0x18004ce 25167054 (9: row:513.513 avs:823)
leaf: 0x18004cf 25167055 (10: row:513.513 avs:823)
leaf: 0x18004d1 25167057 (11: row:513.513 avs:823)
leaf: 0x18004d2 25167058 (12: row:513.513 avs:823)
leaf: 0x18004d3 25167059 (13: row:513.513 avs:824)
leaf: 0x18004d4 25167060 (14: row:513.513 avs:823)
leaf: 0x18004d5 25167061 (15: row:513.513 avs:823)
leaf: 0x18004d6 25167062 (16: row:513.513 avs:823)
leaf: 0x18004d7 25167063 (17: row:513.513 avs:823)
leaf: 0x18004d8 25167064 (18: row:246.246 avs:4559)
leaf: 0x18004d9 25167065 (19: row:0.0 avs:8000)
leaf: 0x18004da 25167066 (20: row:0.0 avs:8000)
leaf: 0x18004df 25167071 (21: row:0.0 avs:8000)
leaf: 0x18004dd 25167069 (22: row:0.0 avs:8000)
leaf: 0x18004de 25167070 (23: row:0.0 avs:8000)
leaf: 0x18004db 25167067 (24: row:0.0 avs:8000)
leaf: 0x18004dc 25167068 (25: row:0.0 avs:8000)
leaf: 0x18004e5 25167077 (26: row:0.0 avs:8000)
leaf: 0x18004e6 25167078 (27: row:0.0 avs:8000)
leaf: 0x18004e7 25167079 (28: row:0.0 avs:8000)
leaf: 0x18004e4 25167076 (29: row:0.0 avs:8000)
leaf: 0x18004ed 25167085 (30: row:0.0 avs:8000)
leaf: 0x18004ee 25167086 (31: row:0.0 avs:8000)
leaf: 0x18004ef 25167087 (32: row:0.0 avs:8000)
leaf: 0x18004e1 25167073 (33: row:0.0 avs:8000)
leaf: 0x18004e2 25167074 (34: row:0.0 avs:8000)
leaf: 0x18004e3 25167075 (35: row:0.0 avs:8000)
leaf: 0x18004e9 25167081 (36: row:0.0 avs:8000)
leaf: 0x18004ea 25167082 (37: row:0.0 avs:8000)
leaf: 0x18004eb 25167083 (38: row:0.0 avs:8000)
leaf: 0x18004ec 25167084 (39: row:0.0 avs:8000)
leaf: 0x18004f5 25167093 (40: row:0.0 avs:8000)
leaf: 0x18004f6 25167094 (41: row:0.0 avs:8000)
leaf: 0x18004f7 25167095 (42: row:0.0 avs:8000)
leaf: 0x18004f1 25167089 (43: row:0.0 avs:8000)
leaf: 0x18004e8 25167080 (44: row:0.0 avs:8000)
leaf: 0x18004f2 25167090 (45: row:0.0 avs:8000)
—– end tree dump
The index has indeed bloated in size as a result of the update. Note that the index would be the same size had the transaction committed, except that the leaf blocks that currently contain data would effectively be empty and contain nothing but deleted index entries while the empty leaf blocks would all contain the new indexed values.
So depending on the update operation, a unique index can potentially reuse existing index entries if the new column values existed previously in other rows. If not, then the usual delete/insert mechanism applies.
Empty Leaf Blocks After Rollback Part I (Empty Spaces) June 23, 2015
Posted by Richard Foote in Index Coalesce, Oracle Indexes, Update Indexes.7 comments
There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks and why the performance degradation after the update failed and rolled back.
This is a topic I’ve previously discussed in the blog such as in Updates and Indexes and Differences Between Unique and Non-Unique Indexes. However, I though it might be worthwhile discussing these again the in context of the rollback of a large update-based transaction.
The key point to make is that an Update is actually a delete/insert operation in the context of indexes. So if we perform a large update, all the previous indexed values are marked as deleted in the index and the new values re-inserted elsewhere within the index structure, potentially filling up a whole bunch of new leaf blocks. If we then decide to rollback the transaction (or the transaction fails and automatically rolls back), then all these newly inserted index entries are deleted potentially leaving behind now empty new leaf blocks in the expanded index structure. Here’s the thing, Oracle will roll back changes to index entries but not changes to the index structure such as block splits.
If an index scan is forced to navigate through these empty leaf blocks, this can indeed potentially have a detrimental impact on subsequent performance.
However, depending on whether the index is Unique or Non-Unique and the type of update being performed, the impact on the index could be quite different.
To illustrate all this, a simple demo.
Let’s start with a simple table and Non-Unique index on the (effectively) unique ID column:
SQL> create table bowie (id number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created.
If we look at an index tree dump of this index:
SQL> select object_id from dba_objects where object_name='BOWIE_ID_I'; OBJECT_ID ---------- 98700 SQL> alter session set events 'immediate trace name treedump level 98700'; Session altered.
—– begin tree dump
branch: 0x1800523 25167139 (0: nrow: 21, level: 1)
leaf: 0x1800524 25167140 (-1: row:485.485 avs:828)
leaf: 0x1800525 25167141 (0: row:479.479 avs:820)
leaf: 0x1800526 25167142 (1: row:479.479 avs:820)
leaf: 0x1800527 25167143 (2: row:479.479 avs:820)
leaf: 0x1800528 25167144 (3: row:479.479 avs:820)
leaf: 0x1800529 25167145 (4: row:479.479 avs:819)
leaf: 0x180052a 25167146 (5: row:479.479 avs:820)
leaf: 0x180052b 25167147 (6: row:479.479 avs:820)
leaf: 0x180052c 25167148 (7: row:479.479 avs:820)
leaf: 0x180052d 25167149 (8: row:479.479 avs:819)
leaf: 0x180052e 25167150 (9: row:479.479 avs:820)
leaf: 0x180052f 25167151 (10: row:479.479 avs:820)
leaf: 0x1800531 25167153 (11: row:479.479 avs:820)
leaf: 0x1800532 25167154 (12: row:479.479 avs:820)
leaf: 0x1800533 25167155 (13: row:479.479 avs:819)
leaf: 0x1800534 25167156 (14: row:479.479 avs:820)
leaf: 0x1800535 25167157 (15: row:479.479 avs:820)
leaf: 0x1800536 25167158 (16: row:479.479 avs:820)
leaf: 0x1800537 25167159 (17: row:479.479 avs:820)
leaf: 0x1800538 25167160 (18: row:479.479 avs:819)
leaf: 0x1800539 25167161 (19: row:414.414 avs:1795)
—– end tree dump
We notice the index is nice and compact with just the default 10% of free space.
I’m going to start by performing an update of the ID column which simply increments the ID by 1 for all rows in the table. But then, rather than commit, I’ll roll back the transaction:
SQL> update bowie set id=id+1; 10000 rows updated. SQL> rollback; Rollback complete.
Now the interesting thing to note here is that for each ID value, we temporarily have the same value twice as we progress and update each ID value (for example, for ID=1, it becomes 2 which already exists. Then the previous ID=2 becomes 3 which already exists, etc.). As the index is Non-Unique, this means when we update say ID=1 to 2, we need mark as deleted the index entry with ID=1 and insert a new index entry with an ID=2. When we update the previous ID=2 to 3, we again mark as deleted the previous indexed value of 2 and insert a new index entry of 3. Etc. Etc.
As we only have 10% of free space available in the index before the update, by updating all rows in this fashion, it means we have to keep performing 50-50 block splits to fit in the new index entries in the corresponding leaf blocks. This effectively results in the index doubling in size as we now have twice the number of index entries (with the previous index entries now marked as deleted).
However, having now performed all these index block splits, if we now roll back the update transaction, it simply means that all the new index entries are deleted and the delete byte removed from the previously deleted entries, with the index structure retaining its newly bloated size. The resultant index block splits are not rolled back. If we look at a new index tree dump:
—– begin tree dump
branch: 0x1800523 25167139 (0: nrow: 43, level: 1)
leaf: 0x1800524 25167140 (-1: row:223.223 avs:4756)
leaf: 0x180053e 25167166 (0: row:262.262 avs:4072)
leaf: 0x1800525 25167141 (1: row:242.242 avs:4373)
leaf: 0x180053f 25167167 (2: row:237.237 avs:4447)
leaf: 0x1800526 25167142 (3: row:217.217 avs:4747)
leaf: 0x180053a 25167162 (4: row:262.262 avs:4073)
leaf: 0x1800527 25167143 (5: row:217.217 avs:4747)
leaf: 0x180053b 25167163 (6: row:262.262 avs:4073)
leaf: 0x1800528 25167144 (7: row:217.217 avs:4747)
leaf: 0x180053c 25167164 (8: row:262.262 avs:4073)
leaf: 0x1800529 25167145 (9: row:217.217 avs:4747)
leaf: 0x180053d 25167165 (10: row:262.262 avs:4072)
leaf: 0x180052a 25167146 (11: row:217.217 avs:4747)
leaf: 0x1800545 25167173 (12: row:262.262 avs:4073)
leaf: 0x180052b 25167147 (13: row:217.217 avs:4747)
leaf: 0x1800546 25167174 (14: row:262.262 avs:4073)
leaf: 0x180052c 25167148 (15: row:217.217 avs:4747)
leaf: 0x1800547 25167175 (16: row:262.262 avs:4073)
leaf: 0x180052d 25167149 (17: row:217.217 avs:4747)
leaf: 0x1800541 25167169 (18: row:262.262 avs:4072)
leaf: 0x180052e 25167150 (19: row:217.217 avs:4748)
leaf: 0x180054e 25167182 (20: row:262.262 avs:4072)
leaf: 0x180052f 25167151 (21: row:136.136 avs:5962)
leaf: 0x180054f 25167183 (22: row:133.133 avs:6006)
leaf: 0x1800542 25167170 (23: row:210.210 avs:4852)
leaf: 0x1800531 25167153 (24: row:217.217 avs:4747)
leaf: 0x1800543 25167171 (25: row:262.262 avs:4073)
leaf: 0x1800532 25167154 (26: row:217.217 avs:4747)
leaf: 0x1800544 25167172 (27: row:262.262 avs:4073)
leaf: 0x1800533 25167155 (28: row:217.217 avs:4747)
leaf: 0x180054d 25167181 (29: row:262.262 avs:4072)
leaf: 0x1800534 25167156 (30: row:217.217 avs:4748)
leaf: 0x1800549 25167177 (31: row:262.262 avs:4072)
leaf: 0x1800535 25167157 (32: row:217.217 avs:4747)
leaf: 0x180054a 25167178 (33: row:262.262 avs:4073)
leaf: 0x1800536 25167158 (34: row:217.217 avs:4747)
leaf: 0x180054b 25167179 (35: row:262.262 avs:4073)
leaf: 0x1800537 25167159 (36: row:217.217 avs:4747)
leaf: 0x180054c 25167180 (37: row:262.262 avs:4073)
leaf: 0x1800538 25167160 (38: row:256.256 avs:4162)
leaf: 0x1800548 25167176 (39: row:223.223 avs:4657)
leaf: 0x1800539 25167161 (40: row:152.152 avs:5722)
leaf: 0x1800555 25167189 (41: row:262.262 avs:4073)
—– end tree dump
The index is now approximately double the size with each leaf block now approximately 1/2 empty (or 1/2 full if you’re the positive type).
If we now perform another update, but this time update all the IDs to values that don’t currently exist (by simply adding 10000 to the ID) and then again rollback:
SQL> update bowie set id=id+10000; 10000 rows updated. SQL> rollback; Rollback complete.
—– begin tree dump
branch: 0x1800523 25167139 (0: nrow: 73, level: 1)
leaf: 0x1800524 25167140 (-1: row:223.223 avs:4756)
leaf: 0x180053e 25167166 (0: row:262.262 avs:4072)
leaf: 0x1800525 25167141 (1: row:242.242 avs:4373)
leaf: 0x180053f 25167167 (2: row:237.237 avs:4447)
leaf: 0x1800526 25167142 (3: row:217.217 avs:4747)
leaf: 0x180053a 25167162 (4: row:262.262 avs:4073)
leaf: 0x1800527 25167143 (5: row:217.217 avs:4747)
leaf: 0x180053b 25167163 (6: row:262.262 avs:4073)
leaf: 0x1800528 25167144 (7: row:217.217 avs:4747)
leaf: 0x180053c 25167164 (8: row:262.262 avs:4073)
leaf: 0x1800529 25167145 (9: row:217.217 avs:4747)
leaf: 0x180053d 25167165 (10: row:262.262 avs:4072)
leaf: 0x180052a 25167146 (11: row:217.217 avs:4747)
leaf: 0x1800545 25167173 (12: row:262.262 avs:4073)
leaf: 0x180052b 25167147 (13: row:217.217 avs:4747)
leaf: 0x1800546 25167174 (14: row:262.262 avs:4073)
leaf: 0x180052c 25167148 (15: row:217.217 avs:4747)
leaf: 0x1800547 25167175 (16: row:262.262 avs:4073)
leaf: 0x180052d 25167149 (17: row:217.217 avs:4747)
leaf: 0x1800541 25167169 (18: row:262.262 avs:4072)
leaf: 0x180052e 25167150 (19: row:217.217 avs:4748)
leaf: 0x180054e 25167182 (20: row:262.262 avs:4072)
leaf: 0x180052f 25167151 (21: row:136.136 avs:5962)
leaf: 0x180054f 25167183 (22: row:133.133 avs:6006)
leaf: 0x1800542 25167170 (23: row:210.210 avs:4852)
leaf: 0x1800531 25167153 (24: row:217.217 avs:4747)
leaf: 0x1800543 25167171 (25: row:262.262 avs:4073)
leaf: 0x1800532 25167154 (26: row:217.217 avs:4747)
leaf: 0x1800544 25167172 (27: row:262.262 avs:4073)
leaf: 0x1800533 25167155 (28: row:217.217 avs:4747)
leaf: 0x180054d 25167181 (29: row:262.262 avs:4072)
leaf: 0x1800534 25167156 (30: row:217.217 avs:4748)
leaf: 0x1800549 25167177 (31: row:262.262 avs:4072)
leaf: 0x1800535 25167157 (32: row:217.217 avs:4747)
leaf: 0x180054a 25167178 (33: row:262.262 avs:4073)
leaf: 0x1800536 25167158 (34: row:217.217 avs:4747)
leaf: 0x180054b 25167179 (35: row:262.262 avs:4073)
leaf: 0x1800537 25167159 (36: row:217.217 avs:4747)
leaf: 0x180054c 25167180 (37: row:262.262 avs:4073)
leaf: 0x1800538 25167160 (38: row:256.256 avs:4162)
leaf: 0x1800548 25167176 (39: row:223.223 avs:4657)
leaf: 0x1800539 25167161 (40: row:152.152 avs:5722)
leaf: 0x1800555 25167189 (41: row:262.262 avs:4073)
leaf: 0x1800552 25167186 (42: row:0.0 avs:8000)
leaf: 0x1800553 25167187 (43: row:0.0 avs:8000)
leaf: 0x1800551 25167185 (44: row:0.0 avs:8000)
leaf: 0x1800556 25167190 (45: row:0.0 avs:8000)
leaf: 0x1800557 25167191 (46: row:0.0 avs:8000)
leaf: 0x1800554 25167188 (47: row:0.0 avs:8000)
leaf: 0x180055d 25167197 (48: row:0.0 avs:8000)
leaf: 0x180055e 25167198 (49: row:0.0 avs:8000)
leaf: 0x180055f 25167199 (50: row:0.0 avs:8000)
leaf: 0x1800558 25167192 (51: row:0.0 avs:8000)
leaf: 0x1800559 25167193 (52: row:0.0 avs:8000)
leaf: 0x1800565 25167205 (53: row:0.0 avs:8000)
leaf: 0x1800566 25167206 (54: row:0.0 avs:8000)
leaf: 0x1800567 25167207 (55: row:0.0 avs:8000)
leaf: 0x1800561 25167201 (56: row:0.0 avs:8000)
leaf: 0x180055c 25167196 (57: row:0.0 avs:8000)
leaf: 0x180055a 25167194 (58: row:0.0 avs:8000)
leaf: 0x180055b 25167195 (59: row:0.0 avs:8000)
leaf: 0x1800563 25167203 (60: row:0.0 avs:8000)
leaf: 0x1800564 25167204 (61: row:0.0 avs:8000)
leaf: 0x180056d 25167213 (62: row:0.0 avs:8000)
leaf: 0x180056e 25167214 (63: row:0.0 avs:8000)
leaf: 0x180056f 25167215 (64: row:0.0 avs:8000)
leaf: 0x1800568 25167208 (65: row:0.0 avs:8000)
leaf: 0x1800569 25167209 (66: row:0.0 avs:8000)
leaf: 0x180056a 25167210 (67: row:0.0 avs:8000)
leaf: 0x180056b 25167211 (68: row:0.0 avs:8000)
leaf: 0x180056c 25167212 (69: row:0.0 avs:8000)
leaf: 0x1800562 25167202 (70: row:0.0 avs:8000)
leaf: 0x1800575 25167221 (71: row:0.0 avs:8000)
—– end tree dump
As all the inserts now occurred in the right-hand most side of the index, Oracle allocated a bunch of new index leaf blocks via 90-10 block splits to store all the new index entries. After the rollback however, all these new entries were removed leaving behind nothing but these new empty leaf blocks which are still part of the overall index structure.
Query performance now depends on what part of the index we need to access.
If we just want to select a single value, then no problem as the ID column is effectively unique and we just need to generally access down to the one leaf block:
SQL> select * from bowie where id=42;
ID NAME
---------- ------------------------------------------
42 DAVID BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
618 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)
1 rows processed
4 consistent gets is about as good as it gets for a non-unique Blevel 1 index.
Larger index range scan might need to access additional leaf blocks as they now only contain 1/2 the number of index entries than before, although the additional overhead of such scans would still likely be minimal as most of the work is associated with visiting the table blocks.
One of the worst case scenarios would be having to now plough through all these empty leaf blocks as with the following search for the max ID value:
SQL> select max(id) from bowie; MAX(ID) ---------- 10000 Execution Plan ---------------------------------------------------------- Plan hash value: 3831694747 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| BOWIE_ID_I | 1 | 13 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 541 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) 1 rows processed
Oracle uses the Index Full (Min/Max) Scan by starting with the right-most leaf block but as it’s empty, Oracle is forced to make its way across through all the empty leaf blocks until it finally hits upon the first non-empty leaf block that contains the max ID. The excessive 32 consistent gets is due to having to access all these new empty blocks.
If we now Coalesce the index and try again:
SQL> alter index bowie_id_i coalesce; Index altered. SQL> select max(id) from bowie; MAX(ID) ---------- 10000 Execution Plan ---------------------------------------------------------- Plan hash value: 3831694747 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| BOWIE_ID_I | 1 | 13 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 541 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) 1 rows processed
We notice the Max(ID) query now returns the result with just the 2 expected consistent gets from a BLevel 1 index.
If we now look at the index tree dump:
branch: 0x1800523 25167139 (0: nrow: 21, level: 1)
leaf: 0x1800524 25167140 (-1: row:485.485 avs:828)
leaf: 0x1800525 25167141 (0: row:479.479 avs:820)
leaf: 0x1800526 25167142 (1: row:479.479 avs:820)
leaf: 0x1800527 25167143 (2: row:479.479 avs:820)
leaf: 0x1800528 25167144 (3: row:479.479 avs:820)
leaf: 0x1800529 25167145 (4: row:479.479 avs:819)
leaf: 0x180052a 25167146 (5: row:479.479 avs:820)
leaf: 0x180052b 25167147 (6: row:479.479 avs:820)
leaf: 0x180052c 25167148 (7: row:479.479 avs:820)
leaf: 0x180052d 25167149 (8: row:479.479 avs:819)
leaf: 0x180052e 25167150 (9: row:479.479 avs:820)
leaf: 0x180052f 25167151 (10: row:479.479 avs:820)
leaf: 0x1800531 25167153 (11: row:479.479 avs:820)
leaf: 0x1800532 25167154 (12: row:479.479 avs:820)
leaf: 0x1800533 25167155 (13: row:479.479 avs:819)
leaf: 0x1800534 25167156 (14: row:479.479 avs:820)
leaf: 0x1800535 25167157 (15: row:479.479 avs:820)
leaf: 0x1800536 25167158 (16: row:479.479 avs:820)
leaf: 0x1800537 25167159 (17: row:479.479 avs:820)
leaf: 0x1800538 25167160 (18: row:479.479 avs:819)
leaf: 0x1800539 25167161 (19: row:414.414 avs:1795)
—– end tree dump
We notice the index is back to its original compact self again and we can determine the Max(ID) by just visiting the last leaf block.
That’s enough of a read for now !! I’ll next look at how this scenario differs with a Unique index in Part II.
Index Tree Dumps in Oracle 12c Database (New Age) June 22, 2015
Posted by Richard Foote in 12c, TreeDumps.add a comment
I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c.
Let’s begin by creating a little table and index:
SQL> create table bowie (id number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, 'DAVID BOWIE' from dual connect by level <=10000; 10000 rows created. SQL> commit; Commit complete. SQL> create index bowie_id_i on bowie(id); Index created.
To generate an Index Tree Dump, we first need the OBJECT_ID of the index:
SQL> select object_id from dba_objects where object_name='BOWIE_ID_I'; OBJECT_ID ---------- 98829
And then use it to generate the Index Tree Dump:
SQL> alter session set events 'immediate trace name treedump level 98829'; Session altered.
Previously, an Index Tree Dump looked like the following:
—– begin tree dump
branch: 0x100023b 16777787 (0: nrow: 21, level: 1)
leaf: 0x100023c 16777788 (-1: nrow: 485 rrow: 485)
leaf: 0x100023d 16777789 (0: nrow: 479 rrow: 479)
leaf: 0x100023e 16777790 (1: nrow: 479 rrow: 479)
leaf: 0x100023f 16777791 (2: nrow: 479 rrow: 479)
leaf: 0x1000240 16777792 (3: nrow: 479 rrow: 479)
leaf: 0x1000241 16777793 (4: nrow: 479 rrow: 479)
leaf: 0x1000242 16777794 (5: nrow: 479 rrow: 479)
leaf: 0x1000243 16777795 (6: nrow: 479 rrow: 479)
leaf: 0x1000244 16777796 (7: nrow: 479 rrow: 479)
leaf: 0x1000245 16777797 (8: nrow: 479 rrow: 479)
leaf: 0x1000246 16777798 (9: nrow: 479 rrow: 479)
leaf: 0x1000247 16777799 (10: nrow: 479 rrow: 479)
leaf: 0x1000249 16777801 (11: nrow: 479 rrow: 479)
leaf: 0x100024a 16777802 (12: nrow: 479 rrow: 479)
leaf: 0x100024b 16777803 (13: nrow: 479 rrow: 479)
leaf: 0x100024c 16777804 (14: nrow: 479 rrow: 479)
leaf: 0x100024d 16777805 (15: nrow: 479 rrow: 479)
leaf: 0x100024e 16777806 (16: nrow: 479 rrow: 479)
leaf: 0x100024f 16777807 (17: nrow: 479 rrow: 479)
leaf: 0x1000250 16777808 (18: nrow: 479 rrow: 479)
leaf: 0x1000251 16777809 (19: nrow: 414 rrow: 414)
—– end tree dump
So this index is a Level 1 Index with a root block and 21 Leaf Blocks. The first entry always corresponds to the index root block and is followed by the 21 leaf blocks. Each leaf block entry details the relative block address, the sequence number, the number of index entries (nrow) and the number of non-deleted index entries (rrow).
If we look at the same Index Tree Dump in 12c (12.0.1.2):
branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:485.485 avs:828)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump
We notice the format is a little different in that it also now includes the avs (free space) within the leaf block as well.
If we now delete a few rows (and hence index entries) and look at the updated tree dump:
SQL> delete bowie where id between 1 and 400; 400 rows deleted. SQL> commit; Commit complete. SQL> alter session set events 'immediate trace name treedump level 98829'; Session altered.
branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:485.85 avs:828)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump
We notice that it now correctly details how many non-deleted index entries we now have in the first leaf block (85). Unfortunately, the free space remains the same and doesn’t take into account the deleted index entries (still recorded as 828 bytes).
Of course, if we perform any additional DML that impacts this leaf block such as another delete:
SQL> delete bowie where id=401; 1 row deleted. SQL> commit; Commit complete. SQL> alter session set events 'immediate trace name treedump level 98829'; Session altered.
—– begin tree dump
branch: 0x180017b 25166203 (0: nrow: 21, level: 1)
leaf: 0x180017c 25166204 (-1: row:85.84 avs:6725)
leaf: 0x180017d 25166205 (0: row:479.479 avs:820)
leaf: 0x180017e 25166206 (1: row:479.479 avs:820)
leaf: 0x180017f 25166207 (2: row:479.479 avs:820)
leaf: 0x18004c8 25167048 (3: row:479.479 avs:820)
leaf: 0x18004c9 25167049 (4: row:479.479 avs:819)
leaf: 0x18004ca 25167050 (5: row:479.479 avs:820)
leaf: 0x18004cb 25167051 (6: row:479.479 avs:820)
leaf: 0x18004cc 25167052 (7: row:479.479 avs:820)
leaf: 0x18004cd 25167053 (8: row:479.479 avs:819)
leaf: 0x18004ce 25167054 (9: row:479.479 avs:820)
leaf: 0x18004cf 25167055 (10: row:479.479 avs:820)
leaf: 0x18004d1 25167057 (11: row:479.479 avs:820)
leaf: 0x18004d2 25167058 (12: row:479.479 avs:820)
leaf: 0x18004d3 25167059 (13: row:479.479 avs:819)
leaf: 0x18004d4 25167060 (14: row:479.479 avs:820)
leaf: 0x18004d5 25167061 (15: row:479.479 avs:820)
leaf: 0x18004d6 25167062 (16: row:479.479 avs:820)
leaf: 0x18004d7 25167063 (17: row:479.479 avs:820)
leaf: 0x18004d8 25167064 (18: row:479.479 avs:819)
leaf: 0x18004d9 25167065 (19: row:414.414 avs:1795)
—– end tree dump
We notice the leaf block has now cleaned out the previously deleted index entries and the free space has been updated accordingly (now 6725 bytes).
Showing the amount of free space within a block is a nice little improvement to the format of the index tree dump.
Indexing and Transparent Data Encryption Part III (You Can’t Do That) June 16, 2015
Posted by Richard Foote in Oracle Indexes.add a comment
In Part II of this series, we looked at how we can create a B-Tree index on a encrypted column, providing we do not apply salt during encryption.
However, this is not the only restriction with regard to indexing an encrypted column using column-based encryption.
If we attempt to create an index that is not a straight B-Tree index, for example a Bitmap Index:
SQL> create bitmap index bowie_code_i on bowie(code); create bitmap index bowie_code_i on bowie(code) * ERROR at line 1: ORA-28337: the specified index may not be defined on an encrypted column
Or a Function-Based Index:
SQL> create index bowie_code_i on bowie(code+10); create index bowie_code_i on bowie(code+10) * ERROR at line 1: ORA-28337: the specified index may not be defined on an encrypted column
We note that such indexes can’t be defined on an encrypted column. Only standard B-Tree Indexes are supported on specifically encrypted columns.
The B-Tree index can potentially be used with equality predicates:
SQL> set autotrace on
SQL> select * from bowie where id=42;
ID CODE SALARY TEXT
---------- ---------- ---------- ------------------------------
42 42 42 BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BOWIE_ID_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
748 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As we can see, the CBO has successfully used the index. However, if we attempt to use SQL with a non-equality predicate, such as a bounded range scan:
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: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BOWIE | 2 | 34 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ID")>=42 AND
INTERNAL_FUNCTION("ID")<=43)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
814 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Or an unbounded range scan:
SQL> select * from bowie where id > 10000000000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 124 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BOWIE | 1 | 124 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("ID")>10000000000)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
108 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We notice the index is ignored and can’t be used by the CBO.
So on an encrypted column, only a standard B-Tree index with an equality based predicate can be used. These restrictions don’t however apply to indexes based on tables within encrypted tablespaces.
Richard Foote's Oracle Blog 