When dealing with the CR I usually add a couple of views, i.e.:
create or replace view mis_active_products as
select x.*
from cr_items ci, mis_productsx x
where ci.item_id = x.item_id and
x.revision_id = ci.live_revision;
create or replace view mis_fast_products as
select p.*, ci.name, cr.*
from cr_items ci, mis_products p, cr_revisions cr
where p.product_id = ci.live_revision and
p.product_id = cr.revision_id;
The first one enables a cleaner code, because I can use it without worrying about joining with cr_items and cr_revisions.
I use the second one for performance reasons, because I noticed that when using the auto generated mis_productsx view the resulting query plan is really awfull.
