Index Questions

Hi,

I was wondering if the order in which indexes are created, as well as, the
position of the column is located in table.

I have a table with 40+ fields. Over time, I've just been appending fields
to the end of the table and then indexing them. There are approx. 30
indexes.

Thanks,

Shew


0
Shew
12/10/2001 3:00:30 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

6 Replies
401 Views

Similar Articles

[PageSpeed] 46
Get it on Google Play
Get it on Apple App Store


Shew wrote:

> Hi,
>
> I was wondering if the order in which indexes are created, as well as, the
> position of the column is located in table.
>
> I have a table with 40+ fields. Over time, I've just been appending fields
> to the end of the table and then indexing them. There are approx. 30
> indexes.
>
> Thanks,
>
> Shew

Hi Shew,

It isn't clear what your question is - appears your thought got cut off in
mid-stream.

However, if you are asking about the performance implication of what you are
doing,
there is some advantage to putting the nullable columns that will contain the
most null
values at the end of the table definition - this leads to shorter rows thus
more rows per page,
thus smaller tables that fit more into cache.

30 indexes on a table seem like a lot, and, while generally good for selects,
will make inserts, updates
and deletes much slower.  You should think about each index in terms of
whether it is really that
likely to be used by a query, and eliminate any that don't make sense.

-bret


0
Bret
12/10/2001 4:07:02 PM
Hi Bret,

Sorry, question was:

1. Does it matter in what order indexes are created, i.e. most used index
should be created 1st or it doesn't matter.
2. Reason for all the indexes is basically because 90% of the transactions
are selects and users want to be able to search for every possible thing
under the sun.

Say for example, I've got the following:

- field1 - primary key
- field2 - indexed
- field3 - not indexed
- field4 - not indexed
- field5 - indexed ** Used the most by queries **
- field6 - not indexed
- field7 - indexed
- field8 - indexed
- field9 - indexed
- field10 - indexed

Hopefully, this makes more sense!


"Bret Halford" <bret@sybase.com> wrote in message
news:3C14DDA6.4D2E736@sybase.com...
>
>
> Shew wrote:
>
> > Hi,
> >
> > I was wondering if the order in which indexes are created, as well as,
the
> > position of the column is located in table.
> >
> > I have a table with 40+ fields. Over time, I've just been appending
fields
> > to the end of the table and then indexing them. There are approx. 30
> > indexes.
> >
> > Thanks,
> >
> > Shew
>
> Hi Shew,
>
> It isn't clear what your question is - appears your thought got cut off in
> mid-stream.
>
> However, if you are asking about the performance implication of what you
are
> doing,
> there is some advantage to putting the nullable columns that will contain
the
> most null
> values at the end of the table definition - this leads to shorter rows
thus
> more rows per page,
> thus smaller tables that fit more into cache.
>
> 30 indexes on a table seem like a lot, and, while generally good for
selects,
> will make inserts, updates
> and deletes much slower.  You should think about each index in terms of
> whether it is really that
> likely to be used by a query, and eliminate any that don't make sense.
>
> -bret
>
>


0
Shew
12/10/2001 4:28:39 PM

Shew wrote:

> Hi Bret,
>
> Sorry, question was:
>
> 1. Does it matter in what order indexes are created, i.e. most used index
> should be created 1st or it doesn't matter.


In general, doesn't matter, as long as the indexes are all on just one column.

I seem to recall there might be some issue if you have, say, an index on
(col1,col2)
and another on (col1) - I think there are some processes which will use just the
first
matching index they find - but the details escape me at the moment.  Is that
ever
the case on your system?

0
Bret
12/10/2001 5:33:16 PM
This is a multi-part message in MIME format.
--------------A9ACDEC3595F6D6551E0757F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Shew wrote:
> 
....
> 
> 1. Does it matter in what order indexes are created, i.e. most used index
> should be created 1st or it doesn't matter.

Actually, it could matter in what order the indexes are created when it
comes to which index the optimizer chooses where two or more indexes are
deemed to "cost" the same.  Now, as of 11.9.x, the optimizer would
consider each candidate index defined in sysindexes by indid order
(probably because the sysindexes table is clustered on id, indid).

Consider two indexes index_x having indid (2), and index_y having indid
(3).  Both indexes could contain similar columns, or even completely
different columns:

If index_x was costed the same as index_y, the optimizer would choose
index_x (in the simple case, and most common case where only one index
is chosen as the access path to the data) since it was the first index
chosen to cost for a query plan.

Where I've seen this happen quite a lot is where a very non-selective
index would be chosen over a very selective index where the equi-SARG on
the first index had a value that falls outside the range of the
histogram stats.  In that case it's selectivity defaulted to 1 row which
was the same selectivity of the second index which was the "expected"
index to use.  Since both costs were the same, the first index was
chosen only because it had a lower indid than (ie created before) the
second index.  

So my advice has always been, create your most selective indexes first.
--------------A9ACDEC3595F6D6551E0757F
Content-Type: text/x-vcard; charset=us-ascii;
 name="ksherlo.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Sherlock, Kevin
Content-Disposition: attachment;
 filename="ksherlo.vcf"

begin:vcard 
n:Sherlock;Kevin
x-mozilla-html:FALSE
url:http://qwestdex.com
org:Qwest DEX;Information Managment
adr:;;;Omaha;NE;68114;USA
version:2.1
email;internet:ksherlo_AT_qwest.com
title:Staff Information Systems Engineer
x-mozilla-cpt:;3
fn:Kevin Sherlock
end:vcard

--------------A9ACDEC3595F6D6551E0757F--

0
Sherlock
12/10/2001 7:39:51 PM
Hi Bret,

No, All of our indexes are based on a single column. I've rarely seen the
incorrect index chosen. But we basically re-create the indexes either weekly
or every 2 weeks.


"Bret Halford" <bret@sybase.com> wrote in message
news:3C14F1DC.7EADDB07@sybase.com...
>
>
> Shew wrote:
>
> > Hi Bret,
> >
> > Sorry, question was:
> >
> > 1. Does it matter in what order indexes are created, i.e. most used
index
> > should be created 1st or it doesn't matter.
>
>
> In general, doesn't matter, as long as the indexes are all on just one
column.
>
> I seem to recall there might be some issue if you have, say, an index on
> (col1,col2)
> and another on (col1) - I think there are some processes which will use
just the
> first
> matching index they find - but the details escape me at the moment.  Is
that
> ever
> the case on your system?
>


0
Shew
12/10/2001 8:43:29 PM
Thanks Kevin, we'll do!

"Sherlock, Kevin" <ksherlo@qwest.com> wrote in message
news:3C150F78.EB486E89@qwest.com...
Shew wrote:
>
....
>
> 1. Does it matter in what order indexes are created, i.e. most used index
> should be created 1st or it doesn't matter.

Actually, it could matter in what order the indexes are created when it
comes to which index the optimizer chooses where two or more indexes are
deemed to "cost" the same.  Now, as of 11.9.x, the optimizer would
consider each candidate index defined in sysindexes by indid order
(probably because the sysindexes table is clustered on id, indid).

Consider two indexes index_x having indid (2), and index_y having indid
(3).  Both indexes could contain similar columns, or even completely
different columns:

If index_x was costed the same as index_y, the optimizer would choose
index_x (in the simple case, and most common case where only one index
is chosen as the access path to the data) since it was the first index
chosen to cost for a query plan.

Where I've seen this happen quite a lot is where a very non-selective
index would be chosen over a very selective index where the equi-SARG on
the first index had a value that falls outside the range of the
histogram stats.  In that case it's selectivity defaulted to 1 row which
was the same selectivity of the second index which was the "expected"
index to use.  Since both costs were the same, the first index was
chosen only because it had a lower indid than (ie created before) the
second index.

So my advice has always been, create your most selective indexes first.


0
Shew
12/12/2001 3:03:13 AM
Reply:

Similar Artilces:

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...

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 Hi, if you have a...

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...

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...

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 ...

windows ME questions, questions, questions.......
Hi, my next PC will be running Windows ME. The PC will be supplied via my work, so there's no choice here for me (ME?) (I think I would have preferred 98 SE). The harddisk (40GB matrox) will have ME installed, and both the Windows ME set-up files and an image of the initial Harddisk 'on a hidden partition'. Word has it that this partition is not seen by Format nor FDisk. Is this really possible? No Windows CD will be supplied. Seems a new way of MS to encourage working with licensed software only. Anyone familiar with this way of distributing an OS? Will I be able to ...

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...

Question about questions
Name: Edward Newill Email: ernewillatyahoodotcom Product: Thunderbird Summary: Question about questions Comments: Is there an email address that I can send a question too? I could not find one in the Service area of your site. I would like to know how I create signatures for my Thunderbird email? Best regards, E. Newill Browser Details: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727) From URL: http://hendrix.mozilla.org/ ...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

Index question
If I have a table with a foreign key into another table, will the optimizer use that as an index for other purposes as well, such as joins to other tables (not the one the foreignkey is referring to) or 'where' clauses. Or do I need to put in an explicit index for those purposes? The reason I'm asking is that a query which uses a join into this table was giving me a sequential scan of this table even though there was an index available in the form of the foreign key. I can't tell if that's just because the optimizer thought this would be faster (since it's ...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

Administrative question
Not quite a client question but I couldn't find a group for administration. Running ConsoleOne with GroupWise snapins. I'm unable to find how to access the Admin defined fields. There apparently has been some leftover information that we want to expunge. Thanks, Lilith Lilith, It appears that in the past few days you have not received a response to your posting. That concerns us, and has triggered this automated reply. Has your problem been resolved? If not, you might try one of the following options: - Do a search of our knowledgebase at http://support.novell....

A Question about ASE
My DBServer name is called 'bank', there are many files name like bank.001, bank.002,......and so on auto-generated by ASE in path $sybase/ASE-12_5/ .I want make the files generate into any other path that I appointed, how can i do? US Marine Corps wrote: > > My DBServer name is called 'bank', there are many files name like bank.001, > bank.002,......and so on auto-generated by ASE in path $sybase/ASE-12_5/ .I > want make the files generate into any other path that I appointed, how can i > do? These files are historical configuration files...

indexing question
I'm a bit unsure about a topic after reading the ASA 9 documentation: automatically generated indexes with respect to primary keys and foreign keys constraints. The documentation suggests that ASA 9 engine automatically indexes both primary and foreign key columns upon creation of the constraints. In the past we have practiced creation of foreign key column indexes manually, but now I am thinking this practice may now be counter-productive in terms of now forcing the database to maintain two indexes on the same set of 'foreign key' columns. Am I correct to assume...

Web resources about - Index Questions - sybase.ase.administration

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

South Africa: Questions Raised Over Former Minister's Signature On Nuclear Procurement Notice
As questions are raised by opposition parties over former energy minister Ben Martins' signature on the recently gazetted notice to move ahead ...

Question Of The Day: Greatest hood scoop ever?
Filed under: Automotive History Just about every manufacturer has installed some sort of scoop, functional or not, to at least one vehicle hood. ...

The 10 tech questions that stumped the masses this year
It was a year of new product releases, app updates, and fresh gadgets. Which, naturally, means that we were confused by a lot of things. Google ...

Review: Ten Economic Questions for 2015
At the end of each year, I post Ten Economic Questions for the coming year. I followed up with a brief post on each question. The goal was to ...

Questions raised about San Bernardino shooter's visa
Chair of House Judiciary Committee says immigration officials had insufficient evidence to issue Tashfeen Malik a visa to enter the U.S.

Shocker: Hillary Clinton Spins Question On Bullying In Order To Criticize Donald Trump
Shocker: Hillary Clinton Spins Question On Bullying In Order To Criticize Donald Trump

A very important question: What are your Christmas food traditions?
Merry Christmas! I have an important question for you: What do you cook and/or eat on Christmas? Thanksgiving is easy. There’s a standard repertoire ...

Once Upon a Time bosses answer your burning season 5B questions in the EW Hot Seat - EW.com
Once Upon a Time bosses answer your burning season 5B questions in the EW Hot Seat - EW.

'Love & Hip Hop' Season 6: Amina Not Pregnant? 4 Burning Questions We Want Answered Episode 3
"Love & Hip Hop" season six has already gotten off to a rather dramatic start for just about all of the cast members. And after watching the ...

Resources last updated: 12/27/2015 4:00:11 AM