David S. Gale

Programming, Life, and what I think about it all

Tuning tip: Inline Selects

by Leave a Comment

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.

Time zones, or, You’re logging in from where?

by Leave a Comment

This is going to be one of those posts where I talk about an issue I'm working with, but where I don't even pretend to have a definitive answer. So, what follows is what I've managed to figure out, but there may be a better way (in fact, I kind of hope there is).

My NaNoWriMo tracker has proven to be more popular than I originally expected–I now have over 50 registered users. And looking at my traffic sources, a problem occurred to me–I'd designed the application without considering the fact that it'd be used by people all over the world. And since almost all of its functionality is very time-focused, this was a problem.

The first thing I needed to fix was to change my view logic–there were several instances where I used sysdate (don't we all?), and those needed to be changed to current_date. sysdate gives the current database time, while current_date shifts it to the local time in the current session.

Of course, that requires that we actually be able to set the time zone for the current session. APEX gives us a handy procedure–apex_util.set_session_time_zone. This procedure does exactly what we want, though the timezone must be passed as an offset from UTC. More on that later.

Next, we need to provide the users with a list of timezones to chose from. I really struggled with this one; in the end, the best I could come up with was:

select tzname||' ('||tm||')' d, tzname r
from (select distinct tn.tzname, substr(tz_offset(tn.tzname),1,6) tm
      from v$TIMEZONE_NAMES tn
)
order by decode(substr(tm,1,1),
        '+', 24 + to_number(substr(tm,2,2)),
             24 - to_number(substr(tm,2,2))) desc,
    to_number(substr(tm,5,2)) desc,
    tzname

This results in a very long list, but ensures that the users are able to find something that matches their location. Just storing the offsets would make for a smaller list, but then you have to worry about locations that don't observe Daylight Savings Time (or, conversely, those that do). tzname is varchar2(64), so I added that to my central users table; I could have added it to an application-specific config table, but someone's location isn't going to change based on which application they're using.

Finally, I created an application-level process to call apex_util.set_session_time_zone(tz_offset(…)) with the timezone stored in the user table. This ensures that the timezone is properly set, regardless of which page the user lands on.

The one feature I wasn't able to add that I wanted to was a field next to the timezone selector that would update when the user chose a new timezone and display the current time in that timezone. I'm not sure why this wasn't working; I tried several different dynamic actions, and found that on my test page, at least, it was using the original value of the select box instead of the selected value. I'm going to have to do some more testing to see if I can figure out what I was doing wrong there.