RE: Sql Tuning help

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Wed, 07 Feb 2001 08:30:11 -0800
Message-ID: <F001.002ADC46.20010207081046@fatcity.com>

Matt,
have you tried replacing the IN statement with an EXISTS statement?

<FONT face=Tahoma

  size=2>-----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   

<SPAN

  class=732172014-07022001>SELECT
  to_char(<FONT
  color=#000000>NVL(<FONT
  color=#008000>SUM(<FONT

  face=Arial>bet_amount<FONT color=#808000 
  size=1>)<FONT color=#000000 
  size=1>,0<FONT color=#808000 
  size=1>))<FONT 
  face=Arial>FROM<FONT color=#000000 
  size=1> sb_bets<FONT 
  face=Arial>WHERE<FONT color=#000000 
  size=1> processed_DATE <FONT color=#808000 
  size=1>>= 

  add_months(<FONT

  face=Arial>TO_DATE<FONT color=#808000 
  size=1>(<FONT color=#0000f0 
  size=1>'07011999 000000',<FONT 

  color=#0000f0 size=1>'MMDDYYYY HH24MISS'<FONT color=#808000

  size=1>)<FONT color=#000000 
  size=1>,19<FONT color=#808000 
  size=1>-1<FONT color=#808000 
  size=1>) 

  AND<FONT
  face=Arial> processed_DATE <FONT
  color=#808000 size=1><<FONT
  color=#000000 size=1> add_months<FONT color=#808000

  size=1>(<FONT color=#000000 
  size=1>TO_DATE(<FONT 
  face=Arial>'07011999 000000'<FONT 

  color=#000000 size=1>,'MMDDYYYY
  HH24MISS')<FONT

  face=Arial>,<FONT color=#800000 
  size=1>19)<FONT 
  face=Arial> <FONT color=#008000 
  size=1>AND 

  customer_id in (<FONT
  color=#008000 size=1>select<FONT
  color=#000000 size=1> customer_id<FONT color=#008000   size=1>from
  customerswhere<FONT
  face=Arial>
  customers.customer_id=sb_bets.customer_id <FONT color=#008000   size=1>and
  LICENSEE_ID=<FONT color=#800000 face=Arial   size=1>6130<FONT
  face=Arial>)
<FONT color=#000000 face=Arial

  size=2>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.
<FONT color=#000000 face=Arial

  size=2> 
<FONT color=#000000 face=Arial

  size=2>Please email me for any further info   thanks.

  Matt
  Southcott
  DBA   
<SPAN

  class=474161317-01022001>Starnetsystems   (268) 480
  1734
<SPAN

  class=474161317-01022001> 
    Received on Wed Feb 07 2001 - 10:30:11 CST