Re: SQL Tuning Case .... When is Select /*+ FIRST_ROWS(200) */ Hint Useful ?
Date: Tue, 28 Oct 2008 13:41:57 +0100
That's typically the kind of query I hate to see. Hint, CURSOR_SHARING=FORCE and obviously a "dynamic" query where data is returned from a single table but the 3,256 which share a join key with them are joined (with outer joins) just in case an end-user would have the idea of adding a condition on them.
1) Don't join things just in case. It's easy to add a subquery if there is a condition on a related table. The optimizer will know how to handle it. Be minimalist.
2) Don't hard-code.
3) At this stage the question about the hint should be moot.
On Mar Oct 28 13:11 , VIVEK_SHARMA
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
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" ) )
-- http://www.freelists.org/webpage/oracle-l Received on Tue Oct 28 2008 - 07:41:57 CDT