Re: Is it the best SQL?

I believe the Oracle optimizers (both rule and cost) will optimize these queries down their most efficient forms before creating an execution plan. So in both cases I'd bet money that you'd get identical execution plans. This of it as doing a SELECT on a VIEW, which is just a stored subquery. In that case the optimizer has to do the exact same process of combining the queries into the most efficient form.

And yes, you can build queries on the fly, using the DBMS_SQL package. It works very nicely.

Kevin Lam wrote:
>
> Hi,
>
> I wonder Oracle will tune SQL by itself?
>
> For examples:
> 1. select * from (select * from a union select * from b) c
> where c.column_1 = 1
> 2. select * from a where column_1 = 1 union
> select * from b where column_1 = 1
> I think method 2 should be faster if Oracle does not tune it, am I
> right?
>
> Another case is:
> 1. select * from a, b
> where a.col_1 = b.col_1 and
> a.col_2 = 1
> 2. select * from a, b
> where a.col_2 = 1 and
> a.col_1 = b.col_1
> Which one is faster? I guess case 2!?
>
> BTW, is it possible to build a SQL(add a where cause condition, add a
> column, etc...) in runtime? (Am I dreaming?)
>
> Thank in advance!
>
> Kevin
Received on Tue Apr 14 1998 - 19:33:16 CDT