473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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 1659
In article <11*********************@d34g2000cwd.googlegroups. 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*********************@d34g2000cwd.googlegroups. 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*********************@d34g2000cwd.googlegroups.c om...
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*********************@d34g2000cwd.googlegro 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
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
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...
1
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...
8
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...
8
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...
15
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...
1
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...
16
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...
3
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
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.