Re: Index Rebuilds

exactly that's what makes me go nuts. tkprof snippet was taken after coalesce. I get the preferred execution plan but the query does 65484 disk reads to run that query.

Asif,

I did gather stats after coalesce.

>Will you be using the output number of rows for further processing ?
>or is it only to see the existences of type = 'live' ?

Both. I imported the table from production to test and ran the same query. It took only couple of millisecs with index_ffs scan.

On 7/30/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> If the number of leaf block is 11,000 then how can you get 65484 disk
> reads for a fast full scan? Or is that tkprof snippet from before the
> coalesce when there were LBLKS: 66653 ?
>
> At 05:08 PM 7/28/2006, Stalin wrote:
>
> >LBLKS: 66653
> >BlVL: 3
> >CF: 679426
> >NROWS: 829734
> >AVG_DATA_BLK/KEY: 2
> >
> >Coalescing the index got the lblks to 11k and the desired plan
> >however, the performance is still under water. 10046 trace on the sql
> >is
> >
> >select count(*)
> >from
> >objects where type = 'live'
> >
> >
> >call count cpu elapsed disk query current
> > rows
> >------- ------ -------- ---------- ---------- ----------
> >---------- ----------
> >Parse 1 0.01 0.00 0 0 0
> > 0
> >Execute 1 0.00 0.00 0 0 0
> > 0
> >Fetch 2 10.39 31.07 65484 69693 0
> > 1
> >------- ------ -------- ---------- ---------- ----------
> >---------- ----------
> >total 4 10.40 31.08 65484 69693 0
> > 1
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
>
>

--
http://www.freelists.org/webpage/oracle-l