Re: Query with concatenation in explain plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 29 Nov 2008 12:38:18 -0000
Message-ID: <cfCdncNkfvcqqqzUnZ2dnUVZ8rSdnZ2d@bt.com>


"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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Sat Nov 29 2008 - 06:38:18 CST

Original text of this message