Oracle-L: Re: Re[2]: SQL Tuning - How to avoid TOCHAR function against a date

From: <Cherie_Machler_at_gelco.com>
Date: Mon, 08 Apr 2002 12:48:25 -0800
Message-ID: <F001.0043EEE5.20020408124825@fatcity.com>

Bob,

Thanks for your reply. I am testing this afternoon.

Cherie

                                                                                                                   
                    Robert                                                                                         
                    Eskridge             To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <bryny_at_dfweahs       cc:                                                                       
                    .net>                Subject:     Re[2]: SQL Tuning - How to avoid TOCHAR function against a   
                    Sent by:              date                                                                     
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    04/08/02 02:44                                                                                 
                    PM                                                                                             
                    Please respond                                                                                 
                    to ORACLE-L

How about something like:

SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1)+86399/86400;

It's not the prettiest thing in the world, but it keeps the use of the index on ORACLE_DATE and an adjacent comment that there are 86400 seconds in the day should make it readable enough.

-rje

R> I don't think you can do it.. I mean, you could change it to trunc the
R> oracle_date field (that eliminates the minutes) and then do a to_date
R> of :b1 but you will still be operating on the oracle_date field.

R> Okay, I HATE to suggest this, but since the table is small:

R> add another field to the table oracle_date_2 as a date field. Update R> the table set oracle_date_2=trunc(oracle_date)

R> add a trigger to fill in oracle_date_2 when you insert a row or update R> the oracle_date column

R> create an index on oracle_date_2 and change the query to use that R> column

R> --- Cherie_Machler_at_gelco.com wrote:

>>
>> I've got the following SQL statement that is running very long on a
>> nightly
>> data load. The problem is the TO_CHAR function which is preventing
>> me from using the index on this small (20,000-row table).
>>
>> This is an 8.0.4 database so it is not possible for me to use
>> make this a function-based index.
>>
>> The problem is that the date field has minutes, etc. included and
>> those need to be eliminated before the comparison can be made.
>> That's why I can't just eliminate the TO_CHAR from both sides
>> of the equation.
>>
>> Isn't there a way that I can pull this function out of the select
>> statement
>> and do it in a preceeding statement? Then I could just pass in both
>> variables to this statement without the TO_CHAR and use my index.
>>
>> Is this realistic? How, exactly could it be done?
>>
>>
>> SELECT DATE_KEY
>> FROM DATE_DIM
>> WHERE TO_CHAR(ORACLE_DATE,'DD-MON-YYYY') =
>> TO_CHAR(:b1,'DD-MON-YYYY')
>>
>>
>> SQL> desc date_dim;
>> Name Null? Type
>> ------------------------------- -------- ----
>> DATE_KEY NOT NULL NUMBER(5)
>> ORACLE_DATE NOT NULL DATE
>> DATACOM_DATE NUMBER(6)
>> DATACOM_REVERSE_DATE NUMBER(6)
>> DAY_OF_WEEK NOT NULL VARCHAR2(30)
>> DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3)
>> DAY_NUMBER_OVERALL NOT NULL NUMBER(9)
>> WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3)
>> WEEK_NUMBER_OVERALL NOT NULL NUMBER(7)
>> MONTH NOT NULL VARCHAR2(30)
>> MONTH_NUMBER_OVERALL NOT NULL NUMBER(7)
>> YEAR NOT NULL NUMBER(5)
>> WEEKDAY_IND NOT NULL CHAR(1)
>> LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1)
>> DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE
>> DATA_MART_MOD_DATETIME NOT NULL DATE
>>
>>
>>
>> SQL> select oracle_date from date_dim where rownum=1;
>>
>> ORACLE_DA
>> ---------
>> 01-JAN-70
>>
>>
>> Thanks in advance for any help.
>>
>> Cherie Machler
>> Oracle DBA
>> Gelco Information Network
>>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Eskridge
  INET: bryny_at_dfweahs.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Apr 08 2002 - 15:48:25 CDT