Re: Query with concatenation in explain plan
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 29 Nov 2008 13:57:08 +0100
Message-ID: <49313c25$0$189$e4fe514c@news.xs4all.nl>
>> I 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
>>
Date: Sat, 29 Nov 2008 13:57:08 +0100
Message-ID: <49313c25$0$189$e4fe514c@news.xs4all.nl>
Jonathan Lewis wrote:
> "Shakespeare" <whatsin_at_xs4all.nl> wrote in message > news:49312a68$0$186$e4fe514c_at_news.xs4all.nl...
>> I 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 Received on Sat Nov 29 2008 - 06:57:08 CST