Re: need enhance feature for CBO :)
From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Thu, 13 Nov 2008 12:00:31 +0700
Message-ID: <3edcb66e0811122100q601b1675i49d771fbbbf5c64f@mail.gmail.com>
Date: Thu, 13 Nov 2008 12:00:31 +0700
Message-ID: <3edcb66e0811122100q601b1675i49d771fbbbf5c64f@mail.gmail.com>
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_at_enkitec.com> wrote:Received on Wed Nov 12 2008 - 23:00:31 CST
> 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