Home » RDBMS Server » Performance Tuning » Applying Hints in the query
Applying Hints in the query [message #65700] Thu, 09 December 2004 19:34 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
<A name=4782>[/url]Hi Experts,

I have this doubt and comments of my seniors on this.I just want to confirm that specifying hint in the 1st block is correct or does it make any difference in execution of the query in comparison to the 2nd one.My seniors told me this:-

Comments:-Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:A statement block can have *only one comment* containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.

.1. select  /*+USE_NL(N, T, E, J, ES, ET)*/

           /*+ORDERED*/

2. select  /*+ORDERED USE_NL(N, T, E, J, ES, ET)*/

Is the specification of Hint in the 1st block wrong or if not does it make any difference to the 2nd one, which is better and what is the perfect method that should be adopted while applying hint to the query.

Pls. help me by giving me your suggestions on this.

thanks in advance

Milind
Re: Applying Hints in the query [message #65701 is a reply to message #65700] Thu, 09 December 2004 22:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your tutor is right: you can only specify 1 set of hints per "statement-block"
You can however do
select /*+ hint1 */ *
from   table
union
select /*+ hint2 */ *
from   table2

That is what they mean by 1 set of hints per statement-block.

The perfect implementation of hints is to use them as little as possible! Especially on 9iR2 and up let the optimizer do its work.

hth
Re: Applying Hints in the query [message #65702 is a reply to message #65701] Fri, 10 December 2004 00:05 Go to previous messageGo to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Frank,
Thanks for your quick response.
It is agreed that for ORDERED AND USE_NL hints they should be specified in one comment block. But, I have this query tuned by my senior which has two comment blocks one for Index hint and one for Parallel hint.
Will both the hint will be accepted or one of them will be rejected as there are two hint statements for a single select block. I am sending you the query for your reference.

select /* [[[[[[Communication.WorksheetSearch]]]]]] */
/*+ INDEX (B,BUSINESS_NAME_PK) */
/*+ PARALLEL (W,2) */
W.COMM_WORKSHEET_ID LogId,
trim(W.BUS_NAME) EntityName,
decode(W.BUS_NAME_ID, null, 'No', 'Yes') IsCTEntity,
B.ENTITY_ID EntityId,
trim(J.JURIS_SHORT_NAME) CommJuris,
trunc(W.RECEIVED_DATE) Received,
(select max(T.RETURNED_DATE) ReturnDate
from ARV_COMM_TRANSMITTAL T where T.COMM_WORKSHEET_ID = W.COMM_WORKSHEET_ID) LatestReturned,
decode(W.REP_ID, null, 'No', 'Yes') RepId,
trunc(W.CREATED_DATE) CreatedOn,
decode(D.OFFICIAL,
'Y', 'Official - ' ||
W.DOC_TYPE_TEXT,
'N', 'Unofficial - ' ||
W.DOC_TYPE_TEXT) DocType,
(select ST.NAME from AV_SERVICE_TEAM ST where ST.SERVICE_TEAM_ID = W.OWNING_TEAM_ID) ServiceTeamName
from
ARV_COMM_WORKSHEET W,
ARV_JURISDICTION J,
ARV_BUSINESS_NAME B,
ARV_DOCUMENT_TYPE D
where
W.RECEIVED_JURIS_ID = J.JURIS_ID and
W.BUS_NAME_ID = B.BUS_NAME_ID (+) and
W.DOC_TYPE_CD = D.DOCUMENT_TYPE_CD
-- and W.RECEIVED_JURIS_ID = 1

Will both these hint will be accepted by optimiser or noe of them will be rejected as you said there should be only one hint statement per block.

1./*+ INDEX (B,BUSINESS_NAME_PK) */
2./*+ PARALLEL (W,2) */

Pls. clarify my doubts for the same.

Thanks in advance.

Milind.
Re: Applying Hints in the query [message #65705 is a reply to message #65701] Fri, 10 December 2004 05:34 Go to previous messageGo to next message
Alan
Messages: 68
Registered: October 1999
Member
hi,
make sure to gather_stats on a regular basis for the optimizer.

cheers
Re: Applying Hints in the query [message #65713 is a reply to message #65702] Mon, 13 December 2004 21:58 Go to previous message
K. Prakash babu
Messages: 46
Registered: July 2001
Member
Hi milind

We can use multiple hints at once based on the requirement. Oracle will take it. However, it is not recomended in most of the cases.

-Prakash
Previous Topic: data Pulling is very slow and how we can make it fast
Next Topic: So much rows
Goto Forum:
  


Current Time: Tue Sep 29 10:26:36 CDT 2020