Include columns after creating an index

Question

Include columns after creating an index

Zsolt Szabo

SSC Veteran

Points: 255
More actions
April 10, 2008 at 4:53 am

#181286

I need to include columns to an index after creating it. So I need to replace this command
CREATE INDEX
[INDEX01] ON [TABLE01] (Column01, Column02, Column03)
INCLUDE (Column04, Column05, Column06, Column07)
with
CREATE INDEX
[INDEX01] ON [TABLE01] (Column01, Column02, Column03)
and somehow include necessary columns after this command.
Could you help me, please?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Zsolt Szabo (4/10/2008)
I need to include columns to an index after creating it. So I need to replace this command
CREATE INDEX
[INDEX01] ON [TABLE01] (Column01, Column02, Column03)
INCLUDE (Column04, Column05, Column06, Column07)
with
CREATE INDEX
[INDEX01] ON [TABLE01] (Column01, Column02, Column03)
and somehow include necessary columns after this command.
Could you help me, please?

You cannot change the columns in an index using an ALTER INDEX statement, so what you will have to do is:

DROP INDEX INDEX01

CREATE INDEX INDEX01 .. with the columns you wish the index had

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Zsolt Szabo SSC Veteran Points: 255 More actions · Answer 2

I hoped I can make it without dropping.

Thanks for the answer.

shamshudheen SSCrazy Points: 2390 More actions · Answer 3

Use ALTER INDEX to perform the following maintenance tasks for a Text index:

Rename the index.

Rebuild the index using different preferences. .

Resume a failed index operation (creation/optimization). .

Process DML in batch (synchronize). .

Optimize the index. .

Add stopwords to the index. .

check this link for more detail

http://lbd.epfl.ch/f/teaching/courses/oracle8i/inter.815/a67843/csql.htm#14189

I dont think u can add are change fields to indexed, best is drop and recreate again

Zsolt Szabo SSC Veteran Points: 255 More actions · Answer 4

Yes that is what I did. Recreated the indexes. Fortunately I don't have to do it too often so it's a good solution for me. Thanks.