Re: Query tuning

Hi kapil,

Here you have specified 3 tables, does JOB_TYPE_FIRST and JOB_TYPE_SECOND
both contain all the JOBID in the third table?

Maybe i can help you if you elaborate your problem a bit more.

Regards,
Moiz Kothari

On Thu, May 15, 2008 at 11:30 AM, <kapil(dot)munish(at)wipro(dot)com> wrote:

> Hi,
>
>
>
>
>
> I have a query which is run across 3 tables JOB_TYPE_FIRST, JOB_TYPE_SECOND
> and JOB_ALLOCATION_WORKLIST.
>
>
>
> The column JOBID is referenced in JOB_ALLOCATION_WORKLIST table and primary
> key in both JOB_TYPE_FIRST, JOB_TYPE_SECOND tables.
>
>
>
> There is one more column BOOK_ID which is supplied as the binding parameter
> to the query. The query looks like:
>
>
>
> select count(distinct(j.JOBID)) as jobCount
>
> from JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j
>
> where (( a.JOBID = j.JOBID)
>
> and (a.BOOK_ID = :bookId))
>
> or ((b.JOBID = j.JOBID)
>
> and (b.BOOK_ID = :bookId));
>
>
>
> As the records in the database are too large it is having huge cost and
> stalling the server and takes lot of time.
>
>
>
> Can anyone suggest a better way to fetch the results for the query or tune
> it? Any help would be highly appreciated.
>
>
>
>
>
> Thanks & Regards,
>
> Kapil
>
> * Please do not print this email unless it is absolutely necessary. *
>
> The information contained in this electronic message and any attachments to
> this message are intended for the exclusive use of the addressee(s) and may
> contain proprietary, confidential or privileged information. If you are not
> the intended recipient, you should not disseminate, distribute or copy this
> e-mail. Please notify the sender immediately and destroy all copies of this
> message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses. The
> company accepts no liability for any damage caused by any virus transmitted
> by this email.
>
> www.wipro.com
>

--
Hobby Site : http://dailyhealthtips.blogspot.com