Re: Full table scans with Date fields
Date: 1997/05/23
Message-ID: <3387f768.4982364_at_newshost>#1/1
You might consider generating triggers on insert/update of the date columns from the data dictionary that simply:
:new.date_field := trunc( :new.date_field );
that way, the only thing that ever gets stored is the dd-mon-yyyy part. Then, you will never have to trunc() a database column again.
the drawback in your environment might be if you have BEFORE, FOR EACH ROW triggers already, they will not be able to count on the above trigger firing first (as same type triggers fire in a random order).
Have you control over how the data is input into the system in the first place? If so, you could fix it there as well. Don't default it to SYSDATE, default it to trunc(sysdate)....
On 22 May 1997 20:31:36 GMT, "Stacey Knight" <sknight_at_aegonusa.com> wrote:
>We have a financial system that that is heavily date-dependent. All calcs,
>queries, imports, database triggers, etc are date-dependent.
>
>The problem is that we only reference the date portion of the date and not
>the time. To do this, the entire system uses the TRUNC(DATE) function and
>this causes full table scans in every imaginable situation, therefore, the
>performance is horrible in nearly every aspect.
>
>I have implemented the BETWEEN condition in place of the ' = TRUNC(DATE)'
>in many of my IF statements to utilize indexes, but this is not feasible
>for database triggers and a few other situations.
>
>It's hard for me to believe there isn't another way. Does anyone have any
>suggestions?
>Please email me, as our newsgroup server is unreliable.
>
>THANKS!
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 23 1997 - 00:00:00 CEST