Thanks for the question, Freek.

Asked: October 03, 2002 - 10:46 am UTC

Last updated: June 20, 2011 - 1:36 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Sir,

I have following table:

CREATE TABLE individual_attributes
(
iat_ind_urn NUMBER(10) NOT NULL,
iat_inr_uid NUMBER(10) DEFAULT 0 NOT NULL,
iat_att_code VARCHAR2(5) NOT NULL,
iat_batch_number NUMBER(6),
iat_value VARCHAR2(60) NOT NULL,
iat_create_date DATE,
iat_create_user VARCHAR2(30),
iat_update_date DATE,
iat_update_user VARCHAR2(30)
)
/

ALTER TABLE individual_attributes
ADD CONSTRAINT iat_pk PRIMARY KEY (iat_ind_urn,iat_inr_uid,iat_att_code)
/

when iat_att_code = 'DLVDT', then the iat_value field contains a date in the format 'ddmmyyyy'.
For other values iat_att_code, non date values could be stored in the iat_value field.
Now for a query I need to select all the records where the iat_att_code = 'DLVDT' and that iat_value_field contains a date in july 2002.

To do this I use a query with an inline view that selects the records where iat_att_code = 'DLVDT' and converts the iat_value to a date.
In the parent query I compare the date with an other date.

select
*
from
(
select
trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
from
individual_attributes
where
iat_att_code = 'DLVDT'
) A
where
A.delivery_date = to_date('01/07/2002', 'dd/mm/yyyy');

I use this inline view because the order of evaluation of the where clause in a SQL statment is undefined (correct?).

This approach however does not seem to work (ORA-01858: a non-numeric character was found where a numeric was expected).
I now that the values in the iat_value field are correct, because when I just issue the inline query, everything works fine.

When I do an explain plan I get following result:

SQL> set autotrace traceonly explain
SQL> select
2 *
3 from
4 (
5 select
6 trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
7 from
8 individual_attributes
9 where
10 iat_att_code = 'DLVDT'
11 ) A
12 where
13 A.delivery_date = to_date('01/07/2002', 'dd/mm/yyyy')
14 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=145 Card=173 Bytes=2422)
1 0 TABLE ACCESS (FULL) OF 'INDIVIDUAL_ATTRIBUTES' (Cost=145 Card=173 Bytes=2422)

So it looks like the optimizer has merged the inline view and as a result of that the restriction ia_att_code = 'DLVDT' is not
certain to be applied first anymore.

When adding a no_merge hint, the query performed ok.

SQL> set autotrace traceonly explain
SQL> select
2 *
3 from
4 (
5 select
6 /*+ NO_MERGE */
7 trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
8 from
9 individual_attributes
10 where
11 iat_att_code = 'DLVDT'
12 ) A
13 where
14 A.delivery_date = to_date('01/07/2002', 'dd/mm/yyyy');

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=145 Card=173 Bytes=1038)
1 0 VIEW (Cost=145 Card=173 Bytes=1038)
2 1 TABLE ACCESS (FULL) OF 'INDIVIDUAL_ATTRIBUTES' (Cost=145 Card=173 Bytes=2422)

Question is how sure can I be that the optimizer will still not take the first execution plan, since a hint is nothing more than a hint.
Is there any other way to build this query, so that the to_date function only will be applied to the correct selection of the table?

Thanks

Freek D'Hooge

and Tom said...

the answer is -- you shouldn't. You should write the query so the order of evaluation is not relevant.

the order of evaluation should not have a bearing on your query -- in SQL the order of evaluation is purposely "not defined" -- i can show you a query using bottom up evaluation and then the same query using top down.

Your query could be written as:

SQL> select
2 *
3 from
4 (
5 select
6 trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
7 from
8 individual_attributes
9 where
10 iat_att_code = 'DLVDT'
11 ) A
12 where
13 A.delivery_date = to_date('01/07/2002', 'dd/mm/yyyy')
14 /

select trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
from individual_attributes
where iat_att_code = 'DLVDT'
and decode( iat_att_code, 'DLVDT',
trunc(to_date(iat_value, 'ddmmyyyy'), 'mm'), null ) =
to_date( '01/07/2002', 'dd/mm/yyyy' );

or, easier:

select trunc(to_date(iat_value, 'ddmmyyyy'), 'mm') delivery_date
from individual_attributes
where iat_att_code = 'DLVDT'
and iat_value = '01072002';

use CASE and/or DECODE to make it not matter what the order of evaluation is.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks

Freek D'Hooge, October 04, 2002 - 3:03 am UTC

I knew I should create the query independent from the order of evaluation, I just didn't know how to do it.
Never thought of using a decode.

thanks

Freek D'Hooge

Some other options

Connor McDonald, October 04, 2002 - 3:59 am UTC

Not really relevant in this case, but there is also an ORDERED_PREDICATES hint that can be used if required. I also recently saw a 9.2 example where the optimizer reordered the predicates based on the anticipated cpu cost of those predicates...very impressive indeed.

Order of evaluation in the where clause

Christian, June 20, 2011 - 5:47 am UTC

Sir,
I have a simple select in Oracle 10g:

select * from table where <condtions> and :VAR = 1
union all
select * from table where <condtions> and :VAR = 2

The problem is that the 2. select should stop immediately when it sees that the condition :VAR = 2 is not met.
But instead, I see in the execution plan that it continues to evalute the <conditions>, which includes subqueries, in both cases.
This has been noted by Burleson as well, see
http://www.dba-oracle.com/oracle_tips_ault_where_1_equals_2_parallel_.htm

Without ordered_predicates-hint, what can I do ? I tried to rewrite the query as one select using or, but this was very much slower.

June 20, 2011 - 1:36 pm UTC

No, it doesn't, we do employ short circuit evaluation.

However, you have no control, none, over the order of evaluation.

I'd be really interested how you can tell from a PLAN that it continues?

Please do not refer to that site, first and foremost, what is written there does NOT prove your point, nor does it even *support* your point. No where in there does it show that the entire predicate is evaluated - it only shows that in fact THE PREDICATE IS IN FACT evaluated. The page referenced does beg the question "why would one fetch from a query if the only goal was to get the column headings???". First, they would just prepare the query - get the column headings - and wouldn't want to or need to fetch from it ever - the full scan would NEVER happen.

Here is a "proof" that we short circuit:

ops$tkyte%ORA11GR2> create table t ( x varchar2(11) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ('hello world');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t where 1=0 and to_number(x) > 0;

no rows selected

ops$tkyte%ORA11GR2> select * from t where 1=1 and to_number(x) > 0;
select * from t where 1=1 and to_number(x) > 0
                              *
ERROR at line 1:
ORA-01722: invalid number

the first query would have failed. Here is further evidence that we short circuit and don't even EVALUATE a query if we know the predicate is false:



ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable var number
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where object_id = 42 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 1
  2  union all
  3  select * from t where object_id = 43 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 2
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2603986468

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |   184 |   865   (1)| 00:00:11 |
|*  1 |  HASH JOIN SEMI       |                    |     1 |   184 |   865   (1)| 00:00:11 |
|   2 |   VIEW                | VW_JF_SET$5D76B708 |    24 |  4104 |   576   (1)| 00:00:07 |
|   3 |    UNION-ALL          |                    |       |       |            |          |
|*  4 |     FILTER            |                    |       |       |            |          |
|*  5 |      TABLE ACCESS FULL| T                  |    12 |  1896 |   288   (1)| 00:00:04 |
|*  6 |     FILTER            |                    |       |       |            |          |
|*  7 |      TABLE ACCESS FULL| T                  |    12 |  1896 |   288   (1)| 00:00:04 |
|   8 |   TABLE ACCESS FULL   | T                  | 65315 |   829K|   288   (1)| 00:00:04 |
--------------------------------------------------------------------------------------------

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

   1 - access("T2"."OBJECT_ID"="ITEM_1")
   4 - filter(TO_NUMBER(:VAR)=1)
   5 - filter("OBJECT_ID"=42)
   6 - filter(TO_NUMBER(:VAR)=2)
   7 - filter("OBJECT_ID"=43)

Note
-----
   - dynamic sampling used for this statement (level=2)


<b>See those filters?  they cause a pruning to happen - dynamically - at runtime.  We can see this when we run the query:</b>


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :var := 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> select * from t where object_id = 42 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 1
  2  union all
  3  select * from t where object_id = 43 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 2
  4  /

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS    

<b>In the tkprof you will find something like this:</b>

select * from t where object_id = 42 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 1
union all
select * from t where object_id = 43 and exists (select null from t t2 where t2.object_id = t.object_id) and :var = 2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0         10          0           0
Fetch        2      0.05       0.05        761       1037          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.07        761       1047          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 243
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  HASH JOIN SEMI (cr=1037 pr=761 pw=0 time=54707 us cost=865 size=184 card=1)
         1          1          1   VIEW  VW_JF_SET$5D76B708 (cr=1033 pr=761 pw=0 time=54362 us cost=576 size=4104 card=24)
         1          1          1    UNION-ALL  (cr=1033 pr=761 pw=0 time=54357 us)
         1          1          1     FILTER  (cr=1033 pr=761 pw=0 time=54336 us)
         1          1          1      TABLE ACCESS FULL T (cr=1033 pr=761 pw=0 time=54328 us cost=288 size=1896 card=12)
         0          0          0     FILTER  (cr=0 pr=0 pw=0 time=3 us)
         0          0          0      TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us cost=288 size=1896 card=12)
        53         53         53   TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=92 us cost=288 size=849095 card=65315)

See the "TABLE ACCESS FULL T (cr=0 pr=0 ..." bit? We DID NOT READ THAT TABLE, not at all.

and - you would see the SAME EXACT THING in 9i, 10g - 11g.

The referenced article is actually wrong on many levels. They actually proved themselves wrong but don't seem to know it. They wrote:

It is this second case, “WHERE 1=2” I want to talk about. Of course since 1 never equals 2 you never get a result back, however it forces a full table scan! In testing with 10gR1 version 10.1.0.4 and on 9iR2, 9.2.0.4

then later they "show" :

SQL> select count(object_id) from test where 1=2;

COUNT(OBJECT_ID)
----------------
0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (FAST FULL SCAN) OF 'TEST_IDX' (INDEX) (Cost=23
Card=50115 Bytes=200460)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
310 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

which SHOWS that the database didn't full scan. And they go on to show that over and over.

Then they erroneously claim:

In actuality it seems to be another case of Oracle explain plans not actually matching what is happening in the database.

But - do you see that filter step in the plan??? That filter step is 1=2, we KNOW we don't have to do the subtree, we don't do the subtree. Where is the plan wrong? It shows what is happening, the autotrace shows it is happening (or not happening actually)

What is funny though, is this:

What we ended up doing in this clients case was to replace the “WHERE 1=2” with a clause that equated the primary key of the table with an impossible value for that key, in this case the ID was being passed in as a GUID (a hexadecimal value) so we use a “WHERE KEY=HEX(00)” and got a low cost unique index lookup instead of a costly full table scan.

They replaced "nothing" with "something"

They took a query that DID NOTHING, NO WORK AT ALL

and replaced it with a query that had to do work.

Think about that for a minute.

Who cares about the cost of a query? I care about the amount of work done by said query. I'd prefer a query that does NOTHING versus a query that does SOMETHING - if I was just getting column headings....

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thanks

Some other options

Order of evaluation in the where clause

More to Explore

Performance

how to set the order of the evaluation of the where clause

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thanks

Some other options

Order of evaluation in the where clause

More to Explore

Performance