MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

SQL Alter Table Add Column - Indexes



fixed

It has been my experience that when I add a column to a table via alter table that the indexes would also rebuild(?) and pick up the new column. However that does not seem to the case with the latest file implementation I ran. Some of the indexes have the new field, some don't. We don't use dependencies.

Example of an index that picked up the new field:
CREATE INDEX PkgAsnDay3
ON PkgAsnDayP ( CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID, ROWID);

Examples of indexes that didn't pick up the new field:
CREATE INDEX PkgAsnDay4
ON PkgAsnDayP (Cust#, BTN#, TID, PkgID, PkgLvl, RatePer, UnlFlg, EffDte,
ThrDte, ExpDte, PkgTyp)
RCDFMT PkgAssgnF;

CREATE INDEX PkgAsnDay5
ON PkgAsnDayP (CUST#, PKGLVL, BTN#, TID, SEQ#, PKGID)
Where PkgLvl <> '@'
RCDFMT PkgAssgnF ADD ALL COLUMNS;

Here is the alter table statement. (There was also a Label On statement .)
Alter Table PkgAssgnP
Add Column Overage Decimal(15,4) NOT NULL DEFAULT 0;

LABEL ON COLUMN PkgAssgnP(
PkgLvl IS 'Package Level' ,
Overage Is 'Overage Amount '
);

I did find this in the SQL Reference under Alter Table:
"Indexes may need to be rebuilt. (Any indexes that need to be rebuilt are rebuilt asynchronously by database server jobs.)
An index does not need to be rebuilt when columns are added to a table or when columns are dropped or altered and those columns are not referenced in the index key.
Altering a column that is used in the key of an index or constraint usually requires the index to be rebuilt. The index does not need to be rebuilt, however, in the following cases:
- The length attribute of a VARCHAR or VARGRAPHIC key is increasing.
- The CCSID of a column is changing but no conversion is necessary between the old and new CCSID. For example, if one CCSID is 65535"

Overage is not a key field in any index, yet PkgAsnDay3 was rebuilt. I'm a bit perplexed.

Any thoughts? We're on IBM i 7.1.

Kurt Anderson
Sr. Programmer/Analyst - Application Development, Service Delivery Platform
[cid:image002.jpg@01CD16F9.25D27630]
web: www.customcall.com<http://www.customcall.com/> | email: kurt.anderson@xxxxxxxxxxxx<mailto:kurt.anderson@xxxxxxxxxxxx>

A Division of Enghouse Systems Ltd.<http://www.enghouse.com/> (TSX:ESL)






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact