Re: help figuring out why query doing full table scan

In our last gripping episode "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> I don't think that's the answer, David.
>
> For the hash join there are no conditions
> to eliminate rows from USER (how do you
> get a table with a reserved word as its name?)
> so the reported cardinality will match the number
> of rows in the table, and histograms will not
> affect that..
>
> I would guess (and I stress guess) that the key
> is in the numbers, though.
>
> The cardinality of the join (i.e. result set) is 20,492
> so Oracle has probably worked out that getting the
> data through a nested loop will require 20,492 index
> probes and table access for a cost of 20,492 * 5 plus
> the 1970 cost of the scan on SUMMARY_BILLING_EVENT
> A total of 104,430 - much higher than the cost of the
> hash join.
>
> If you know that Oracle's caching of the relevant index
> is going to make its I/O cost incorrect, you could adjust
> the parameter
> optimizer_index_cost_adj
> Default value = 100 - implying that every index block request
> will turn into a physical I/O - set it to (say) 10% to indicate
> that a large fraction of index block requests will be met from
> the buffer cache.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> See a first review at:
> http://www.ixora.com.au/resources/index.htm#practical_8i
> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> David Fitzjarrell wrote in message <91tncv$mmf$1_at_nnrp1.deja.com>...
> >In our last gripping episode bad <bad_at_yahoo.com> wrote:
> >> Running Oracle 8.0.5 on Solaris 2.6
> >>
> >> We have the following statement within a stored procedure that has
 all
> >> the sudden
> >> decided to do a full table scan on the user table. I don't
 understand
> >> why it all the sudden started doing this, as the statistics are up
 to
> >> date. The USER table has tens of millions of rows, so this just
 isn't
> >> going to work. Any ideas???????
> >>
> >> SELECT COUNT(DISTINCT USER.USER_ID || '@' || USER.USER_DOMAIN )
 FROM
> >> USER,SUMMARY.SUMMARY_EVENT SE WHERE USER.USAGE_USED_BE_ID =
> >> SE._EVENT_ID AND SE.CUSTOMER_SUMMARY_ID = :b1
> >>
> >> With the following explain plan output.....
> >>
> >> SELECT STATEMENT Optimizer=CHOOSE (Cost=84353 Card=1 Bytes=2991832)
> >> SORT (GROUP BY)
> >> HASH JOIN (Cost=84353 Card=20492 Bytes=2991832)
> >> TABLE ACCESS (BY INDEX ROWID) OF SUMMARY_BILLING_EVENT (Cost=1970
> >> Card=6002 Bytes=168056)
> >> INDEX (RANGE SCAN) OF SBE_CUSTOMER_SUMMARY_ID_IDX (NON-UNIQUE)
 (Cost=47
> >> Card=6002)
> >> PARTITION (CONCATENATED)
> >> TABLE ACCESS (FULL) OF USER (Cost=73345 Card=32531311
 Bytes=3838694698)
> >>
> >>0
> >
> >The key is the Card value for the scan of USER -- the optimiser is
> >expecting 32,531,311 rows to be returned. It would appear that your
> >indexed column values are not evenly distributed; I would try
> >rebuilding the index or indexes on USER and create histograms. This
> >will indicate to the optimiser the skew of the data.
> >
> >--
> >David Fitzjarrell
> >Oracle Certified DBA
> >
> >
> >Sent via Deja.com
> >http://www.deja.com/
>
>

I must admit that I looked only at the final portion of the query plan and deduced, wrongly, the crux of my response. Reading your post, and giving the query plan a second and more thorough examination, I can see where I erred. Thank you for having such good eyes -- I tend to become complacent since I have "four". 8)

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/