Looks like a much better path when I use the PK in the view definition. Note to self, if building complex views, don't use the Foreign Key column, use the Primary Key column.

Admittedly, I can't explain all the "magic" behind it, I'm hoping someone out there could help to explain. Logically, it makes sense as the Primary Key allows you the fastest access to a specific record (discounting the rowid).

7 comments:

"the Primary Key allows you the fastest access to a specific record (discounting the rowid)."Not always. Richard Foote has some great stuff on indexes. Firstly, a primary key is a constraint, not an index, though it would need to be supported by a unique index. Generally a unique index would be faster than a non-unique index. But a smaller index (less levels) could be faster than a larger one.So it is possible for a smaller non-unique index to be faster than a larger unique one.If you've got an indexed nullable field, and 99.999% of records are null, then it could be really small (even a single block) and so be REALLY fast.

Gary,Are you sure about this?Firstly, a primary key is a constraint, not an index, though it would need to be supported by a unique indexIf I am not mistaken, one can create a primary key constraint based on a non-unique index.

Reading your post again (pardon me...I don't get things easily...:))I am not sure about your conclusionLooks like a much better path when I use the PK in the view definition. Note to self, if building complex views, don't use the Foreign Key column, use the Primary Key column.

In the examples that you provided, why do you think including PK results in "better" plan? In fact, including the PK has added more steps to the plan. For e.g. in the view definitionCREATE OR REPLACEVIEW vw_testASSELECTtc.my_id,--note that isn't the PK from Tt2.child_id--again, not the PK from T_CHILDFROMt,t_child tc,t_child_2 t2WHERE t.my_id = tc.my_idAND tc.child_id = t2.child_id

I didn't get why table T needs to be referenced? Am I missing the point?

The point, old boy, is, as Narenda points out, that in your initial view the optimizer recognises that although "T" is in your view definition, it has no need to go anywhere near it to satisify the query.

The foreign key constraints mean that, if you do not require anything to be selected, it does not need to the parent tables to validate the join condition.

Remove the foreign keys and you should see the plan that you think you want ;)

"If I am not mistaken, one can create a primary key constraint based on a non-unique index."Possibly but the primary key must be unique so you might as well use a unique index. I can understand there may be times you'd use a non-unique index to support a unique constraint, but I'd only expect to see that if the constraint was deferrable or not validated which I wouldn't expect from a PK.