RE: need enhance feature for CBO :)

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