Reading through Expert Oracle Application Express is a definite learning experience; I highly recommend it.
Today's post comes from Sharon Kennedy's chapter on interactive reports, and highlights a feature of Oracle's tuning engine that I've never really paid much attention to–but if you're making complex views for use in a variety of places, or in an interactive report, you ought to know.
The short version is that if one or more of your columns in a view is derived from an inline view, rather than from a table joined in the main from/where clauses, and you write a query which does not reference those columns, Oracle doesn't bother pulling the data from them at all.
A very simplified example, using the Oracle e-Business Suite tables. Consider the following query:
select msi.organization_id, msi.inventory_item_id, msi.description item_description, msi.atp_rule_id, mar.rule_name atp_rule, papf.full_name buyer, pv.vendor_name supplier_name from inv.mtl_atp_rules mar, hr.per_all_people_f papf, po.po_vendors pv, inv.mtl_system_items_b msi where msi.organization_id <> 103 and substr (msi.segment1, 0, 3) <> 'DOC' and mar.rule_id(+) = msi.atp_rule_id and papf.person_id(+) = msi.buyer_id and sysdate between papf.effective_start_date(+) and papf.effective_end_date(+) and pv.vendor_id(+) = to_number(msi.attribute9);
Now, if you use this as the code for a view, and then do an explain plan on selecting just the organization_id and inventory_item_id columns (both of which are from inv.mtl_system_items_b), you'll see that Oracle still joins out to the other tables. In this particular case, the difference is small, but that won't always be the case. And it's always good to minimize the work done by the system. So, a better way to write the above query is:
select msi.organization_id, msi.inventory_item_id, msi.description item_description, msi.atp_rule_id, ( select mar.rule_name from inv.mtl_atp_rules mar where mar.rule_id = msi.atp_rule_id ) atp_rule, ( select papf.full_name from hr.per_all_people_f papf where papf.person_id = msi.buyer_id and sysdate between papf.effective_start_date(+) and papf.effective_end_date(+) ) buyer, ( select pv.vendor_name from po.po_vendors pv where pv.vendor_id = to_number(msi.attribute9) ) supplier_name from inv.mtl_system_items_b msi where msi.organization_id <> 103 and substr (msi.segment1, 0, 3) <> 'DOC';
This gives the same data as the first query (note the outer joins in the first, which aren't necessary in the second version), but a query that doesn't include the atp_rule, buyer, or supplier_name columns will not even touch those tables. A small improvement in this instance, but, again, potentially much larger if one of your columns takes a while to calculate and isn't used very often.
The nice part of this for APEX is in interactive reports. As far as I can tell, APEX uses the code you provide for the report as an inline view, and only selects the columns necessary for display to the user. Which means, of course, that if some of your columns use inline selects and they're not displayed to the user, Oracle will skip over the logic for them–potentially saving your users an annoying wait.