Thread: Partial index in other DB

Can anybody confirm whether these databases support partial indexes (and
what are their term and syntax)?

SQL Server 2000: I've glanced the T-SQL Reference and it seems it
doesn't support it, though it supports indexing views. CLUSTERED index
is not the same thing, right?

Oracle: I've glanced the 9i SQL Reference and it doesn't seem to support
it. PARTITION-ing is not the same thing, right?

DB2: In the hackers archive, Bruce said that it does support it "using
single-clause predicates".

--
dave

>Can anybody confirm whether these databases support partial
>indexes (and
>what are their term and syntax)?
>
>SQL Server 2000: I've glanced the T-SQL Reference and it seems it
>doesn't support it, though it supports indexing views. CLUSTERED index
>is not the same thing, right?

No. A CLUSTERED index basically puts the storage of the table into an
index instead of a heap. It still indexes the whole table. In this, it
works like postgresqls CLUSTER except it *keeps* the table clustered at
all times. (This is why you can have only one clustered index / table).
Other indexes just point to pages in the clustered index instead of the
heap.

You might be able to use an indexed view to get the same result,
depending on exactly what your application needs are. But it's definitly
not the same thing.

Can't say anything about the other DBs.

//Magnus