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

and also typically the kind of query generated by Business Intelligence tools, where the model - complete with the 42 additional tables is the 'object' queried by the tool - in which case the FIRST_ROWS(200) is probably also generated by setting a page size parameter for the report.

obviously I don't know if this matches Vivek's situation, but it certainly does happen.

On Tue, Oct 28, 2008 at 12:41 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> Vivek,
>
> 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.
>
> HTH
>
>
>
> S Faroult
>
> *On Mar Oct 28 13:11 , VIVEK_SHARMA sent:
>
> *
>
> 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" ) )
>
>
> -- http://www.freelists.org/webpage/oracle-l

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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