Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mihai.

Asked: April 15, 2014 - 6:03 pm UTC

Last updated: April 15, 2014 - 10:00 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I faced with a question and I was in doubt about the correct answers. In my opinion, not just two answers are possible, even if the question was "which two tasks are performed during the optimization stage of a sql statement"
And here is the list of possible answers:

a - evaluating the expressions and conditions in the query
(in my opinion this is correct, as I can see here http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm#40574 )

b - checking the syntax and analyzing the semantics of the statement
(this is false, as this is done in the parse stage, and the result of the parse stage which consists of a set of query blocks is then sent to the optimizer)

c - separating the clauses of the sql statement into structures that can be processes
(not sure about what "structures that can be processed" really are)

d - inspecting integrity constraints and optimizing the query based on this metadata
(correct? as per http://docs.oracle.com/cd/E16655_01/server.121/e17749/schemas.htm#DWHSG8151 , this option should be correct)

e - gathering the statistics before creating the execution plan for the statement
(not really clear...they can be gathered automatically or not, but not implicitly gathered automatically).

I was in doubt here.. any suggestions?

Thank you!

and Tom said...

Here is my input on this (the statements are a bit vague, I'm guessing this was an interview question... or a multiple guess question on a test of some sort...)


a) This can be *partially* true. If the expressions/conditions are able to be statically analyzed (evaluated fully and deterministically at compile time) - then the optimizer may well do that.

However, in almost all "real" cases, this is not the case. The optimizer will build a plan, compile a program that evaluates these expressions and conditions at run time - well after optimization.

for example:

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from dual where 1+2 > 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)


here we can see the optimizer evaluated the expression 1+2 and then evaluated the condition 3>5 and determined "this will always be false". The way the optimizer expresses "falseness" in a plan is to use the filter "null is not null". it will turn any condition it can into "null is not null", which compiles as "false" - we know it is not true.

ops$tkyte%ORA11GR2> select * from dual where upper(dummy) = upper('x');

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(UPPER("DUMMY")='X')


ops$tkyte%ORA11GR2> set autotrace off


Here we see that the expression upper(dummy) cannot be evaluated, but the expression upper('x') was. The condition "upper(dummy) = 'X'" cannot be evaluated - so the optimizer would just generate code to have the expression and the condition evaluated at runtime.



I say this one is "fuzzy" because the person that wrote this as an answer is probably expecting to hear "yes, the optimizer evaluates expressions and conditions" - but really means "the optimizer will compile your expressions and conditions into executable code"



b) Not the optimizer. this is a parse operation, yes. it is the first step in soft parsing(no optimization) and hard parsing (will eventually optimize).

c) Not the optimizer. that sounds like parsing again, if you ask me. but it is a bit fuzzy again.

d) absolutely part of the optimization process

e) Yes, the optimizer can do this, so this would be something that MIGHT happen. Dynamic sampling - http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html




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

More to Explore

Performance

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