| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql Tuning help
Try turning the subquery into a join like this. Also make sure you've got sufficient hash_area_size to do a hash join instead of a sort/merge join. 20m should be plenty. If you have to, put a use_hash hint on the query to force it to a hash join.
SELECT to_char(NVL(SUM(bet_amount),0))FROM sb_bets a, customers bWHERE a.processed_DATE between add_months(TO_DATE('07011999 000000','MMDDYYYY HH24MISS'),19-1) and add_months(TO_DATE('07011999 000000','MMDDYYYY HH24MISS'),19) and a.customer_id = b.customer_id and b.customer_id=a.customer_id and b.LICENSEE_ID=6130
-----Original Message-----From: SouthcottM_at_StarnetSystems.net [mailto:SouthcottM_at_StarnetSystems.net]Sent: Wednesday, February 07, 2001 8:31 AMTo: Multiple recipients of list ORACLE-LSubject: Sql Tuning help I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE >= add_months<FONT!
!
color=#808000 size=1>(TO_DATE('07011999 000000','MMDDYYYY HH24MISS'),19-1) AND processed_DATE < add_months(TO_DATE(</FON!
!
T>'07011999 000000','MMDDYYYY HH24MISS'),19) AND customer_id in (select customer_idfrom customerswhere customers.customer_id=sb_bets.customer_id and</FONT!
!
> 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 visithttp://www.uk.uu.net/products/security/virus/Do You Yahoo!?Yahoo! Auctions - Buy the things you want at great prices. Received on Wed Feb 07 2001 - 13:07:13 CST
 |
 |