Re: Does Ref. Integrity help CBO ?

Brian Peasland wrote:
> klabu wrote:
> > 10gR2
> >
> > Does Ref. Integrity help CBO ?
> > or maybe my Q should be: Does Statistics take RI into consideration ?
> >
> > So if say joining n tables....
> > Does the fact that these tables have RI amongst them "help" CBO do a
> > "better" job
> > than if they just have PKs defined ?
> >
> >
> > thanks, hope i'm making sense, I tried ;)
> >
> >
>
> I don't know for sure, but my inclination is that a FK constraint by
> itself does not help the CBO. However, indexing the FK columns *may*
> help the join performance. If that is the case, it is the index and not
> the FK constraint that is providing benefit.
>
>

I don't know much about it, but I've heard tell in the DW business people use dimensions and disable and rely to influence the optimizer's use of query rewrite. See

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm
http://www.oracle.com/technology/oramag/oracle/03-sep/o53business.html
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/qrbasic.htm#g1045551

http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part4.html#2 etc.

In a nutshell, CBO can use RI to evaluate to the correct data access path in certain specific situations that you define. Of course, with materialized views, it could be said you are simply prejoining, shoving the performance off to a different time than the user is judging it. At runtime, it's the trusted constraint not the index that is helping decide. That's why we can wear those funny pointy hats with the stars and lightning bolts, and not just this time of year.

In a more general sense, yes, any more information you give to the optimizer to help it decide the best access to the data helps. The first thing the optimizer does is determine if a single row can be joined between two tables via a primary or unique key, and if so, those get joined first. So if you are joining many tables, and the order of evaluation affects the subsequent optimization steps, the simple existence of the key can affect the rest of plan, whether or not it eventually uses the index. This can cascade into differences of opinion of whether, say, nested loop joins should be used. http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i76330

jg

--
@home.com is bogus.
http://www.californiaprogressreport.com/2006/10/different_stori.html
"Uniformed men with guns came into my office and took all my computer
equipment and campaign supplies... this is all a misunderstanding about
the meaning of the word 'emigrado.'  That's what every agent asks when
they are arresting illegals."  -  Tan Nguyen, on 1070 news radio.