Re: Conditional FROM clause?

On Wed, 29 Sep 2004 16:14:12 GMT, netcomradeNSPAM_at_bookexchange.net (NetComrade) wrote:

>All,
>
>We have a query that looks something like
>
>select t1.*, t2.*, pkg.func(t1.col1.....)
>from table1 t1, table2 t2
>where t1.id=t2.id
>....
>
>the query retuns anywhere from 10 to 3000K rows.
>
>The function (pkg.func) does some lookups based on what the value is
>in col1 in other tables (e.g. if col1=1 look at table t3, if col1=2
>look at table t4, etc). So basically we have a query executing for
>each row.
>
>The query is actually fairly complex. We actually have a number of
>similar queries using similar func calls, which consistently show up
>as 'top' queries, and we have been known to be CPU bound in the past.
>I've had a few solutions in mind
>
>1) create an aggregate table that'll always return what the function
>should return
> the drawback is that such table might be very difficult to maintain
>(lots of triggers)
>

look into materialized views.

>2) for each value in col1 do a separate query and union them in the
>end (col1 #'s are limited)
> i am so far in 'favor' of such solution, but queries aren't going to
>be pretty
>
>3) do bunch of outer joins.. but the query becomes really ugly, and
>autroace becomes 'bad' and unreadable
>
>What's your advice, and/or other suggestion? Is it possible to have a
>conditional FROM clause in a query based on a column?

No, but of course you could always put the function call in an inline view referring to dual and join that inline view with the rest of the statement.

>
>Thanks
>.......
>We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>remove NSPAM to email

--
Sybrand Bakker, Senior Oracle DBA