Re: Query with concatenation in explain plan

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 Received on Mon Dec 01 2008 - 20:28:13 CST