Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Tuning - How to avoid TOCHAR function against a date

RE: SQL Tuning - How to avoid TOCHAR function against a date

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 08 Apr 2002 11:35:24 -0800
Message-ID: <F001.0043ECED.20020408113524@fatcity.com>


let's face it Rachel, the date column is probably incorrect as the table was designed. knowing that it is important in queries, and that the minutes cause problems during query, your suggestion should have been incorporated in the original design (or truncing the oracle_date field via a trigger). both the blessing and curse of the DATE column. great for performing date math, but a pain when it comes to queries.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, April 08, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L

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

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

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

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

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


Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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 - 14:35:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US