| Interesting predicate found in explain plan [message #150362] |
Wed, 07 December 2005 09:34  |
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 #150574 is a reply to message #150362] |
Thu, 08 December 2005 08:35  |
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 |
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 #150930 is a reply to message #150752] |
Mon, 12 December 2005 01:20 |
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 |
Frank
Messages: 7901
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
