Re: Problems with a plan

From: Mladen Gogala <>
Date: Tue, 5 Apr 2011 13:03:13 +0000 (UTC)
Message-ID: <>

On Mon, 04 Apr 2011 09:06:48 -0700, joel garry wrote:

> Hopefully that will get you mad enough at the support optimizer team to
> make me wrong.

Joel, there is no need to get mad. When you cluster tables, they only exist as a logical entities. The physical entity here is the cluster segment. The data distribution within the cluster is much more sparse and there are many more blocks to read. Cluster is a killer for full table scans, full index scans and large range scans, as there are many more blocks to read. Clustering tables will usually increase the level of your indexes.
However, the 3 tables clustered together are almost always queried together, based on the common key and the queries almost always request a very small portion of the data. Clustering the tables was my ideas of how to solve a performance problem, and the developers love this particular cluster.
In this case, however, a full index scan was selected over a range scan. Fortunately, I was able to help with that query. The good news is that the plan is correct on the 11G staging database. It's only 10G production and UAT that are having problems.

Received on Tue Apr 05 2011 - 08:03:13 CDT

Original text of this message