Query with concatenation in explain plan
Shakespeare
shows a split in the query with a concatenation at the end. Because the
query is fairly complex, the two parts of the query take a lot of time,
where (to my opinion) the whole part could be done in one run.
I stripped the query to the point where the concatenation comes in. In
the real query, it is joined with a lot of extra tables, of which some
are full table scans (still have to tune that part).
This is what it boils down to this (the query is generated by a software
package, so I can't change them without changing the code of the package):
SELECT mtb.*
FROM MYTABLE mtb
WHERE mtb.dt_begin between nvl(:b14,mtb.dt_begin) and
nvl(:b13,mtb.dt_begin)
Plan
SELECT STATEMENT ALL_ROWS
Cost: 748 Bytes: 275,550 Cardinality: 2,505
6 CONCATENATION
2 FILTER
1 TABLE ACCESS FULL TABLE APP.MYTABLE
Cost: 727 Bytes: 250,470 Cardinality: 2,277
5 FILTER
4 TABLE ACCESS BY INDEX ROWID TABLE APP.MYTABLE Cost: 22 Bytes:
25,080 Cardinality: 228
3 INDEX RANGE SCAN INDEX APP.mtb_IX_DT_BEGIN
Cost: 3 Cardinality: 41
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Looks like a QBE where a user can put in date limits. If nothing is
entered, all rows are returned.
I noted that the problem is in the NVL's. A normal 'between' would use
the index in one run. But still: a concatenation suggests that rows that
are returned in the first clause and rows that are returned in the
second are put together in the result set, where I would think only rows
that are returned by BOTH clauses should be i the result set.
Fron the docs: Concatenation = An operation that accepts multiple sets
of rows and returns the union-all of the sets.
Am I missing something here?
Shakespeare
Jonathan Lewis
news:49312a68$0$186$e4fe...@news.xs4all.nl...
Your stripped example shows the optimizer producing a
run-time option on execution plans. It's an example of the
type of thing I've described in the following note:
http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/
At run time, only one of the two parts of the plan will operate
because the two FILTERs are filtering constants, and are mutually
exclusive.
In your case, the "good" part of the plan may be bad because you
have multiple conditions of the same type (for different columns in
different tables) and the optimizer can only play this concatenation
trick once. The link above has a follow-on link saying more about
that issue.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Shakespeare
Right, I did miss something here... FILTER!
If I strip this to:
SELECT mtb.*
FROM mytable mtc
WHERE mtb.dt_begin <= nvl(:b14,mtb.dt_begin)
I still get the concatenation:
Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,094 Bytes: 5,511,220 Cardinality: 50,102 6 CONCATENATION
2 FILTER
1 TABLE ACCESS FULL TABLE APP.MYTABLE
Cost: 727 Bytes: 5,010,170 Cardinality: 45,547
5 FILTER
4 TABLE ACCESS BY INDEX ROWID TABLE APP.MYTABLE
Cost: 366 Bytes: 501,050 Cardinality: 4,555
3 INDEX RANGE SCAN INDEX APP.MTB_IX_DT_BEGIN
Cost: 3 Cardinality: 820
It's not a concatenation between <= and >= but between null and not null
of :b14.
What is strange and misleading though is that the costs and cardinality
of both parts add up; where it should be either the first or the second....
Shakespeare
Shakespeare
Jonathan,
thank you for your response. I just concluded I missed that filter part
(see my next post...) before reading your response. I'll check out the
link. It seems to be bad programming: replacing the column<=column part
with column <= "a very large value" seems to perform better.
This application has a lot of constructs like this in one query though,
and unfortunately, each and everyone generates a concatenation in the
plan, which, as you stated, leads to bad performance because of the
concatenation trick being played once.
Shakespeare
Dion Cho
It is a designed feature which is controlled by
"_or_expand_nvl_predicate" hidden parameter.
Dion Cho
Shakespeare
Shakespeare