Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?
I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>
|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.
|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)
|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David
|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert