Thanks for the question, Armando.

Asked: April 06, 2016 - 11:05 am UTC

Last updated: April 07, 2016 - 11:29 am UTC

Version: 11gr2

Viewed 10K+ times! This question is

You Asked

Dear Tom,

Yesterday we had a discussion at lunch regarding the performance impact of how the WHERE clause is constructed. The question was the following:

Assuming a variable @var that is an integer and has a value of 0 (zero).

What is the best scenario?

a) SELECT (...) WHERE @var = 0 or table.field = @var

b) SELECT (...) WHERE table.field = @var or @var = 0

My belief was, when executing the query, the second scenario would be as fast as the first one because when analyzing the query the database engine would to the necessary optimizations. My colleagues stated that it would indeed make a difference and scenario A would be a lot faster than B.

I've done a research on the internet and could not find an assertive solution regarding this question.

So, Tom... what is your opinion on this?

Thanks

and Connor said...

The order is of little consequence.

SQL> create table t as select * from all_Objects;

Table created.

SQL>
SQL>
SQL> variable b1 number
SQL>
SQL> exec :b1 := 12

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ gather_plan_statistics */ *
  2  from t
  3  where owner = 'SYS'
  4  and :b1 = 0;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7rmafp2t0ss2t, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where owner = 'SYS' and
:b1 = 0

Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |
|*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL| T    |      0 |   2611 |      0 |00:00:00.01 |
---------------------------------------------------------------------------

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

   1 - filter(:B1=0)
   2 - filter("OWNER"='SYS')


21 rows selected.

SQL>
SQL> select /*+ gather_plan_statistics */ *
  2  from t
  3  where :b1 = 0
  4  and owner = 'SYS';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  g5dnmv8qx9r7w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where :b1 = 0 and owner =
'SYS'

Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |
|*  1 |  FILTER            |      |      1 |        |      0 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL| T    |      0 |   2611 |      0 |00:00:00.01 |
---------------------------------------------------------------------------

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

   1 - filter(:B1=0)
   2 - filter("OWNER"='SYS')


21 rows selected.

You can see in both cases, we check the value of the bind variable first (1-FILTER) and then did not proceed.

*Within* expressions, you might get some value, for example:

select case when expensive_function and cheap_function then 'x' end

would be better expressed as

select case when cheap_function and expensive_function then 'x' end

because the moment "cheap" is false, we would not proceed onto "expensive"

Hope this helps.

Rating

(2 ratings)

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

Comments

Things differ from version to version

Kaushal Ruparel, April 07, 2016 - 10:29 am UTC

I once saw a scenario (in a book, written by a real time performance geek) that in 10g the query's where clause got evaluated in different order than what 11g evaluated and the query got into an infinite loop. I will post this example here, when I get free time.

But Oracle is smart enough, atleast in 11g, that it weighs the impact of various predicates and chose the execution order based on this. But for doing this, it requires statistics.

Take for example, if we are using user defined functions in where clause and we don't have statistics for those functions, then Oracle will apply those predicates in the same order in which it appears in the query. How do we address this? Check out adrian billington's blog, there he has described certain workarounds for this, including defining custom statistics for functions.

Hope this helps,
Kaushal Ruparel

April 07, 2016 - 11:29 am UTC

Yes, cpu costing has in the main gotten rid of the need for predicate ordering. Its also presumably why the "ordered_predicates" hint has disappeared.

Power of CPU Costing

Rajeshwaran Jeyabal, April 08, 2016 - 2:03 pm UTC

Here is a simple test case to demonstrate How the optimizer re-arranges the predicates (in case of cpu costing).

rajesh@ORA11G> create table t
  2  as
  3  select to_char( mod(rownum,20)) v1,
  4     rownum n1,
  5     mod(rownum,20) n2
  6  from all_objects
  7  where rownum <=3000;

Table created.

rajesh@ORA11G>
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

rajesh@ORA11G>
rajesh@ORA11G> explain plan set statement_id ='PLAN01'
  2  for
  3  select /*+ no_cpu_costing */ *
  4  from t
  5  where v1 = 1
  6  and n2 =1
  7  and n1=981;

Explained.

rajesh@ORA11G>
rajesh@ORA11G> explain plan set statement_id ='PLAN02'
  2  for
  3  select *
  4  from t
  5  where v1 = 1
  6  and n2 =1
  7  and n1=981;

Explained.

rajesh@ORA11G> select statement_id,id,operation,options,
  2         object_name,filter_predicates
  3  from plan_table
  4  order by statement_id,id;

STATEMENT_         ID OPERATION            OPTIONS    OBJECT_NAM FILTER_PREDICATES
---------- ---------- -------------------- ---------- ---------- ---------------------------------------------
PLAN01              0 SELECT STATEMENT
PLAN01              1 TABLE ACCESS         FULL       T          TO_NUMBER("V1")=1 AND "N2"=1 AND "N1"=981
PLAN02              0 SELECT STATEMENT
PLAN02              1 TABLE ACCESS         FULL       T          "N1"=981 AND "N2"=1 AND TO_NUMBER("V1")=1

4 rows selected.

rajesh@ORA11G>

With no-cpu costing, the optimizer evaluated the predicates as such. from a table of 3000 rows
a) perform 3000 implicit conversion to find 150 rows (having TO_NUMBER("V1")=1)
b) then from that 150 rows apply filter n2=1 to return 150 rows.
c) then finally "N1"=981 to just one row.

But with CPU Costing, the optimizer re-arranges the predicates like this.
a) apply n1=981 and just get one row
b) then do n2=1 to return just one row
c) then perform just one implicit conversion to return that row by TO_NUMBER("V1")=1

CPU Costing is available since Oracle 9i, so feel free to write the queries with required predicates in any order, let the optimizer find its best way to re-arrange them.

Thanks to optimizer for continuous evolution.

To know more about CPU costing, refer to
Book : Cost Based optimizer Fundamentals by Jonathan lewis
Chapter#2 : Tablescans
Topic: The power of CPU Costing
Page no# : 22

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Things differ from version to version

Power of CPU Costing

More to Explore

PL/SQL demos

PL/SQL docs

WHERE clause in query - does order really matter?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Things differ from version to version

Power of CPU Costing

More to Explore

PL/SQL demos

PL/SQL docs