| Applying Hints in the query [message #65700] |
Thu, 09 December 2004 19:34  |
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  |
Frank
Messages: 7901
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
