Conditional FROM clause?

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)
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?

Thanks
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Sep 29 2004 - 11:14:12 CDT