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

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 inser
ted 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 cl
ustered index is the
>physical sort order of data in a table. And once i have this technique avai
lable to me, i can take
>advantage of it to group low-cardinality rows together, where an index woul
d not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tunin
g 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

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

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

'distributor_admin' is not defined as a remote login at the server

We have set up one of the clustered instance as a remote distributor and
replication has been working properly. However, on the event log of the
active node, I saw the following error appeared every few minutes:
Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
'distributor_admin' is not defined as a remote login at the server. Verify
that you have specified the correct login name. [CLIENT: 10.1.1.28].
REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
as a remote distributor and it is running on active node; the client
(10.1.1.28) is the passive node of the cluster. our environment is windows
2003 and sql server 2005 with sp2.
I wonder if anyone has any idea how to get rid of this error from my server.
Thanks in advance.
Zack.
I wonder if it is a managed news group by Microsoft, if not, someone knows
the managed news group since I was told that it is a managed group and
Microsoft will reply every post?
thanks for your kind reply because I really need to fix this error and don't
want to pay MS for this kind of error. We have paid too much to MS already
for support.
Zack.
m
"dp" wrote:

> We have set up one of the clustered instance as a remote distributor and
> replication has been working properly. However, on the event log of the
> active node, I saw the following error appeared every few minutes:
> Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
> 'distributor_admin' is not defined as a remote login at the server. Verify
> that you have specified the correct login name. [CLIENT: 10.1.1.28].
> REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
> as a remote distributor and it is running on active node; the client
> (10.1.1.28) is the passive node of the cluster. our environment is windows
> 2003 and sql server 2005 with sp2.
> I wonder if anyone has any idea how to get rid of this error from my server.
> Thanks in advance.
> Zack.
>