Re: Need help in tuning a set of query.

Hi Robert,

First of all, I want to thank you for taking the time to provide some pointers
on this query. You had some good suggestion here and had helped me detected the root of the problem thus avoiding most of the full table scan except for one. (Time_Sheets)

The fundamental problem seems to derived from the fact that 2 of the table columns that I am referencing to in this query, had not been indexed yet. After adding the indexes and computed the stat's, the access path seems to look a lot better now.

Your alternatives methods for the queries had also provided me some ideas to write more efficient queries.

Thanks Robert and you have a pleasant evening.

Kenny-

> Here are some alternative queries
> 5. In-line table approach... idea,
> keep memory small until the end, then
> get detailed data.
> a. Gather the Max(effective_dates)
> b. Find all the employees who worked on debugger
> c. See which employees dates match rpt_date.
> d. Add on all the detail (I am assuming Employee
> has a lot of detail columns)
>
> [Select *... better: Select a,b,c,d,e...etc]
>
> Select * from EMPLOYEES
> Where Emp_Seq IN
> (Select EmpProj.EmpSeq
> From
> (Select
> Emp_Seq EmpSeq,
> Max(Effective_Date) EffDate
> From Sal_History
> Group by Emp_Seq
> ) EmpDates,
> (Select
> ts.Emp_Seq EmpSeq,
> pj.Proj_Seq ProjSeq,
> pj.Rpt_Date RptDate
> From Projects pj,
> Time_Sheets ts
> Where pj.Name = 'Debugger'
> And pj.Proj_Seq = ts.Proj_Seq
> ) EmpProj
> Where EmpDates.EffDate = EmpProj.RptDate
> )
>
>
> 5b. One big JOIN approach....
>
> Select e.A, e.B, e.C....etc,
> p.Rpt_Date, Max(sh.Effective_date)
> From
> Projects p,
> Time_Sheets ts,
> Sal_History sh,
> Employees e
> Where p.Proj_seq = ts.Proj_seq
> And ts.Emp_Seq = sh.Emp_Seq
> And sh.Emp_Seq = e.Emp_Seq
> Group By
> e.A, e.B, e.C....etc,
> p.Rpt_Date
> Having p.Rpt_date = Max(sh.Effective_Date)
>
> Good Luck...
> Comments welcome...anyone?
>
> Robert Proffitt
> Beckman Coulter
> RTProffitt AT beckman DOT com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Tue Mar 28 2000 - 08:46:44 CEST