| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Tuning - How to avoid TOCHAR function against a date
Ron,
That's an idea. Easy to implement and test. I'll give it a try tonight to see if it helps.
It is a small table.
Cherie
"Ron Rogers"
<RROGERS_at_galot To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
tery.org> cc:
Sent by: Subject: RE: SQL Tuning - How to avoid TOCHAR function against a date
root_at_fatcity.c
om
04/08/02 03:23
PM
Please respond
to ORACLE-L
Tom,
I realize that there would not be an index but I was trying to
eliminate some overhead by using the TRUNC function as compaired to the
to_char for the fields.
Cherie,
If the table is not to large how about pinning it to save on disk
reads?
Ron
ROR mª¿ªm
>>> NDATFM_at_labor.state.ny.us 04/08/02 03:35PM >>> Ron,
the TRUNC function will also prevent the use of an index on the
oracle_date
column.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, April 08, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
Cherie,
How about using the TRUNC function on the date field. That will use
only thre YYYY,MM,DD of the ORACLE_DATE column. Then you will be
comparing like columns without going through the to_char conversion.
WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1)
Ron
ROR mª¿ªm
>>> Cherie_Machler_at_gelco.com 04/08/02 01:56PM >>>
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
Cherie Machler
Oracle DBA
Gelco Information Network
--
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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Listsil message
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Ma
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
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 Liststo: 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 Tue Apr 09 2002 - 09:18:21 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |