Search

OakieTags

Who's online

There are currently 0 users and 48 guests online.

Recent comments

Affiliations

bugs

FBI Skip Scan

A recent posting on the OTN database forum highlighted a bug (or defect, or limitation) in the way that the optimizer handles index skip scans with “function-based” indexes – it doesn’t do them. The defect has probably been around for a long time and demonstrates a common problem with testing Oracle – it’s very easy for errors in the slightly unusual cases to be missed; it also demonstrates a general principle that it can take some time for a (small) new feature to be applied consistently across the board.

The index definitions in the original posting included expressions like substr(nls_lower(colX), 1, 25), and it’s possible for all sorts of unexpected effects to appear when your code starts running into NLS  settings, so I’ve created a much simpler example. Here’s my table definition, with three index definitions:

Index Compression – aargh

The problem with telling people that some feature of Oracle is a “good thing” is that some of those people will go ahead and use it; and if enough people use it some of them will discover a hitherto undiscovered defect. Almost inevitably the bug will turn out to be one of those “combinations” bugs that leaves you thinking: “Why the {insert preferred expression of disbelief here} should {feature X} have anything to do with {feature Y}”.

Here – based on index compression, as you may have guessed from the title – is one such bug. I got it first on 11.1.0.7, but it’s still there on 11.2.0.4 and 12.1.0.1

Pagination

I was involved in a thread on Oracle-L recently started with the question: “How many LIOs is too many LIOs”. Rather than rewrite the whole story, I’ve supplied a list of links to the contributions I made, in order – the final “answer” is actually the answer to a different question – but travels an interesting path to get there.#

Index Hash

I’m afraid this is one of my bad puns again – an example of the optimizer  making a real hash of the index hash join. I’m going to create a table with several indexes (some of them rather similar to each other) and execute a query that should do an index join between the obvious two indexes. To show how obvious the join should be I’m going to start with a couple of queries that show the cost of simple index fast full scans.

Here’s the data generating code:

Not Pushing

Here’s an odd little optimizer glitch – probably irrelevant to most people, but an indication of the apparent randomness that appears as you combine features. I’ve created an example which is so tiny that the only explanation I can come up with the for optimizer not “behaving properly” is that I’ve found an undocumented restriction relating to a particular feature.

Here’s the basic schema structure with query and execution plan – there’s nothing particularly significant about the object definitions – they’re just a couple of (reduced) structures from a client site I visited a few years ago:

Deferrable RI

Here’s a lovely little example that just came up on the OTN database forum of how things break when features collide. It’s a bug (I haven’t looked for the number) that seems to be fixed in 12.1.0.1. All it takes is a deferrable foreign key and an outer join. I’ve changed the table and column names from the original, and limited the deferability to just the foreign key:

Hash Clusters – 3

This note is a quick summary of an oddity that came to light after a twitter conversation with Christian Antognini yesterday. First a little test script to get things going:

Quiz Night

Okay, it’s a little early in the day (for me at least) to say “night” – but here’s a fun little detail I picked up in Prague yesterday. What do you think will happen when you try to execute the following two queries:


select 0/0 from dual;

select count(*) from (select 0/0 from dual);

I’ve only tried it on 11.2.0.4 and 12.1.0.1 – I could imagine the results might be different if you’re still running 8i or 9i.

If those are too easy, you might want to think about an example that Julian Dontcheff produced at OpenWorld:


select power(0,0) from dual;

What SHOULD the answer be, and what do you think Oracle will supply ?

FBI decode

It probably won’t surprise many people to hear me say that the decode() function can be a bit of a nuisance; and I’ll bet that quite a lot of people have had trouble occasionally trying to get function-based indexes that use this function to behave properly. So (to put it all together and support the general directives that case is probably a better choice than decode() and that the cast() operator is an important thing to learn) here’s an example of how function-based indexes don’t always allow you to work around bad design/code. (Note: this is a model of a problem I picked up at a client site, stripped to a minimum – you have to pretend that I’m not allowed to fix the problem by changing code).

First we create some data and indexes, and gather all relevant stats:

Sorted Hash Clusters RIP

Sorted Hash Clusters have been around for several years, but I’ve not yet seen them being used, or even investigated in detail. This is a bit of a shame, really, because they seem to be engineered to address a couple of interesting performance patterns.

The basic concept is that data items that look alike are stored together (clustered) by applying a hashing function to generate a block address; but on top of that, if you query the data by “hashkey”, the results are returned in sorted order of a pre-defined “sortkey” without any need for sorting. (On top of everything else, the manuals describing what happens and how it works are wrong).