David S. Gale

APEX, Life, and what I think about it all

Pipelined Functions

by Leave a Comment

Yep, this is another post where I’m going to talk about a feature of Oracle which I didn’t know about until very recently, and which I wish I’d heard of a lot sooner. Today’s topic is pipelined functions.

Now, I feel like I may have heard about pipelined functions once or twice over the years, but I never really understood what they could be used for. Part of the problem may be that when they’re mentioned, it’s often in the specific case of loading large amounts of data into a data warehouse environment, and most of my programming is for querying small amounts of data from ERP-type systems. But pipelined functions are not just for data warehouse situations!

If you’ve been following this blog for a while (or just read through the archives), you’re probably aware of my posts about database views and how they can be very powerful. And that hasn’t changed. But I’ve also found that, if you’re not very careful, referencing views from other views can quickly lead to massive performance problems. WriteTrack ran into this, though it was minor enough that I was able to get around it; I had a series of nested views which produced the calendars for every challenge for a specific user. Originally, the views were designed to produce the calendars for every challenge for every user, but this didn’t work well*, and I had to put v(‘APP_USER’) into the views–which meant that I couldn’t check the views through the SQL workshop, and that I couldn’t easily debug what was happening if a user complained that they were getting the wrong information.

Then I found out about pipelined functions, which were introduced by Oracle way back in 9i. A pipelined function is simply a PL/SQL function which returns a result that can be treated as a table in SQL. One of the first places I used them was in parsing an XML file; I created a view which wrapped the call to the XML parsing function, and was able to treat the data from the XML as if it were a table–all of the ugly calls to DBMS_XML were safely tucked away, making for much cleaner code (and easier APEX development!).

So, last October, I returned to my probelmatic WriteTrack views and re-wrote them as wrappers around a pipelined function. Now, instead of having four or five nested views to do all the necessary calculations (a debugging headache of its own), all the code is in one function. Which can, incidentally, produce all of the data for all users and all challenges in about a second.

*  Ok, it didn’t work at all. Once there were multiple users and several different challenges, querying the overall view simply failed to return anything in less than an hour–hardly acceptable for an APEX application!

Merge

by Leave a Comment

This post is partly a reminder for myself; it’s about a part of SQL, which I tend to forget to use. But if I forget about it, then that means that others are likely to as well, right? (heh)

Anyhow, I was trying to create a process for maintaining sample data in an application–the users can install an application, muck up the sample data all they want, and then hit a “reset” button which takes everything back to the beginning. Pretty straightforward; just delete the data, and re-insert it. But the sample data is just to get people started using the apps, so we can’t just wipe the table–they may be using some of the data, or may have made changes that they want to keep, etc.

So we wanted to do something like this:

insert into table1 ( id, col1, col2 )
select 1 id, 'Sample' c1, 'Value' c2 from dual
where not exists ( select null from table1 where id = 1;

That’d work, but in my case, I was loading 10 different tables, and anywhere from 3 to 15 different rows per table. I knew that I’d end up with a different id in the data I was inserting and in the not exists test sooner or later due to excessive cut-and-paste. (Alternatively, I could wrap each insert statement in a begin/end block and catch the duplicate ID exception–but who wants to do all that?) So I started trying to make something that would be simpler to maintain.

My solution was a private procedure that took a table name and an associative array of column names and values, and then built up the above insert statement using dynamic SQL. Effective, but rather ugly. I’m not even going to bother posting it here.

One of my co-workers then suggested that I use merge. The syntax is a little more complex than the above insert statement, but I can use one merge for all of the rows in a table–that’s a definite win. And I don’t have to type any of the values twice, so that source of error is gone!

merge into table1 dest using (
    select 1 id, 'Sample' c1, 'Value' c2 from dual
    union all
    select 2, 'More', 'Value2' from dual
    ...
    ) src
on ( dest.id = src.id )
when not matched then
    insert (id, col1, col2 )
    values (src.id, src.col1, src.col2 );

It’s fast, it’s secure, and it’s easy to maintain. The only downside is that it wasn’t my first approach.

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.

The power of views

by Leave a Comment

I like views. I don't mean  panoramic landscapes, though I do like them, too. But here, I'm talking about database views.

In its simplest form, a view simply provides a different way of accessing data within a table or two within your database. But done they are capable of so very much more; here are some uses I've found for them.

  • Hiding calculations – fairly obvious, you can create a view–or series of views–that include complex calculations that are used multiple other places. My NaNoWriMo tracker is a great example of this–I have two views which together comprise at least 80% of the calculations required by the tool. Some of the calculations are analytic functions, so it's really nice to be able to write that once, tuck the logic away, and just trust it to work whenever I select current_target from the view.
  • Hiding data – Got sensitive data that only certain users should be allowed to see? If Oracle's Virtual Private Database (VPD) isn't an option, this can be a great poor-man's substitute. I'd generally go with a VPD, because it's a more robust solution, but it also takes more work to set up. And, of course, if you look at a view's code, you can see whether something is being masked; VPD's don't give you that visibility. (Yes, there's positives to both sides of that last point!)
  • Creating data – Um, yeah. You can create data on the fly with views. I mentioned doing this in passing in my post on cartesian joins, but the technique is fairly simple. If you need a sequence of numbers, for instance, just do something like

    		select level var from dual connect by level < 10;

    Instant data!

  • Outer join to multiple tables - I love this one. Normally, if you try to outer join one table to two others, Oracle throws an error. But if you encapsulate those two tables in a view, you can outer join to that view, and Oracle proceeds happily. One place I tend to run into this is the E-Business Suite's PO tables--PO_HEADERS, PO_LINES, PO_RELEASES, PO_LINE_LOCATIONS--and being able to coax Oracle into doing what I want is wonderful.
  • Enabling table restructures - I have to give credit for this one to a great friend and DBA that I worked with six years ago. We needed to revamp our entire table structure, but couldn't afford to take the business offline for the time it would take to do so (who could?). The solution he came up with was to create views based on the current table structure, and have all of the developers modify our code to point to the views instead of the tables. Once that was done, he was able to build the new tables, populate them with data, and update the view code to point to the new structure at his leisure. It was still a bit of work, but the risk of unwanted downtime was reduced to a minimum.

Of course, Uncle Ben's maxim that "with great power comes great responsibility" holds true for views. Because they are capable of hiding so much behind the scenes, it's very possible to write a simple-looking query with abysmal performance; it's the developer's responsibility to constantly review their query performances, and check to see if a view is truly necessary.