Re: Optimizer not using histogram to calculate estimated number of rows?
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: info@xxxxxxxxxxxxxxx
- Date: Wed, 10 Jun 2009 21:39:11 -0700
What I would suggest is to try and debug the bad plan by using the
CARDINALITY hint to tell the optimizer how many rows are exactly
coming back from this table. If the issue which the bad plan is
indeed the cardinality estimate for the rowsource for psroleuser, than
this should give you a better plan. If the plan is still not "good",
there is is more to the problem.
select /*+ cardinality(psroleuser 169) */ * from psroleuser where
roleuser ='HVA_FUNC'
You can try the Cardinality Hindsight Method(TM) for a few different
values of roleuser and see if you get the desired plans.
On Wed, Jun 10, 2009 at 3:20 PM, David Kurtz<info@xxxxxxxxxxxxxxx> wrote:
> I have another version of this table on a test database, but the volume is
> slightly smaller (26000 rows, 3300 distinct values)
>
> That uses the histogram exactly as I would expect, the estimated number of
> rows is close to reality, and the execution plan of the complex query
> changes.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
