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 --- [EMAIL PROTECTED] 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@galot To: > Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > tery.org> cc: > > > Sent by: Subject: > RE: SQL Tuning - How to avoid TOCHAR function > against a date > [EMAIL PROTECTED] > > > 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 > > >>> [EMAIL PROTECTED] 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 > > >>> [EMAIL PROTECTED] 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: [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 > === message truncated === ===== Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __________________________________________________ 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: [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).
