Re: Function Based Index

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Thu, 20 Sep 2012 23:21:28 +0100
Message-ID: <CAOuMUT59hYMW-CWGVeY7SPBFKu_=VATN4+_+L2iOFd87KFW4wg_at_mail.gmail.com>

Hi Purav,
I think Mike really hit the nail on the head here where he wrote:

*delete from <table_name> where
trunc(datetime) < (trunc(sysdate) - 31) and rownum < 1000;

*
*can also be written as:*
*

delete from <table_name> where
datetime < (trunc(sysdate) - 31) and rownum < 1000;*

Even when datetime has a time component this is true; so in this particular case you really didn't need the FBI.

Illustration below:

select sysdate from dual;

SYSDATE

---

20-SEP-12 23:02:11 select (trunc(sysdate) - 31) from dual;

(TRUNC(SYSDATE)-31)

---

20-AUG-12 00:00:00 select * from c order by 1;

DATETIME

---

19-AUG-12 00:00:01
19-AUG-12 23:59:59
20-AUG-12 00:00:00
20-AUG-12 00:00:01

select * from c where trunc(datetime) < (trunc(sysdate) - 31);

DATETIME

---

19-AUG-12 00:00:01
19-AUG-12 23:59:59 select * from c where datetime < (trunc(sysdate) - 31); DATETIME

---

19-AUG-12 00:00:01
19-AUG-12 23:59:59 Of course if there was no trunc on the sysdate then this would no longer be true

Regards,
Fergal

On 20 September 2012 10:52, Purav Chovatia <puravc_at_gmail.com> wrote:

> :)
> This column contains datetime and is a classic case of what Jonathan has
> demonstrated in his index explosion series. Because of applications'
> limitations we cannot move from storing datetime to storing only date. One
> other solution would be to have another column which contains only the date
> and index that column and then NOT index this column which stores datetime;
> but I didn't want that additional data unnecessarily. I thought FBI would
> be simple.
>
> Thanks.
>
> On Tue, Sep 18, 2012 at 5:18 PM, Tefft, Michael J <
> Michael.J.Tefft_at_snapon.com> wrote:
>
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Fergal Taheny
Pentec IT Limited
2 knightsbrook court, Dublin Road, Trim, Co. Meath.
+353 (0) 87 9823137
ftaheny_at_gmail.com

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify us immediately.  It
is possible for data transmitted by email to be deliberately or
accidentally corrupted or intercepted. For this reason, where the
communication is by email, Pentec IT does not accept any responsibility for
any breach of confidence which may arise through the use of this medium.
Pentec IT Limited is Registered in Ireland: No 443280 with a registered
office at 2 Knightsbrook Court, Dublin Road, Trim, Co. Meath. Company
Directors: Fergal Taheny, Caitriona Ni Riain.


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 20 2012 - 17:21:28 CDT