SQL Tuning Case .... When is Select /*+ FIRST_ROWS(200) */ Hint Useful ?

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 28 Oct 2008 17:41:41 +0530
Message-ID: <69E1360E54B50C4A828A136C158E474205454EFD19@BLRKECMBX02.ad.infosys.com>

Folks

When is Select /*+ FIRST_ROWS(200) */ Hint Useful ? Following SQL needs Tuning ... Any Ideas for RE-structuring ... Should it be converted into a PL/SQL? ... Any Examples , Docs , Links pls?

Will provide any Data needed.

Cheers & Thanks V much

P.S.

Select /*+ FIRST_ROWS(200) */ BO24_Sales.ApplCreationDate, .. From Opportunities BO16_Opportunities, BizCenter BO146_BizCenter, Agents BO3_Agents, SRMGroups BO13_SRMGroups, Opportunity_Products BO16_Opportunity_Products, Products BO21_Products, Sales BO24_Sales where BO16_Opportunities.AssignedLocationID = BO146_BizCenter.BCID (+)

AND BO16_Opportunities.AssignedUserID = BO3_Agents.PersonID (+)
AND BO16_Opportunities.assignedGroupID = BO13_SRMGroups.GroupID (+)
AND BO16_Opportunity_Products.productID = BO21_Products.ProductID
AND BO16_Opportunities.OpportunityID = BO16_Opportunity_Products.jobID (+)
AND BO16_Opportunities.OpportunityID = BO24_Sales.OpportunityID (+)
AND BO16_Opportunities.bank_ID = BO13_SRMGroups.bank_ID (+)
AND BO16_Opportunity_Products.bank_ID = BO21_Products.bank_ID
AND BO16_Opportunities.bank_ID = BO16_Opportunity_Products.bank_ID (+)
AND BO16_Opportunities.bank_ID = BO24_Sales.bank_ID (+)

AND ( ( ( ( ( BO16_Opportunities.mergedWith is null ) AND ( BO16_Opportunities.Status in (:"SYS_B_11", :"SYS_B_12") ) ) AND ( ( ( BO16_Opportunities.OwnerUserID = :"SYS_B_13" ) OR ( ( BO16_Opportunities.AssignedUserID = :"SYS_B_14" ) OR ( BO16_Opportunities.SalesOwnerID = :"SYS_B_15" ) ) ) ) ) AND (rownum <=:"SYS_B_16")
AND BO16_Opportunities.BANK_ID = :"SYS_B_17" ) )

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS***

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 28 2008 - 07:11:41 CDT