I've got 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 wish to determine if worth of
<param>='y' would query1 get performed ??
The reason being the recodset "abc" is extremely very large and actual query involves 5-6 unions on a single parameter(u could see that just one query information is being needed at any given time). Therefore if the information has been fetched all the queries and also the strained based on where clause then it might be a large overhead, whereas if strained until then only one inch 5 queries really get performed.
Should you write something similar to
where 1 = 2
which may be examined without touching the database, then Oracle is going to be clever enough to skip being able to access the tables.
This will work with bind variables.
where ? = ?
Obviously, the moment posts are participating, it'll have to visit consider the data.
If 'param' is really a column in abc, it might help greatly whenever you index that column.
However the greatest performance impact inside your totally most likely the 'union', because Oracle needs to remove duplicate rows. With respect to the size your result set, this is a reasonably heavy operation (sorting, getting rid of replicates). If you are not worried about duplicate results (or maybe they are simply impossible due to the phrase 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 will not execute the 2nd query within the union. As you can tell within the execution plan, there's a Filter which states "null isn't null". However there's a substantial overhead with lots of unions.
For me this really is lazy programming and you're simply attempting to make the database perform the application's job. An easy "if" statement with a lot of concatenations is all that's necessary.
The question indicates in my experience, that you would like to prevent two full table scans within the data set. This method might assist you to.
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.
When you are doing searches on param, a catalog could be of great value, permitting the entire TABLE SCAN to become changed with a less pricey INDEX scan.
When the group of distinct values in param are small, it might be of great benefit to construct histograms.
As with everything Oracle, your mileage can vary.
I'd want to consider hearing the way it all works out for you personally.
As others have stated, Oracle will remove dead code branches out of your query after getting examined the bind variable. You are able to replace the union with "union all" (not too it will matter anyway), however the intent from the developer is proven more clearly (I, the developer expected no replicates here).
One open question (in my experience anyway) is: Are you able to know without a doubt the data kind of "a" matches "e" and "b" matches "f" etcetera?
Really, I have not implemeneted this myself, only find out about it so someone wish to validate what I am saying. But a totally different approach would be to produce a saved procedure that takes the parameter. The process might have one cursor for every expected worth of param. You'll be able to test (IF) the need for the param and return a ref cursor.