Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sql Tuning help

Re: Sql Tuning help

From: Tim Sawmiller <sawmillert_at_state.mi.us>
Date: Wed, 07 Feb 2001 11:49:32 -0800
Message-ID: <F001.002ADD80.20010207083122@fatcity.com>

Instead of customer_id in...try

where exists (select 'x' from customers where sb_bets.customer_id = customers.customer_id and sb_bets.customer_id and LICENSEE_ID=6130)

>>> SouthcottM_at_StarnetSystems.net 02/07/01 08:30AM >>>
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    

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: sawmillert_at_state.mi.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Feb 07 2001 - 13:49:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US