I am jumping in the middle of this thread so execuse
me if I am repeating the past suggestions I haven't
read yet. In a DW you'd have a date dim of dates only
(no time component to date) and a time_dim (down to
seconds). Your fact table should have a date_key and a
time_key if both date and time components are
significant. In that case your query
SELECT DATE_KEY
FROM DATE_DIM
WHERE TO_CHAR(ORACLE_DATE,'DD-MON-YYYY') =
TO_CHAR(:b1,'DD-MON-YYYY')
would be transformed to:
SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE = TRUNC(:b1);
You should perhaps update the oracle_date column in
date_dim to TRUNC(oracle_date,'DD') and then rebuild
the index on oracle_date column and run the above
mentioned query.
- Sundeep
- Cherie_Machler_at_gelco.com wrote:
>
> 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
> ---------
> 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: 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
>
=== message truncated ===
Sundeep Maini
Consultant
Currently on Assignement at Marshfield Clinic WI
mainis_at_mfldclin.edu
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: sundeep maini
INET: sundeep_maini_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).
Received on Tue Apr 09 2002 - 18:08:21 CDT
