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 > > -- //www.freelists.org/webpage/oracle-l