Re: Query with concatenation in explain plan
- From: Shakespeare <whatsin@xxxxxxxxx>
- Date: Sat, 29 Nov 2008 13:57:08 +0100
Jonathan Lewis wrote:
"Shakespeare" <whatsin@xxxxxxxxx> wrote in message news:49312a68$0$186$e4fe514c@xxxxxxxxxxxxxxxxxI have a customer who has a bad-performing query, and the explain plan 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
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.
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
.
- References:
- Query with concatenation in explain plan
- From: Shakespeare
- Re: Query with concatenation in explain plan
- From: Jonathan Lewis
- Query with concatenation in explain plan
- Prev by Date: Re: Query with concatenation in explain plan
- Next by Date: Lot of row cache/library pin
- Previous by thread: Re: Query with concatenation in explain plan
- Next by thread: Re: Query with concatenation in explain plan
- Index(es):
Relevant Pages
|
