Re: need enhance feature for CBO :)

hi,

this table and its indexes never analyzed, due to huge rows (more than
billion rows).
of course PVC_U1 is smaller physical blocks rather than PVC_PK.

from development machine which has <14 million rows (through dba_segments):
PVC_PK = 1280 blocks per partitions
PVC_U1 = 256 blocks per partitions

in development:

select distinct seq from pvc;
using PVC_U1 with parallel process
-- > 5 mins never end :)

select /*+ noparallel_index(pvc) */ distinct seq from pvc;
using PVC_U1 with no parallel process
-- 3mins 31 sec

select /*+ index_ffs(pvc pvc_pk) */ distinct seq from pvc;
using PVC_PK with no parallel process
--46 sec

another parameter : we set dynamic_sampling to 2

-- 
thanks and regards
ujang | oracle dba
jakarta | http://ora62.wordpress.com

On Thu, Nov 13, 2008 at 5:10 AM, Michael Fontana <mfontana@xxxxxxxxxxx> wrote:
> Perhaps this should not even be an IOT to begin with if the SEQ and ES
> columns guarantee uniqueness?
>
>
>
> Just make the unique key the primary.  That might just get you a little
> better performance.
>
>
>
> Perhaps also we can put this matter to bed if ujang will send us output from
> the following query:
>
>
>
> Select * from dba_tables where table_name = 'PVC";
>
>
>
> That way we can validate statistics!?
>
>
>
>
>
>
>
>
>
>
>
>
>
> Jared asks an interesting question, but I'm even more interested in why you
> think
>
>
>
> "I think scanning Primary key
>  more efficient rather than roundtrip read PVC_U1 index and then again
>  read PVC_PK index, due to IOT mapping..." ?
>
>
>
> Your query only needs the seq column. All the column seq values are in
> pvc_u1, which is smaller than the PK.
>
> So of course the CBO uses the smaller index. Since both SEQ and ES are in
> the PK, I can absolutely guarantee that pvc_u1 is smaller than the PK, which
> contains them and additional columns.
>
>
>
> Now if you needed to look up values not contained in pvc_u1 (that is other
> columns than SEQ and ES) that are contained in the PK, or for that matter
> any non-overflow column in the case of an IOT, then your concern about going
> back to the IOT would be valid. But the value you want IS in the index and
> that index is guaranteed to be the smaller object.
>
>
>
> Regards,
>
>
>
> mwf
>
>
--
http://www.freelists.org/webpage/oracle-l


Other related posts: