John,
I will test it out. Thanks for your helpful recommendation.
Cherie
"John Hallas"
<john.hallas@hcresour To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
ces.co.uk> cc:
Sent by: Subject: RE: SQL Tuning - How to
avoid TOCHAR function against a date
[EMAIL PROTECTED]
04/08/02 01:20 PM
Please respond to
ORACLE-L
I have not got a system to test this out on at the moment but can you do a
substr on the to_char so that the format matches the date_key
Something like substr((TO_CHAR(:b1,'DD-MON-YYYY'),11)
John
-----Original Message-----
[EMAIL PROTECTED]
Sent: 08 April 2002 18:57
To: Multiple recipients of list ORACLE-L
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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: John Hallas
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
