Home » SQL & PL/SQL » SQL & PL/SQL » where clause and execution plan
where clause and execution plan [message #434592] Wed, 09 December 2009 19:57 Go to next message
kang
Messages: 89
Registered: November 2007
Member
why case 1 and case 2 have different execution plan?

case 1.
select /*+ index_desc(pk_emp) */ max(empno) from emp

SELECT STATEMENT, GOAL = ALL_ROWS			2	1	13
 SORT AGGREGATE				1	13
  INDEX FULL SCAN (MIN/MAX)	DB_XDP	PK_EMP	2	15	195


case 2.
select /*+ index_desc(pk_emp) */ max(empno) from emp where empno<>''

SELECT STATEMENT, GOAL = ALL_ROWS			2	1	13
 SORT AGGREGATE				1	13
  FIRST ROW			2	1	13
   INDEX FULL SCAN (MIN/MAX)	DB_XDP	PK_EMP	2	1	13


Re: where clause and execution plan [message #434593 is a reply to message #434592] Wed, 09 December 2009 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>why case 1 and case 2 have different execution plan?
Because they are different SQL & the CBO evaluations produced different results.

Different SQL can produce same result set by utilizing different plans.

This is an example why folks should do SQL optimization & implement the SQL with the better plan.
Re: where clause and execution plan [message #434594 is a reply to message #434593] Wed, 09 December 2009 20:09 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
Thanks.

I know different sql produce different plans.

What I want to know here is why the meaningless(for me) where clause "where empno <> ''" produce the result.

How can I generalize this situation?
Re: where clause and execution plan [message #434596 is a reply to message #434594] Wed, 09 December 2009 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>What I want to know here is why the meaningless(for me) where clause "where empno <> ''" produce the result.
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

Dissect the resultant trace file for the answer.


>How can I generalize this situation?
CBO behaviour is version dependent.
I contend you would be foolish to try to generalize for any/every SELECT statement.

SILVER BULLETS only exist in fiction novels.

[Updated on: Wed, 09 December 2009 20:23]

Report message to a moderator

Re: where clause and execution plan [message #434597 is a reply to message #434592] Wed, 09 December 2009 20:28 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;
select /*+ index_desc(pk_emp) */ max(empno) from emp;
select /*+ index_desc(pk_emp) */ max(empno) from emp where empno<>'';
ALTER SESSION SET SQL_TRACE=FALSE;

SHOW PARAMETER user_dump_dest

[*]Now find the trace file within User Dump Dest folder (displayed from the SHOW PARAMETER command above], and type the following from the Operating System Prompt:
tkprof <trace_file.trc> trace_results.txt


Post contents of trace_results.txt back here nicely formatted.

[Updated on: Wed, 09 December 2009 20:39]

Report message to a moderator

Previous Topic: avoid error message when drop a non-existing table
Next Topic: Materialized View with for update
Goto Forum:
  


Current Time: Wed Sep 28 12:35:59 CDT 2016

Total time taken to generate the page: 0.09470 seconds