Query with NULL
bob123
I have a lot of queries like below:
>select * from bxat.no5
>WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
>AND (tbname = :wp19 OR :wp19 IS NULL)
so an access full on no5
How can I speed up this query ?
Thanks in advance
(Oracle 9.2.0.6)
jefftyzzer
Bob,
If the issue is that the optimizer is choking on the ORs you might,
barring adding indexes, want to consider changing your query from the
present set of ORs to a set of simpler UNIONs, e.g.,
select *
from bxat.no5
WHERE no0_session_id = :wp18
UNION
select *
from bxat.no5
WHERE
wp18 IS NULL
UNION
.
.
.
--Jeff
vsevolod afanassiev
select * from bxat.no5
WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
We assume that there is an index on "no0_session_id" and this index
has good selectivity.
If :wp18 has a value then we want the query to use index. If :wp18 is
NULL then all rows in the table
satisfy WHERE clause so we want the query to use full table scan.
Let's simplify the query even further and look at the plans:
SQL> select * from bxat.no5 where no0_session_id = :wp18;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1
Bytes=13)
2 1 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3
Card=1)
SQL> select * from bxat.no5 where :wp18 is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=42845620
Bytes=85691240)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=42845620
Bytes=85691240)
Note FILTER above. You get the same plan if you run query with
condition "1=0". While plan contains full table scan Oracle doesn't
actually run it if condition is false - it is stopped by FILTER.
Now the original simplified query:
SQL> select * from bxat.no5 where no0_session_id = :wp18 or :wp18 is
null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=2142282
Bytes=27849666)
1 0 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=2142282
Bytes=27849666)
Here "FILTER" disappeared, so Oracle always runs the full scan.
Finally the version with UNION:
SQL> select * from bxat.no5 where no0_session_id = :wp18
2 union
3 select * from bxat.no5 where :wp18 is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156184 Card=42845621
Bytes=85691253)
1 0 SORT (UNIQUE) (Cost=156184 Card=42845621 Bytes=85691253)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1
Bytes=13)
4 3 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3
Card=1)
5 2 FILTER
6 5 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653
Card=42845620 Bytes=85691240)
So the trick is to keep the FILTER.
Randolf Geist
One possible way to achieve this without the explicit usage of UNION
suggested is the rewrite with NVL or DECODE as outlined here:
http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/
Which would look something like this for your particular query:
select
*
from
bxat.no5
WHERE
no0_session_id = nvl(:wp18, no0_session_id)
AND tbname = nvl(:wp19, tbname)
The optimizer can then transform this using a concatenation
transformation to make potential use of an index.
Note however that this optimisation doesn't perform multiple
concatenation transformations for multiple similar predicates, as can
be seen here:
http://jonathanlewis.wordpress.com/2007/02/14/conditional-sql-2/
so it might not work that well in your particular case.
and you need to be aware that this re-write is not exactly the same as
your statement if the columns are nullable since the re-write filters
out any rows where the column is null whereas your original statement
does not.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
Mark D Powell
Being that :wp18 and :wp19 are program bind variables then depending
on which variables have a value they are actually 4 different
conditions: return all rows when neither variable has a value, return
matching rows for wp18 when wp19 is null,, return matching rows for
wp19 when wp18 is null, and return only rows that match both wp18 and
wp19 when both have a value.
While one query could be written to handle this set of requirements, I
would consider to make the correct desired result clear for future
maintenance programmers just coding a simple SQL statement for each
condition within a IF structure within the program. That way, based
on which variables have values the correct desired SQL statement is
submitted.
I think taking the simple approach would likely greatly reduce the
chance the CBO chooses anything but the optimal plan for the submitted
query and again the simplier SQL will be unlikely to confuse any
future maintenance programmer as to what result is desired.
Which approach is best is going to depend on the environment:
additional complexity in where clause conditions, how static the
application is expected to be, etc.... It may be that each approach
will prove beneficial depending on the program in question.
HTH -- Mark D Powell --
joel garry
>
> While one query could be written to handle this set of requirements, I
> would consider to make the correct desired result clear for future
> maintenance programmers just coding a simple SQL statement for each
> condition within a IF structure within the program. That way, based
> on which variables have values the correct desired SQL statement is
> submitted.
>
> I think taking the simple approach would likely greatly reduce the
> chance the CBO chooses anything but the optimal plan for the submitted
> query and again the simplier SQL will be unlikely to confuse any
> future maintenance programmer as to what result is desired.
I'd go for a CASE over IF for future maintenance, if it is a tossup.
Had one of those yesterday, was so glad it was CASE - took 5 minutes,
most of which was source control/comments, would have needed much more
work if IF's. The issue is often that over time, the IF's get more
complicated - people can't resist nesting and ELSE. This usually
arises from changing business rules to account for all the situations
not thought of (or admitted) originally. I know, you'd think there
would be no difference since they can be logically equivalent... but
programmer behavior removes the equivalency.
jg
--
@home.com is bogus.
http://100waystokillapeep.blogspot.com/2009/04/psycho-bunny-killer.html
bob123
The problem is that i can't rewrite the code
it's a third party software ...
any clue ?
Robert Klemme
Difficult... You can't easily use stored outlines because those would
be optimal for a single of the four cases. You may get away with
outlines in four different categories but then you somehow need to
switch between them before the statement is executed. I guess
everything you attempt will soon get messy. Is there no chance to get
the vendor of the application to change it? Maybe you can file it as
bug and get a resolution via your support contract.
Good luck!
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
Mladen Gogala
>>select * from bxat.no5
>>WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) AND (tbname = :wp19 OR
>>:wp19 IS NULL)
I would advise against checking whether an external variable is NULL in
the SQL. I am aware that Hibernate sometimes does that, but that can be
reworked.
Mladen Gogala
> Being that :wp18 and :wp19 are program bind variables then depending on
> which variables have a value they are actually 4 different conditions:
> return all rows when neither variable has a value, return matching rows
> for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is
> null, and return only rows that match both wp18 and wp19 when both have
> a value.
Plus the fact that the job checking whether an external variable is null
really belongs to the programming interface and not the database.
Mark D Powell
Since the OP has now informed us the SQL is part of a package and
cannot be changed I think the OP's options are limited. Stored
outlines might be of use but I think it is unlikely since one possible
condition in the SQL as posted requires a full table scan that I do
not think there is any way around without changing the SQL.
I suggest complaining to the vendor about the manner in which the SQL
is written and its effect on performance. I have some limited
experience with DB2 UDB and a liittle more experience with SQL Server
so I believe it is safe to say that having the program issue simplier
SQL for each desired result set based on program variables having
values or not would be more effiicient in all three systems than
having to have that check made in the SQL. I think this last is
pretty much in agreement with Mgogla's previous post in that some
actions belong in the program and some in the database.
Malcolm Dew-Jones
: Hi,
1. Try adding the not null explicitly to each section, I seem to recall
that helps the optimizer recognize what to do.
select * from bxat.no5
WHERE
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
---------------------
AND
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)
---------------------
then you could try using union. Someone suggested that though an
illustration of it didn't seem to use it correctly
select * from bxat.no5
WHERE
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
union
select * from bxat.no5
WHERE
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)
if the union helps but the main part of the query is complicated then put
it in a with clause (my example may have the syntax wrong)
with
( select * from bxat.no5
where complicated stuff in common
) as the_query
select * from the_query where
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
union
select * from the_query where
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)
Mladen Gogala
> Since the OP has now informed us the SQL is part of a package and cannot
> be changed I think the OP's options are limited.
In other words, the OP is screwed. Happy New Year.
Mladen Gogala
> Since the OP has now informed us the SQL is part of a package and cannot
> be changed I think the OP's options are limited. Stored outlines might
> be of use but I think it is unlikely since one possible condition in the
> SQL as posted requires a full table scan that I do not think there is
> any way around without changing the SQL.
In 10G there is DBMS_ADVANCED_REWRITE package which can be used to
rewrite the bad SQL even if there is no access to the source. The
procedure in the package is "DECLARE_REWRITE_EUIVALENCE". A brief
example is available here:
http://www.blogskies.com/2009/07/dbmsadvancedrewrite.html
Somewhat more detailed article is here:
http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php
Randolf Geist
> In 10G there is DBMS_ADVANCED_REWRITE package which can be used to
> rewrite the bad SQL even if there is no access to the source. The
> procedure in the package is "DECLARE_REWRITE_EUIVALENCE". A brief
> example is available here:
I haven't suggested this because in its present form in 10g (haven't
tested it yet in newer releases, but I think these restrictions still
apply) it has some limitations that don't make it suitable for OLTP
like applications. In particular it doesn't support SQLs that contain
bind variables and therefore it is probably not applicable to the
statements mentioned here.
See Metalink (sorry, MOS) Note 392214.1
Dion Cho has also two interesting articles about this package:
http://dioncho.wordpress.com/2009/03/06/optimizing-unoptimizeable-sql-dbms_advanced_rewrite/
http://dioncho.wordpress.com/2009/10/13/dbms_advanced_rewrite-and-dml/
Mladen Gogala
> See Metalink (sorry, MOS) Note 392214.1
I'm aware of that paper, but that stinking pile of ... stuff ain't
working... again.
>
> Dion Cho has also two interesting articles about this package:
>
> http://dioncho.wordpress.com/2009/03/06/optimizing-unoptimizeable-sql-
dbms_advanced_rewrite/
>
> http://dioncho.wordpress.com/2009/10/13/dbms_advanced_rewrite-and-dml/
>
> Regards,
> Randolf
I wasn't aware of these two excellent articles. Thanks again.
Gerard H. Pille
That should be a "UNION ALL".
a. performance (saves an expensive SORT)
B. to get all rows, even if there are duplicates
jefftyzzer
With respect, while I readily agree that UNION ALL is likely faster
given that it obviates the need for a duplicate-eliminating sort, I
think the UNION is necessary precisely because it eliminates
duplicates. My goal was to suggest an alternate syntax that was
semantically equivalent to the OP's original query, which wouldn't
have returned duplicate rows given that it made only a single pass
through the "bxat.no5" table (whether the "bxat.no5" table contains
duplicates is a different matter altogether).
Regards,
--Jeff
Frank van Bortel
no clue about the data model.
Speculating, of course, but I've seen these constructs before.
Bet ya those columns are NOT NULL.
--
Regards,
Frank van Bortel