OraFAQ Forum: SQL & PL/SQL » optimizer hints

Home » SQL & PL/SQL » SQL & PL/SQL » optimizer hints

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

optimizer hints [message #244530]

Wed, 13 June 2007 04:04

hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai

Member

Hi all

What are Optimizer Hints?
and also how to use /*+ ALL_ROWS */

rgds
Hari Krishna

Report message to a moderator

Re: optimizer hints [message #244531 is a reply to message #244530]

Wed, 13 June 2007 04:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL Reference, section Using Hints

Regards
Michel

Report message to a moderator

Re: optimizer hints [message #244533 is a reply to message #244530]

Wed, 13 June 2007 04:18

hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai

Member

hi

I want to know the difference between these two sql statements

SQL> SELECT /*+ ALL_ROWS */ empno, ename, sal, job
  FROM emp
  WHERE empno = 7566;

and


SQL>select empno,ename,job from emp 
where empno=7566;

rgds
Hari Krishna

Report message to a moderator

Re: optimizer hints [message #244534 is a reply to message #244530]

Wed, 13 June 2007 04:28

hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai

Member

hi Michel
thanks for sending the link.
iam just checking the link where i got some doubt.
is there difference between those two sql statement i have given before?

Rgds
Hari Krishna

Report message to a moderator

Re: optimizer hints [message #244538 is a reply to message #244534]

Wed, 13 June 2007 04:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The first one gives a hint to the optimizer, the second one no.

Regards
Michel

Report message to a moderator

Re: optimizer hints [message #244583 is a reply to message #244534]

Wed, 13 June 2007 07:48

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

FOO SCOTT> set autotrace traceonly explain;
FOO SCOTT> select empno,ename,job from scott.emp
  2 where empno=7566;

Execution Plan
----------------------------------------------------------
Plan hash value: 3154838177

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    18 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |     1 |    18 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PRIMARY_KEY |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7566)

FOO SCOTT>SELECT /*+ ALL_ROWS */ empno, ename, sal, job
  2  from scott.emp
  3  WHERE empno = 7566;

Execution Plan
----------------------------------------------------------
Plan hash value: 3154838177

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    22 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP             |     1 |    22 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PRIMARY_KEY |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7566)

Report message to a moderator

Re: optimizer hints [message #244761 is a reply to message #244530]

Thu, 14 June 2007 01:17

hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai

Member

Joy,

Thanks for giving reply.

actually iam not aware of execution plans.

By seeing above two execution plans, the main difference is in Bytes column.
First one is taking 18 and second one is 22. Can you explain me more about this?

Rgds
Hari Krishna

[Updated on: Thu, 14 June 2007 01:18]

Report message to a moderator

Previous Topic:	self join oracle
Next Topic:	Composite Primary Key & dup_val_on_index (2 merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 05:52:55 CST 2024