Basic Index question :- nonclusted indexes which are a subset of a clustered index

Can anyone suggest why I would have the following set of indices upon a
table:

index1    clustered, unique   field1,field,field3,fiel4
index2    nonclustered    field2,field3, field4
index3    nonclustered    field,field2,field4

The table is one into which a lot of inserts are done, so I would have
thought that index2 & 3 would be unncessary since they're
(1) already in the clustered index and
(2) any inserts would triple the amount of index updates that are
required...

Are these assumptions correct ?



Thanks in advance,



Steve




0
svaardt
11/8/2002 2:16:00 AM
sybase.ase.administration 7058 articles. 2 followers. Follow

1 Replies
729 Views

Similar Articles

[PageSpeed] 35

Hi,
if you have a lot of inserts related to this table, clustered index may not
be the best solution for your case. As you know, Clustered index enforces
data to be ordered  in index keys.(for your case: field1,field,field3). So
if you do not have range queries on that table, you can consider dropping
the clustered index That is up to you. You better consider a combine of
issues and try to find out best way for indexing. However, idex key order is
very important for your serach criteria (where clause) in your queries. if
you do not use the leading key( first column of an index) in your search
criteria, your can easily see that your showplan output does not include
index choice for that table. It is clear that you have different leading
keys in the indexes below. So according to your queries on that table, you
must have all the indexes below. Let's have a look at your assumptions.
1-) As stated above, index2&3 may be necessary for you.
2-) yes.
Regards
ALPER ONEY
I.S.E TAKASBANK INC.

"svaardt" <svaardt@h0tmail. com> wrote in message
news:pv8Je$shCHA.198@forums.sybase.com...
>
> Can anyone suggest why I would have the following set of indices upon a
> table:
>
> index1    clustered, unique   field1,field,field3,fiel4
> index2    nonclustered    field2,field3, field4
> index3    nonclustered    field,field2,field4
>
> The table is one into which a lot of inserts are done, so I would have
> thought that index2 & 3 would be unncessary since they're
> (1) already in the clustered index and
> (2) any inserts would triple the amount of index updates that are
> required...
>
> Are these assumptions correct ?
>
>
>
> Thanks in advance,
>
>
>
> Steve
>
>
>
>


0
ALPER
11/8/2002 7:15:23 AM
Reply:

Similar Artilces:

To Index or Not To Index or How To Index
Hello, I was wondering if someone knows a better way to explain how different indexes work. Such as, is it better to have an index with int type or char or varchar etc? Is it better to have composite indexes or indexes with individual columns? How does optimizer handle different indexes and what can we do help it? Indexes are very efficient most of the time but they can be quite detrimental to server's performance, if abused. Perhaps you know of a good article about indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like to issue an article in his very usefu...

To Index or not to Index (that is the question...)
I'd like to clear up an assumption I have. If I have a Primary Key Constraint on a Table I assume that I will not need to create an Index on the field(s) of the constraint as the action of creating the primary key constraint produces what is the equivalent of an Index (be the constraint clustered or not). Is this the case? Also I'm looking to Pivot some data, are there any SQL tricks etc. I should be aware of to be able to do this (I've searched the Newsgroups for 'Pivot' but don't understand any of the answers posted - some SQL examples would be gr...

clustered index vs nonclustered index
I have a 16 million row table in which we do lots of inserts throughout the day and a purge at night (no updates). The nightly delete of 300K - 400K rows takes between 6 and 10 hours. I was thinking of replacing the clustered index with a nonclustered index since the table uses row level locking. Generally speaking, is there a performance difference between clustered and nonclustered indexes on a row level locked table? This is ASE 12. Thanks. Carl Britton. Comment. Apparently you mean that the table has DataRow (or possibly DataPage) locking. In which case the clustered...

A question about clustered indexes forcing rebuild of non-clustered indexes.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if...

Clustered Index against non clustered indexes in OLTP
I have a question, I read in an article, that OLTP systems runs better with few indexes, however my question is, is better to have clustered indexes in OLTP than non clustered??, or how can I determine in which tables I have to use clustered indexes or non clustered indexes ??. I'm new in performance, I have a lot of problems of performance with our OLT system. Thanks in advance, Evelyn This is a multi-part message in MIME format. --------------057F1209A1406F494E323FFD Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Evelyn wrote: > ...

Clustered index and identical non-clustered index
Gurus, I have come across a table with the following indexes on it CREATE TABLE banana ( b_id int, ... ) It has the following indexes unique clustered index banana_idx_1 ON banana ( b_id ) and unique non-clustered index banana_idx_2 ON banana (b_id ) i.e. two unique indexes one clustered, the other non-clustered ** on the same field ** Does anyone know the benefits (if any) of doing this ? Hi Zia, it seems suspicious. Anyhow it could make sense if the table is APL. Clustered index sorts the data physically by the index, so if the tab...

Switching a table from a clustered index to a non-clustered index.
Hi all, I've been using CAST to set my table properties but now I must learn the proper syntax in order to adjust the table settings from iSQL. Currently table A's index is set to clustered but I want to convert it to a non-clustered index. Does anyone know the command line that I should use? Thanks, Al Drop & recreate the index ( Primary key ) alter table table_name drop constraint constraint_name go alter table table_name add constraint constraint_name PRIMARY KEY NONCLUSTERED ( col ) go ...

index server not indexing
Ok, so I want to add a catalog to the index server. I can add the new catalog, I then add a directory to this catalog. I restart the index server, and merge the index. If I click the index server, in mmc, it shows 0 documents for everything. Everything meaning documents to index, total docs, docs defered for indexing and so on.  I've tried this on vista as well as on win2003 and the same result is obtained, no documents are indexed. In win2003 I can index document, but ONLY the ones in the default catalog, if I paste some documents there they get indexed. So, what have I don...

Questions about Indexing and Using an Indexing POA
Although I have only about 50 users, at least 15 of them have in excess of 100,000 messages in their accounts and the POA (version 7.0.2) is regularly slowing to a crawl. (I just know that plans for revolution are fomenting!) I have embarked on a campaign to reduce these accounts by archiving everything off to get mail accounts down to 3000 or fewer pieces. I have achieved user buy-in, but have worked on only a few users so far. In another closely related thread, it was suggested to me that the PO speed issues relate to broken indexes. And I suspect that given so many messages...

Clustered Index Scan vs. Index Seek
Hi, I found something odd in the execution plan. I have a table that looks like this (p1, p2, d1, d2, d3), where p1 and p2 are the primary key and d1, d2, and d3 are data. The primary key is clustered. When I check the execution plan of the below query: select * from tbl where p1=111 and p2='2008-1-1' It shows that it will do the query by performing an Index Scan, and the performance, especially when the query is executed the first time, is not great. For a table that has 4 million records, this sometimes can take 4-5 seconds. In MS SQL server, the same query wil...

Clustered Index on Indexed View from ADO.NET
I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.If anyone has experienced this please let me know.Cheers Niall Views are...

Would ASE implement BitMap index or BitWise index?
Hi,Sybaser: Dose ASE has any plan to implement BitMap index? Any what's the advantage of BitWise index. I just know it's a patent technology of SYBASE while BitMap index is a all-known technology based on RDBMS's theory. Thanks a lot. Flybean 2004.08.25 This is a multi-part message in MIME format. --------------080008090408020008010403 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit flybean wrote: > Hi,Sybaser: > Dose ASE has any plan to implement BitMap index? > Any what's the advantage of ...

SQL Server Clustered Index creation
I have a table in which I have defined a Clustered Index that is different from the PK. SQL Server (7 and 2000) creates a clustered index for the Primary Key by default. So, in the script generated by PowerDesigner, the clustered key generation fails with the error "Cannot create more than one clustered index on table 'AH_DESIGNDEFINITION'. Drop the existing clustered index 'PK_AH_DESIGNDEFINITION' before creating another." It would be nice if PowerDesigner would specify that the Primary Key should be created non clustered so that the user defined cluster...

Crawler Index and FIle Index
I have setup the Apache web services as well as Quickfinder for my Netware 6.5 server. The Crawler works fine, finds all the hits I am looking for and opens the file on my data volume. However, when I setup a File System Index it does not open anything. I have double checked my paths and syntex. The thing that I have noticed is that when I search for a file with the Crawler index and highlight the file, the path at the bottom of my browser is like the following example. http://myserver/comp/docs/myfile. showing the server name not an IP address, which is fine and works! Howev...

Web resources about - Basic Index question :- nonclusted indexes which are a subset of a clustered index - sybase.ase.administration

Resources last updated: 1/20/2016 11:01:44 AM