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

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>, "sfaroult@xxxxxxxxxxxx" <sfaroult@xxxxxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>, Tanel Poder <tanel.poder.003@xxxxxxx>, rjamya <rjamya@xxxxxxxxx>, VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
  • Date: Tue, 28 Oct 2008 13:41:57 +0100

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" ) )


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2008