Re: how to get select where not null to use an index

From: Steve M <steve.mcdaniels_at_vuinteractive.com>
Date: Tue, 19 Feb 2002 16:43:33 -0800
Message-ID: <a4urfk$c2g$1@spiney.sierra.com>

assuming you have table T with index I on field F.

you can implement this:

select * from T where F is not null

by using a hint

select /*+ INDEX (T, I) */ * from T

This should only produce records that have an index entry for each F, and since NULL fields
are never part of the index, you could eliminate the where...not null portion

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3c6c372d.1318626214_at_news.globix.com...
> can't think of anything but
>
> select * from mytable where varchar_field>=
> (select min(varchar_field) from mytable)
>
> better ideas?
>
> Thnx
> .......
> We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 750-3630
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email
Received on Tue Feb 19 2002 - 18:43:33 CST