Re: Index on status field?

You don't need a function based index. Oracle doesn't index NULLs, that's why the trick works in first place. There's a demo on http://psoug.org/reference/indexes.html (search for "Tom Kyte") that shows an FBI but all you need to do is go from Y/N to Y/NULL and you don't need to use the FBI nor do you need the awkward WHERE clause like (DECODE(temporary, 'Y', 'Y', NULL)). All you need is one query to forget to just write "temporary = 'Y'" and the FBI gets skipped and you're back to a FTS. I prefer Y/NULL with a check constraint that makes sure those are the only valid values.

S-

On Thu, 7 Apr 2005, rjamya wrote:
> Sorry I meant to say create a FBI where status is not null.
>
> On Apr 7, 2005 9:48 AM, rjamya <rjamya_at_gmail.com> wrote:
> > how about using a FBI so you index only those rows with status <> 0?
> >
> > Raj

-- 
Stephen Rospo        Principal Software Architect
Vallent Corporation (formerly Watchmark-Comnitel)
Stephen.Rospo_at_vallent.com           (425)564-8145

This email may contain confidential information. If you received this in
error, please notify the sender immediately by return email and delete this
message and any attachments. Thank you.

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