RE: Sql Tuning help

From: Dasko, Dan <Dan.Dasko_at_cdicorp.com>
Date: Wed, 07 Feb 2001 10:06:57 -0800
Message-ID: <F001.002ADDDF.20010207085030@fatcity.com>

Here's
some thoughts.
<SPAN
class=661204616-07022001> 
I
don't know if between is faster, but it might be, and won't a straight join do the same as your subquery?
<SPAN
class=661204616-07022001> 
<SPAN
class=661204616-07022001>SELECT
 
to_char(NVL(SUM(bet_amount),0))
<SPAN
class=661204616-07022001>FROM
 
sb_bets
<SPAN
class=661204616-07022001>WHERE
 
processed_DATE between add_months(    .............) and add_months(   ..........) AND
 
customers.customer_id = sb_bets.customer_id AND  
customer.licensee_id = 6130;
<SPAN
class=661204616-07022001> 
Dan
"Just my thought"

<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> 

   _____________________________________________________________________This 

  message has been checked for all known viruses by UUNET delivered through   the MessageLabs Virus Control Centre. For further information visit<A   virus security products www.uk.uu.net   hrefhttp:>http://www.uk.uu.net/products/security/virus/ Received on Wed Feb 07 2001 - 12:06:57 CST