Home » RDBMS Server » Performance Tuning » Interesting predicate found in explain plan
Interesting predicate found in explain plan [message #150362] Wed, 07 December 2005 09:34 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
In the predicate section, I found this interesting filter condition:

2 - filter(NULL IS NOT NULL)

The result of course is:

MYDBA > select 1 from dual where null is not null;

no rows selected

MYDBA > select 1 from dual where null is null;

         1
----------
         1

Re: Interesting predicate found in explain plan [message #150466 is a reply to message #150362] Thu, 08 December 2005 00:31 Go to previous messageGo to next message
Frank
Messages: 7897
Registered: March 2000
Senior Member
In what context did you find it?
Re: Interesting predicate found in explain plan [message #150574 is a reply to message #150362] Thu, 08 December 2005 08:35 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmm, well now I can't find it after a quick look, will look again. It happened while going through examples and modifying some of them slightly from the CBO book by J Lewis.

In searching for it I saw this one:

MYDBA > select count(*) from audience where month_no in (1,2,null);

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |   300 |   900 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MONTH_NO"=1 OR "MONTH_NO"=2 OR
              "MONTH_NO"=TO_NUMBER(NULL))


I'll see if I can find the other...
Re: Interesting predicate found in explain plan [message #150576 is a reply to message #150362] Thu, 08 December 2005 08:41 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ahh I think this is it:

MYDBA > select count(*) from audience where month_no > 10 and month_no <= 10;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
Plan hash value: 57893822

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |     3 |     0   (0)|          |
|   1 |  SORT AGGREGATE     |          |     1 |     3 |            |          |
|*  2 |   FILTER            |          |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NULL IS NOT NULL)
   3 - filter("MONTH_NO">10 AND "MONTH_NO"<=10)



Allof this is based on working through his chapter 3...
Re: Interesting predicate found in explain plan [message #150681 is a reply to message #150576] Fri, 09 December 2005 00:56 Go to previous messageGo to next message
Frank
Messages: 7897
Registered: March 2000
Senior Member
Great, looks like there is some intelligence in the CBO Wink
Too bad it does it as a filter, only _after_ the FTS...

Frank
Re: Interesting predicate found in explain plan [message #150752 is a reply to message #150362] Fri, 09 December 2005 07:40 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Actually you raise a good point. Along the same lines, I should trace this and see the tkprof plan that is actually used come runtime to see if it does the same thing as the explain plan predicts.
Re: Interesting predicate found in explain plan [message #150930 is a reply to message #150752] Mon, 12 December 2005 01:20 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm on 10.1, and I do get the FILTER step, but the condition is "10<10".

It actually acts as a short-circuit BEFORE the FTS, not after; so I get no blocks read in my stats:
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1257  bytes sent via SQL*Net to client
        373  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


_____________
Ross Leishman
Re: Interesting predicate found in explain plan [message #150934 is a reply to message #150930] Mon, 12 December 2005 01:36 Go to previous messageGo to next message
Frank
Messages: 7897
Registered: March 2000
Senior Member
You are right Ross, on my 10.2 beta (XE) it shows the same behavior (except that i get the filter(null is not null)), it seems to filter before the fts-access.

statement with index:
select *
from   dept
where  deptno < 10
and    deptno >= 10

or to force FTS:
select *
from   dept
where  deptno+0 < 10
and    deptno+0 >= 10


Frank

[Updated on: Mon, 12 December 2005 01:38]

Report message to a moderator

Re: Interesting predicate found in explain plan [message #151008 is a reply to message #150362] Mon, 12 December 2005 10:23 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
So it looks like an optimization made after the parse and costing is done by the runtime optimizer itself. The fact that 0 blocks will be read is not taken into account by the CBO itself, causing the plan to have a higher cost than actual work.

This seems to fit in with one of the points Lewis has brought up in his book a couple times.
Previous Topic: How to tune a sql query to make it run faster ...
Next Topic: SQL - very slow response
Goto Forum:
  


Current Time: Tue Dec 06 21:37:09 CST 2022