Re: Problems with a plan
Date: Tue, 5 Apr 2011 13:03:13 +0000 (UTC)
Message-ID: <pan.2011.04.05.13.03.13_at_gmail.com>
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.
-- http://mgogala.byethost5.comReceived on Tue Apr 05 2011 - 08:03:13 CDT