Sql query with where clause execution

I have a query like :

(q1)
select a,b,c,d from abc
where param='x'

union

(q2)
select e,f,g,h from abc
where param='y'

I want to know if value of <param>='y' would query1 get executed ?? This is because the recodset "abc" is very very big and actual query involves 5-6 unions on the same parameter(u may see that only one query data is being needed at a time). So if the data is being fetched from all the queries and the filtered according to where clause then it would be a big overhead, whereas if filtered before then only one out of 5 querie开发者_开发技巧s actually get executed.

Thanks Himanshu

---

If you write something like

where 1 = 2

which can be evaluated without touching the database, then Oracle will be clever enough to skip accessing the tables.

This should even work with bind variables.

where ? = ?

Of course, as soon as columns are involved, it will have to go look at the data.

---

If 'param' is a column in abc, it would help greatly when you index that column.

But the biggest performance impact in your query is probably the 'union', because Oracle has to filter out duplicate rows. Depending on the size of your result set, this is quite a heavy operation (sorting, removing duplicates). If you're not concerned with duplicate results (or if they're simply impossible because of the definition of the queries), use 'union all':

select a,b,c,d from abc
union all
select e,f,g,h from abc

---

select *
from (
select 1 INDICATOR,
       a, b, c
from abc
union all
select 2 INDICATOR,
       a, b, c
from abc)
where indicator = 1;

This won't execute the second query in the union. As you can see in the execution plan, there is a Filter which says "null is not null". However there is a significant overhead with many unions.

---

In my opinion this is lazy programming and you are trying to make the database do the application's job. A simple "if" statement with a bunch of concatenations is all you need.

---

The question suggests to me, that you want to avoid two full table scans over the data set. This technique might help you.

WITH
base AS
(
    SELECT a,b,c,d,e,f,g,h FROM abc -- all columns of interest
    WHERE param IN ('x', 'y')       -- all rows of interest
),
q1 AS
(
    select a,b,c,d from base        -- one specific subset
    where param='x'
),
q2 AS
(
    select e,f,g,h from base        -- the other specific subset
    where param='y'
)
SELECT a,b,c,d FROM abc             -- then the union of the sets

UNION

SELECT e,f,g,h FROM abc             -- that you are interested in.

As you are doing lookups on param, an index would be of great value, allowing the FULL TABLE SCAN to be replaced by a less costly INDEX scan.

If the set of distinct values in param are small, it may be of benefit to build histograms.

As in all things Oracle, your mileage may vary.

I would be interested in hearing how it all turns out for you.

---

As others have pointed out, Oracle will remove dead code branches from your query after having evaluated the bind variable. You can replace the union with "union all" (not that it should matter anyway), but the intent of the developer is shown more clearly (I, the developer expected no duplicates here).

One open question (to me anyway) is: Can you know for sure that the data type of "a" matches "e" and "b" matches "f" etcetera?

Actually, I have never implemeneted this myself, only read about it so someone want to validate what I'm saying. But a completely different approach could be to create a stored procedure which takes the parameter. The procedure would have one cursor for each expected value of param. Then you can test (IF) the value of the param and return a ref cursor.