I’ve been a little slow in the follow-up to my previous posting on possibly redundant indexes. Before going into the slightly more complex stuff, there’s another peripheral point (but a very important one) that’s worth raising about how clever the optimizer can be. Here’s some code for 18.104.22.168 to demonstrate the point:
I’ve said a number of times, the process of writing articles is part of an ongoing learning experience for me. A few days ago my personal tech editor (Jonathan Lewis) asked about a statement I made in the SQL Plan Directive article. On further investigation it turned out the sentence was a complete work of fiction on my part, based on my misunderstanding of something I read in the manual, as well as the assumption that everything that happens must be as a result of a new feature.
As always, installations of Oracle server products on Fedora are not a great idea, as explained here.
I was reading some stuff about the Fedora 23 Alpha and realised Fedora 22 had passed me by. Not sure how I missed that.
Anyway, I did a run through of the usual play stuff.
It’s interesting to watch the CBO evolving and see how an enhancement in one piece of code doesn’t necessarily echo through to all the other places it seems to fit. Here’s an example of an enhancement that spoiled (or, rather, made slightly more complicated) a little demonstration I had been running for about the last 15 years – but (in a fashion akin to another partitioning limitation) doesn’t always work in exactly the way you might expect.
My recent dalliance with YouTube (141 subscribers and growing! ) has left the blog feeling a little bit unloved of late, but then why write when you can waffle in the car?
Anyway, the 12c learning train keeps on rolling. I’ve recently put the following articles live.
No, not really – but sometimes the optimizer gets better and gives you worse performance as a side effect when you upgrade. Here’s an example where 22.214.171.124 recognised (with a few hints) the case for a nested loop semi-join and 12c went a bit further and recognised the opportunity for doing a cunning “semi_to_inner” transformation … which just happened to do more work than the 11g plan.
Here’s a data set to get things going, I’ve got “parent” and “child” tables, but in this particular demonstration I won’t be invoking referential integrity:
I was looking for something in the New Features Manual and I had a total WTF moment when I saw this stuff.
If you look at the final section of the article, you can see in some cases these just get transformed to regular joins and outer joins, but there is certainly something else under the hood, as shown by the pipelined table function example.
I think it’s going to take me a long time before I think of using these in my regular SQL…
A little over a year ago I was at the BGOUG Spring Conference and I watched a session by Maja Veselica about auditing in Oracle Database 12c. At the time I noted that I really needed to take a look at this new functionality, as is was quite different to what had come before. Fast forward a year and I’ve finally got around to doing just that.
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: To generate an Index Tree Dump, we first need the OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]
I’ll be running a free Oracle Database In-Memory Test Drive Workshop locally here in Canberra on Tuesday, 28th April 2015. Just bring a laptop with at least 8G of RAM and I’ll supply a VirtualBox image with the Oracle Database 12c In-Memory environment. Together we’ll go through a number of hands-on labs that cover: Configuring the Product Easily […]