Home » SQL & PL/SQL » SQL & PL/SQL » optimizer hints
optimizer hints [message #244530] Wed, 13 June 2007 04:04 Go to next message
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
Re: optimizer hints [message #244531 is a reply to message #244530] Wed, 13 June 2007 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL Reference, section Using Hints

Regards
Michel
Re: optimizer hints [message #244533 is a reply to message #244530] Wed, 13 June 2007 04:18 Go to previous messageGo to next message
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
Re: optimizer hints [message #244534 is a reply to message #244530] Wed, 13 June 2007 04:28 Go to previous messageGo to next message
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
Re: optimizer hints [message #244538 is a reply to message #244534] Wed, 13 June 2007 04:42 Go to previous messageGo to next message
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
Re: optimizer hints [message #244583 is a reply to message #244534] Wed, 13 June 2007 07:48 Go to previous messageGo to next message
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)

Re: optimizer hints [message #244761 is a reply to message #244530] Thu, 14 June 2007 01:17 Go to previous message
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:
  


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