Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
  • Date: Tue, 24 Nov 2009 21:46:33 +0100

> To answer your initial question, I think index access does in fact 
> make sense. I'm retrieving anywhere from one to a thousand rows from 
> letter_bin per call. This query is executed thousands of times as 
> part of a batch job.

This raises in first place the question why do you need to execute the query 
thousands of times?

Apart from that you say that when accessing even only a thousand of rows via 
the index the performance is worse than performing a full table scan? Looking 
at the number of blocks of the table that seems to be rather unlikely to me.

According to the plans posted most of the time is wasted on accessing the table 
by rowid - is your issue really the usage of the index? I can't believe that 
the examples posted would be faster when using a FTS for 1,700 rows.

Can you show us an example with ALLSTATS LAST where the modified plan (e.g. 
using the cardinality hint) is actually faster?

Have you tried to set the DENSITY manually to address the single-table access 
estimate issue?

> As a frame of reference, the table represents letters being mailed to 
> an employer(prty_id), for every employee. There are many small 
> businesses each having a few rows. There are medium and large 
> businesses that have many rows. And finally there are employment 
> agencies which have a LOT of rows. Hopefully this explains the 
> distribution of data.

You have more than 200,000 distinct values - at a quick glance almost every 
value of the 254 recorded in the histogram seems to be unpopular. The histogram 
is almost useless given your distribution I would say.

> As far as the transitive closure issue, there are also other queries 
> which query letter_bin by party_id without the join to parties - 
> those are affected as well. The cardinality is always incorrect for 
> any query by letter_bin.party_id.

It was just in idea where I would be interested in the result - as I said it 
doesn't address the root cause.


Oracle related stuff blog:

Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter


Other related posts: