Oracle Scratchpad

September 11, 2012

FBI Delete

Filed under: Bugs,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 5:56 pm BST Sep 11,2012

A recent post on Oracle-l complained about an oddity when deleting through a function-based index.

I have a function based index but the CBO is not using it. The DML that I expect to have a plan with index range scan is doing a FTS. It’s a simple DML that deletes 1000 rows at a time in a loop and is based on the column on which the FBI is created.

Although execution plans are mentioned, we don’t get to see the statement or the plan – and it’s always possible that there will be some clue in the (full) plan that tells us something about the code that the OP has forgotten to mention. However, function-based indexes have a little history of not doing quite what you expect, so I thought I’d take a quick look at the problem, starting with the simplest possible step – do function-based indexes and “normal” b-tree indexes behave differently on a delete. Here’s the data set I created for my test:
(more…)

April 23, 2012

NVL2()

Filed under: Function based indexes,Indexing,Oracle,Performance — Jonathan Lewis @ 5:43 pm BST Apr 23,2012

There are many little bits and pieces lurking in the Oracle code that would be very useful if only you had had time to notice them. Here’s one that seems to be virtually unknown, yet does a excellent job of eliminating calls to decode().

The nvl2() function takes three parameters, returning the second if the first is not null and returning the third if the first is null. This is  convenient for all sorts of examples where you might otherwise use an expression involving  case or decode(), but most particularly it’s a nice little option if you want to create a function-based index that indexes only those rows where a column is null.
(more…)

December 30, 2011

FBI Bug

Filed under: Bugs,Execution plans,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 5:47 pm BST Dec 30,2011

Here’s a funny little optimizer bug – though one that seems to have been fixed by at least 10.2.0.3. It showed up earlier on today in a thread on the OTN database forum. We’ll start (in 9.2.0.8) with a little table and two indexes – one normal, the other descending.
(more…)

June 30, 2011

Virtual bug

Filed under: Bugs,Function based indexes,Indexing,Oracle,Parallel Execution,Statistics,Troubleshooting — Jonathan Lewis @ 5:37 pm BST Jun 30,2011

I’ve said in the past that one of the best new features, in my view, in 11g was the appearance of proper virtual columns; and I’ve also been very keen on the new “approximate NDV” that makes it viable to collect stats with the “auto_sample_size”.

Who’d have guessed that if you put them both together, then ran a parallel stats collection it would break :(

The bug number Karen quotes (10013177.8) doesn’t (appear to) mention extended stats – but since virtual columns, function-based indexes, and extended stats share a number of implementation details I’d guess that they might be affected as well.

December 16, 2010

FBI oddities

Filed under: Function based indexes,Indexing — Jonathan Lewis @ 6:15 pm BST Dec 16,2010

Function-based indexes are wonderful things – but they don’t always work exactly as expected. Here’s an example of one such anomaly.

Imagine you have some type of “orders” table where most orders are in a “finished with” state, and you have a requirement to access the small number of orders in the “new” state. Here’s a sample data set to emulate this type of data requirement (created in 11.1.0.6, 1MB uniform extents, freelist management and 8KB blocks).

rem
rem     Script:         fbi_bug_10204.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2010
rem

create table t1 (
	state		varchar2(10),
	n1		number,
	v1		varchar2(10),
	padding	varchar2(100)
);

insert into t1
select
	decode(mod(rownum,100),0,'OPEN','CLOSED'),
	rownum,
	lpad(rownum,10,0),
	rpad('x',100,'x')
from
	all_objects
where
	rownum <= 5000
;

(more…)

October 15, 2010

Good Nulls

Filed under: CBO,Function based indexes,Indexing,NULL,Tuning — Jonathan Lewis @ 6:17 pm BST Oct 15,2010

I’ve often been heard to warn people of the accidents that can happen when they forget about the traps that appear when you start allowing columns to be NULL – but sometimes NULLs are good, especially when it helps Oracle understand where the important (e.g. not null) data might be.

An interesting example of this came up on OTN a few months ago where someone was testing the effects of changing a YES/NO column into a YES/NULL column (which is a nice idea because it allows you to create a very small index on the YESes, and avoid creating a histogram to tell the optimizer that the number of YESes is small).

They were a little puzzled, though, about why their tests showed Oracle using an index to find data in the YES/NO case, but not using the index in the YES/NULL case. I supplied a short explanation on the thread, and was planning to post a description on the blog, but someone on the thread supplied a link to AskTom where Tom Kyte had already answered the question, so I’m just going to leave you with a link to his explanation.

January 13, 2009

FBI Stats

Filed under: CBO,Function based indexes,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 7:29 pm BST Jan 13,2009

It’s very easy to forget that 10g creates statistics on an index as it builds (or rebuilds) the index – and this can lead to some oddities when you add a function-based index (or, as I tend to name them, index on virtual columns) to a table.

This point (along with a couple of other observations) came up in a discussion on OTN a little while ago, with some useful responses from Richard Foote (the “index king”), Christian Antognini (author of  “Trouble-shooting Oracle Performance”) and a few others, including me.

« Previous Page

Powered by WordPress.com.