CODEWITHSUNDEEP
databaseoracle-databasesql
Himz
Himz

Reputation: 523

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 queries actually get executed.

Thanks Himanshu

Upvotes: 1

Views: 481

Answers (6)

Ronnis
Ronnis

Reputation: 12833

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.

Upvotes: 0

EvilTeach
EvilTeach

Reputation: 28837

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.

Upvotes: 0

milan
milan

Reputation: 63

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.

Upvotes: 2

erbsock
erbsock

Reputation: 1217

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.

Upvotes: 1

Martin Schapendonk
Martin Schapendonk

Reputation: 13506

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

Upvotes: 2

Thilo
Thilo

Reputation: 262554

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.

Upvotes: 2

Related Questions