Oracle Views: Are calculated columns always evaluated even when they are not considered in a query?

April 21, 2009 2:14 PM

Oracle Views: Are calculated columns always evaluated even when they are not considered in a query?

I'd like some advice regarding Oracle views. Are fields always evaluated regardless of whether they are required in the results set?

Let's say I have tables PRODUCTS and ORDERS. And I create a view called PRODUCTS_VIEW which consists of something like:

SELECT
PRODUCT.ID,
PRODUCT.NAME,
fcnGetMostRecentOrderDate(PRODUCT.ID) AS MostRecentOrderDate,
fcnGetTotalOrdersCount(PRODUCT.ID) AS TotalOrdersCount,
fcnGetMaxOrderVolumn(PRODUCT.ID) AS MaxOrderVolumn
and so on
FROM
PRODUCTS,
ORDERS

I.e. a flat view that encapsulates all possible attributes--I estimate around 20 static and 60 calculated fields.

This is for reporting purposes, either queried directly or perhaps wrapped up in an API. Maintainability is important, if I need a new attribute then I can easily add a new field as necessary. Performance is certainly a factor but, since it's only for reporting purposes, there is a degree of flexibility.

If I run a query that only considers 2 or 3 fields e.g.

SELECT PRODUCT.NAME, MostRecentOrderDate
FROM PRODUCTS_VIEW
WHERE... say....CLIENT = Heinz

Would it still evaluate *all* the other calculated fields? (I think it does unfortunately: and I'm concerned that performance would degrade as new fields are added.)

If it *does* behave thus is there a way around it? I suppose a materialized view is an option. Or is this an inappropriate use? I am avoiding dynamic SQL since I would rather consolidate the logic into something that can be made (easily) accessible through ODBC, ADO, a bundle of Web Service methods etc. Note that a data warehouse approach (e.g. a daily dump of denormalised data) is not appropriate since data updates need to reflected immediately.

Thanks.

posted by NailsTheCat to Computers & Internet (7 answers total)

Can you not make additional views with just the fields you need? You will need to include them as objects in your external connections which will add some upkeep if you are adding new attributes frequently though.
posted by spatula at 2:40 PM on April 21, 2009

Response by poster: Thanks spatula. Yes, that's an option too. I've pondered over this before, something like:
PRODUCTS_VIEW_MAIN
PRODUCTS_VIEW_ORDER_DATA
PRODUCTS_VIEW_CLIENT_DATA
PRODUCTS_VIEW_RESOURCE_HUNGRY_STUFF
etc.
this way at least I could avoid calculating ALL the fields ALL the time.
posted by NailsTheCat at 3:20 PM on April 21, 2009

Best answer: My guess is that unused columns get optimized out and so the functions aren't called.

But test this; write a function that sets some observable state, run the query without the column that calls that function, and then look at the state.

One other note: your function names suggest that you could use subqueries rather than functions for most of them. Also, what's the point of prefixing all functions with 'fcn'?
posted by orthogonality at 3:24 PM on April 21, 2009

Response by poster: thanks orthogonality: I was hoping that unused columns would be optimized out, but when I used EXPLAIN PLAN this didn't seem to be the case. That's a good suggestion (setting an observable state) I shall give it a shot.

Re. function names: I just pulled the example data out of my proverbial. Some of them will be sub-queries while some will be calling horribly substantial PL/SQL functions. I prefixed them with "fcn" so that it was explicit in my example that they were functions. This isn't representative of the actual DB--fyi, it's nothing to do with products, orders.
posted by NailsTheCat at 3:56 PM on April 21, 2009

Best answer: I'd trust EXPLAIN PLAN, though that seems to jive with this, which indicates it'll be computed only when queried. If it does get evaluated even when not queried, making a view with the virtual column seems like a better plan to me.

I don't really know Oracle, but it seems like if there's a possibility of the expression changing state, they'd make a big deal out of exactly when it was evaluated. Sure it's not in the docs?
posted by devilsbrigade at 4:30 PM on April 21, 2009

Response by poster: thanks devilsbrigade: I'm using 10g which doesn't support Virtual Columns (very nice they look too). However, I think you're right and the same logic applies to regular views, I just found this article. (Haven't found anything in any documentation yet.)

Following on from orthogonality's suggestion I tried updating some data within a function called by one of my view's columns. Oracle won't actually let you do this through a SELECT and raises an error (makes sense). If I exclude the offending column from my query I don't get the error and therefore it's pretty clear that the column is not considered. Which makes far more sense to me (although it contradicts what I was told by a DBA today...).

Thanks all.
posted by NailsTheCat at 5:12 PM on April 21, 2009

Response by poster: As an addendum, while functions are not evaluated, obviously if the query upon which the view is based uses joins, those must be evaluated and therefore there are always some performance implications.
posted by NailsTheCat at 1:19 PM on May 23, 2009