Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl)

Richard Foote's Oracle Blog

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ā€¦
KDXCOLEV Flags = ā€“ ā€“ ā€“
kdxcolokā€¦
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxcosdcā€¦
kdxconro 651
kdxcofbo 1346=0x542
kdxcofeo 2172=0x87c
kdxcoavs 826

View original post 857 more words

12c Fixed Subquery

Oracle Scratchpad

Itā€™s been about 8 months since I posted a little note about a ā€œnotable change in behaviourā€ of the optimizer when dealing with subqueries in the where clause that could be used to return a constant, e.g.:

Thereā€™s been a note at the start of the script ever since saying: Check if this is also true for any table with ā€˜select fixed_value from table where primary = constantā€™Ā I finally had a few minutes this morning (San Francisco time) to check ā€“ and it does, in both 11.2.0.4 and 12.1.0.2. With the t1 table from the previous article run the following:

Instead of the historic 5% of 5% selectivity, the plan shows the optimizer predicting (approximately) the 80,000 rows that it will actually get:

I canā€™t think itā€™s very likely that anyone has written SQL that looks like this ā€“ but Iā€™m often surprised by what I see in theā€¦

View original post 28 more words