RE: need enhance feature for CBO :)
- From: "Michael Fontana" <mfontana@xxxxxxxxxxx>
- To: <mwf@xxxxxxxx>, <jkstill@xxxxxxxxx>, <ujang.jaenudin@xxxxxxxxx>
- Date: Wed, 12 Nov 2008 16:10:55 -0600 (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
Other related posts:
