SQL Tuning Guru's HELP HELP !

From: <mashy_at_null.net>
Date: Thu, 20 Aug 1998 15:51:02 GMT
Message-ID: <6rhgl6$h0j$1_at_nnrp1.dejanews.com>



Guru's !

 I have a SQL query :

 Select /*+ ORDERED INDEX (c x2_tab3) */

        c.col1,
        c.col2,
        ....
 From   table1   a,
        table2   b,
        table3   c
 Where  a.col1||''      =        c.col1||''
 AND    c.col2  BETWEEN  to_date(199805,'YYYYMM')
                AND      last_day(to_date(199805235959,'YYYYMMHH24MISS'))
 AND    trunc(c.col2)  >        c.col3
 AND    c.col3  >=       '01-MAY-98'

 AND to_number(c.col4) = b.col1
 ......

 here I have an index on :
 Table Indexed Col IndexName



 table1 a.col1 pk_tab1
 table2  b.col1
         b.col2
         b.col3
         b.col4            pk_tab2

 table3  c.col1            x3_tab3
         c.col2            x2_tab3

 When I take the query plan ... it looks like the index on table3.col2 is  being used ... but I wanted to know if that index will be used at all in  fetching rows as i am using a function on the same column in the next  condition of the where clause ....??

 Is there any other way to get around the trunc function ... I have to do it  as the c.col2 is a date field with a timestamp and c.col3 is a date field  without a timestamp ... how do you compare these fields keeping the same  logic applied but not using the TRUNC or a to_char ... ??

 Please Help !!

 Thanks Mates !!

 Mahesh !

 Pls cc : mashy_at_null.net


-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 20 1998 - 17:51:02 CEST

Original text of this message