Re: Conditional FROM clause?

Hiding complexity of selects behind PL/SQL functions calls from SQL will degrade performance because:

1> optimizer does not know how to optimize SQL with embedded functions 2> "context switching" beetwen SQL and PL/SQL

You should play with design of that data model (if possible) to run the most frequent operations faster and also you can try some non-standard Oracle features to reduce logical IO as for example IOT (Index Organised Tables), FBI (Function Based Index), Clusters, Materialised Views, Analytic Functions etc...

and with tuning application logic and having good statistics on segments you should let CBO to do his job.

Jan

Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:<98sll0hagd2g0dso9fcm2d7mgkobo8h1pe_at_4ax.com>...
> 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
Received on Thu Sep 30 2004 - 02:06:42 CDT