Re: SQL join on date

Hi.

 I think that your query is VERRRY slow, because TRUNCATE  prevents Oracle from using index on table1.datetime field.  What Oracle your are using?
 There is a solution in 8i ( FINCTION-BASED INDEXES ).  Otherwise try rewriting your query :

 SELECT tab1.*, tab2.*
 FROM tab1, tab2
 WHERE tab1.datetime between
  TO_DATE(TO_CHAR(tab2.date,'DD-MM-YY') || ' 00:00:00',

          'DD-MM-YY HH24:MI:SS') AND
  TO_DATE(TO_CHAR(tab2.date,'DD-MM-YY') || ' 23:59:59',

          'DD-MM-YY HH24:MI:SS');

 The idea is to enable INDEX search.

 Good luck. Michael.

In article <37a07326.25220635_at_news.hydro.co.uk>,   gary_at_onegoodidea.com (Gary O'Keefe) wrote:
> Blue wrote:
>
> >I would like to join two tables by a date field..
> >
> >The first table always has a date plus a time value in the date
field.
> >
> >The second table always has a just a date value, time is always 0
> >
> >What is the best SQL join statement to link them based on the date
> >value only, ignoring time
> >
> >Sorry if this is a stupid question but Oracle through impromptu locks
> >up if I use a function to convert the date-time to a date.
>
> Have you tried anything like this
>
> SELECT table1.*, table2.*
> FROM table1, table2
> WHERE trunc(table1.datetime) = table2.date;
>
> or was your date conversion performed using to_char, like
>
> SELECT table1.*, table2.*
> FROM table1, table2
> WHERE to_char(table1.datetime, 'YYYY/MM/DD') = to_char(table2.date,
> 'YYYY/MM/DD');
>
> in which case it may not have locked, it may just be very, very slow.
>
> A fragment of your code would be helpful to determine a starting
> point.
>
> Gary
> --
> Gary O'Keefe
> gary_at_onegoodidea.com
>
> You know the score - my current employer has nothing to do with what
I post
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Jul 31 1999 - 08:18:58 CDT