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.