Re: Query with concatenation in explain plan
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 02 Dec 2008 17:09:10 +0100
Message-ID: <49355dab$0$199$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
Thank you for the hint!
Date: Tue, 02 Dec 2008 17:09:10 +0100
Message-ID: <49355dab$0$199$e4fe514c@news.xs4all.nl>
Dion Cho schreef:
> On 11¿ù29ÀÏ, ¿ÀÈÄ9½Ã49ºÐ, Shakespeare <what..._at_xs4all.nl> wrote: >> Shakespeare wrote:
>>> 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
>> 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 > > It is a designed feature which is controlled by > "_or_expand_nvl_predicate" hidden parameter. > > > Dion Cho
Thank you for the hint!
Shakespeare Received on Tue Dec 02 2008 - 10:09:10 CST