RE: Sql Tuning help

[Dasko, Dan]

Here's some thoughts.

 

I don't know if between is faster, but it might be, and won't a straight join do the same as your subquery?

 

SELECT

  to_char(NVL(SUM(bet_amount),0))

FROM

  sb_bets

WHERE

  processed_DATE between add_months(    .............) and add_months(   ..........) AND

  customers.customer_id = sb_bets.customer_id AND

  customer.licensee_id = 6130;

 

Dan "Just my thought"

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: Sql Tuning help

I have been having some problems with this statement

SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE >= add_months(TO_DATE('07011999 000000','MMDDYYYY HH24MISS'),19-1) AND processed_DATE < add_months(TO_DATE('07011999 000000','MMDDYYYY HH24MISS'),19) AND customer_id in (select customer_id
from customers
where customers.customer_id=sb_bets.customer_id and LICENSEE_ID=6130)

Both tables are full access no indexes used.  There is an index on sb_bets.processed_date and customers.customer_id is a primary key and customers.licensee_id has an index also.  Of course this query may just pull too many customer ids to bother with an index.  But that is not too bad only 200000 records in customers but over 12 million in sb_bets.  Is there a better way of writing this query?  I have tried hints but still nothing changed.  Any ideas would be greatly appreciated.

 

Please email me for any further info thanks.

 

 

 

Matt Southcott

DBA

Starnetsystems

(268) 480 1734

 

 

_____________________________________________________________________
This message has been checked for all known viruses by UUNET delivered
through the MessageLabs Virus Control Centre. For further information visit
http://www.uk.uu.net/products/security/virus/