RE: Stop Criteria for Sql Execution
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 7 Mar 2005 12:16:20 -0500
The filter step did not show up in 9.2.0.4 without all_rows hint.
It shows up only if the table is partitioned (even on another column).
Waleed
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]=20
Sent: Monday, March 07, 2005 11:57 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Stop Criteria for Sql Execution=20
This is 9.2.0.6 (I don't have 8.1 available at the moment)
with autotrace on.
Select /*+ all_rows */ * from t1 Where n1 =3D 20 and n1=3D10
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: ALL_ROWS (Cost=3D2 Card=3D1
Bytes=3D13)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=3D2 Card=3D1 Bytes=3D13)
Note the filter line: In this case the filter
line stops the tablescan from taking place
because Oracle sees the contradiction.
(The filter is: n1 =3D 10 and n1 =3D 20)
Select /*+ all_rows */ * from t1 Where n1 is null and n1=3D10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DHINT: ALL_ROWS (Cost=3D2 Card=3D1
Bytes=3D13)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=3D2 Card=3D1 Bytes=3D13)
No filter - ouch !
That's just one of the nasty little things with nulls.
Even after
alter table t1 modify n1 not null;
we don't get a filter.
It's quite funny what happens if you create the
null constraint and then check the query:
Select /*+ all_rows */ * from t1 Where n1 is null and n1 is not null;
The tablescan takes place, and every row is
tested for 'n1 is null'.
Proper execution plan (not autotrace)
Id Par Pos Ins Plan
---- ---- ---- ---- ----------------
0 4 SELECT STATEMENT (hint: all_rows)
1 0 1 1 TABLE ACCESS (analyzed) TEST_USER T1 (full)
Filter=20
("T1"."N1" IS NULL)
Note how the filter (after the row fetch) has
eliminated the 'is not null' predicate as redundant
because there is an 'is not null' constraint, and STILL
tests the predicate 'is null'.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
----- Original Message -----=20
From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 07, 2005 4:38 PM
Subject: Stop Criteria for Sql Execution
Why does Oracle have to execute the sql and scan data in a table for a
sql like this:
Select * from table
Where column1 is null and column1=3D3D10
Or=3D20
Select * from table
Where column1=3D3D10 and column1=3D3D20
All the research money going to optimize the optimizer, and still can't
take care of simple stuff like this.
Am I missing something?
I will probably start searching for smarter databases :)
Waleed
--
//www.freelists.org/webpage/oracle-l=20
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
