Re: Query with concatenation in explain plan
- From: Shakespeare <whatsin@xxxxxxxxx>
- Date: Sat, 29 Nov 2008 13:49:25 +0100
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
.
- References:
- Query with concatenation in explain plan
- From: Shakespeare
- Query with concatenation in explain plan
- Prev by Date: Re: process running slowly, lots of yield() calls ?
- Next by Date: Re: Query with concatenation in explain plan
- Previous by thread: Re: Query with concatenation in explain plan
- Index(es):
Relevant Pages
|
