473,573 Members | 5,078 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help regarding INDEX

Hi group,

I have a table say with 10000 records and 10 columns initially, I
created an index say i1, after few days I altered the table by adding
10 new columns and 90000 rows of records summing up to 100000 records.
My doubt is regarding the index is Do I have to create a new index ? or
can I use the same index which I created first, or do I have to make
any changes to existing index like rebuild etc...?

What needs to be done for the index...

Thanks in advance,
VJ

Sep 13 '06 #1
5 1672
In article <11************ *********@d34g2 000cwd.googlegr oups.com>,
vi******@gmail. com says...
Hi group,

I have a table say with 10000 records and 10 columns initially, I
created an index say i1, after few days I altered the table by adding
10 new columns and 90000 rows of records summing up to 100000 records.
My doubt is regarding the index is Do I have to create a new index ? or
can I use the same index which I created first, or do I have to make
any changes to existing index like rebuild etc...?

What needs to be done for the index...
Why did yo create the index?
Sep 13 '06 #2

Gert van der Kooij wrote:
In article <11************ *********@d34g2 000cwd.googlegr oups.com>,
vi******@gmail. com says...
Hi group,

I have a table say with 10000 records and 10 columns initially, I
created an index say i1, after few days I altered the table by adding
10 new columns and 90000 rows of records summing up to 100000 records.
My doubt is regarding the index is Do I have to create a new index ? or
can I use the same index which I created first, or do I have to make
any changes to existing index like rebuild etc...?

What needs to be done for the index...

Why did yo create the index?
HI,

This question is just a scenario...say we created the index for
performance reason...so what needs to be done for index?

thanks.

Sep 13 '06 #3
vj_dba wrote:
This question is just a scenario...say we created the index for
performance reason...so what needs to be done for index?
Indexes are maintained automatically. So you don't have to do anything.

What may be worthwhile is to reorg the table and the index once in a while.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 13 '06 #4
You have to understand that because you did not do a reorg after the alter,
the new columns are physically separated from their corresponding existing
rows.
Every time you touch the cols. of the exidsting rows and new cols. , you
have to do teo physical page reads. Performance will not be nice, even with
the existing index.

What you need to do is to do an offline reorg. This will rebuild each row
with all of ots columns and will also automatically rebuild the indexes
properly.
Given 100,000 rows the reorg should not take very long.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"vj_dba" <vi******@gmail .coma écrit dans le message de news:
11************* ********@d34g20 00...legro ups.com...
Hi group,

I have a table say with 10000 records and 10 columns initially, I
created an index say i1, after few days I altered the table by adding
10 new columns and 90000 rows of records summing up to 100000 records.
My doubt is regarding the index is Do I have to create a new index ? or
can I use the same index which I created first, or do I have to make
any changes to existing index like rebuild etc...?

What needs to be done for the index...

Thanks in advance,
VJ
Sep 13 '06 #5
"vj_dba" <vi******@gmail .comwrote in message
news:11******** *************@d 34g2000cwd.goog legroups.com...
Hi group,

I have a table say with 10000 records and 10 columns initially, I
created an index say i1, after few days I altered the table by adding
10 new columns and 90000 rows of records summing up to 100000 records.
My doubt is regarding the index is Do I have to create a new index ? or
can I use the same index which I created first, or do I have to make
any changes to existing index like rebuild etc...?

What needs to be done for the index...

Thanks in advance,
VJ
Since the index comprised of one or more of the original 10 columns, if you
add new columns the index you created is still there and does not have to be
recreated or changed in any way (assuming that the columns are still correct
and you do not need any of the new columns in the index).

Anytime you alter a table and add new columns, it is best to reorg the
table. The reason is that adding new columns does not add space for the
columns until you try to update the row, which could get messy since the row
will probably not fit back in the same space where it was. But this is
strictly a performance issue.

A regular (offline) reorg on the table will reorg the table and all indexes.
Sep 13 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2945
by: Erich Trowbridge | last post by:
Has anybody seen this tool? It is awesome. check out http://vw.vermeer.org/ . It's a php front end for large-scale syslog deployments. It makes managing syslog in large networks a snap. The idea is to fifo pipe network syslog into a MySQL database backend. The php scripts reference the database, and print stuff to the screen. My Info ->...
1
2004
by: udayt | last post by:
Hi, My application needs to calculate the sort order of an index key (whether the index key is descending or ascending). The user may connect to MS7 or MS2K servers. As far as I know, the descending indices are supported in version 8 i.e SQL 2000. In MS2K, I can get the index key information by SELECT INDEXKEY_PROPERTY(table_ID ,...
8
5222
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is developing a web-based application, one part of which involves allowing the user the ability to page through transaction "history" information. The...
8
2577
by: Pete Davis | last post by:
First of all, I apologize for cross-posting to so many groups, but clearly there are only 2 people on the planet that understand MS Accessibility in ..NET and I'm hoping I might reach just one of them by cross posting to 6 groups because I've already posted to 4 others with no luck. Our company has a number of custom controls that we're...
15
6461
by: rAinDeEr | last post by:
Suppose i have a table which holds thousands of records with the following structure CREATE TABLE "test "."T_CNTRY" ( "CNTRY_CDE" CHAR(2) NOT NULL , "CNTRY_NAME" VARCHAR(50) ) and i have Created an index like below ::
1
3200
by: teddymeu | last post by:
hi guys I posted the other day regarding a solution i needed to design, im new to development an asp,net and built a local post office search tool using asp.net 2. vb in visual studio, using an sql express mdf database(this means the database is transferable with the solution even if the server doesnt have sql server on it) The app works fine,...
16
4826
by: Okonita via DBMonster.com | last post by:
Hi all, I am comming along with all this Linus/DB2/scripting business...I am no longer scared of it!! (LOL). But, I need to create a .ksh script that does a REORGCHK and output only tables recommended for reorg. My goal is to reorgchk and run reorgs based on entries in this reorg file as shown in the example below. I have tried my hand at...
3
3942
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID = ?
0
7709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8039
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8218
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8091
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3744
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1330
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.