:
Sent by: Subject: RE: SQL Tuning - How to avoid
TOCHAR function against a date
[EMAIL PROTECTED]
om
recipients of list ORACLE-L
[EMAIL PROTECTED]
tery.orgcc:
Sent by: Subject:
RE: SQL Tuning - How to avoid TOCHAR function
against a date
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-'),11)
John
-Original Message-
[EMAIL PROTECTED]
Sent: 08 April 2002 18:57
To: Multiple recipients of
Cherie,
Couldn't you do
SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE = trunc(:b1)
and oracle_date trunc(:b1) + 1
which should at least give a range scan.
Iain Nicoll
-Original Message-
Sent: Monday, April 08, 2002 6:57 PM
To: Multiple recipients of list ORACLE-L
I've got the
Could you maybe calculate a range of date values that encompasses the period
you want and use BETWEEN on the raw date column? I'm thinking something
along the lines of:
SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ;
but like, more elegant.
by:Subject: RE: SQL Tuning - How to
avoid TOCHAR function against a date
[EMAIL PROTECTED
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
]
anais.com Subject: RE: SQL Tuning - How to avoid
TOCHAR function against a date
04/08/02 12:37
Something like:
WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1) + 1 - 1/(24*60*60)
-Original Message-
Sent: Monday, April 08, 2002 12:57 PM
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
Cherie,
How about using the TRUNC function on the date field. That will use
only thre ,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
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
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
I suppose if you wanted to collect statistics about hourly usage, then
the minutes info would be necessary
but then, most people don't think about how they really want to use the
date when they add a date field
--- Mercadante, Thomas F [EMAIL PROTECTED] wrote:
let's face it Rachel, the date
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
To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
ate.ny.us Subject: RE: SQL Tuning - How to avoid
TOCHAR function
15 matches
Mail list logo
