alter index to not cluster for a primary key
Hi
All,
Our system is running IDS 9.21-FC3X-1 on HP-UX 11.0 (64-bit)
I have a problem attempting to alter an index from clustered to not
clustered for the following tables schema definition:
create table "DBO".org_financial_char
(
credit_appl_id decimal(12,0) not null constraint
"DBO".c_financial_cha_n1,
person_id smallint not null constraint "DBO".c_financial_cha_n2,
fin_char_id smallint not null constraint "DBO".c_financial_cha_n3,
......
...... 45 other columns
......
last_upd_datetime datetime year to fraction(5),
primary key (credit_appl_id,person_id,fin_char_id) constraint
"DBO".c_financial_cha_p
) extent size 6750 next size 6750 lock mode row;
revoke all on "DBO".org_financial_char from "public";
create index "DBO".i1_financial_char on "DBO".org_financial_char
(fin_char_type) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_2 on "DBO".org_financial_char
(credit_appl_id,person_id,person_address_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_3 on "DBO".org_financial_char
(credit_appl_id,co_holder_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_4 on "DBO".org_financial_char
(credit_appl_id,person_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_5 on "DBO".org_financial_char
(credit_appl_id,person_id,liab_id) using btree in dbs10dat1;
Now, as the primary key was created at the table level, the Informix engine
has assigned it an index name of 154_643.
I can see the index within the sysindexes view of the database, and when I
use dbaccess to display info about the
table it shows it is clustered:
Index_name Owner Type/Clstr Access_Method Columns
i1_financial_char DBO dupls/No btree fin_char_type
154_643 DBO unique/Yes btree credit_appl_id
person_id
fin_char_id
i_financial_cha_2 DBO dupls/No btree credit_appl_id
person_id
person_address_id
i_financial_cha_3 DBO dupls/No btree credit_appl_id
co_holder_id
How can I alter this index to not clustered ?
I get a syntax error when running the following from dbaccess:
alter index " 154_643" to not cluster;
Any help would be great !
Damion Reeves
Database Administrator
EDS Australia
It seems
from the IDS 9.2 SQL syntax manual that:
The ALTER INDEX statement works only on indexes that are created with the
CREATE INDEX statement; it does not affect constraints that are created withthe CREATE TABLE statement.
You cannot alter the index of a temporary table.
Prashant
-----Original Message-----
From: Reeves, Dam.... [mailto:damion.reeves@eds.com]
Sent: Monday, 3 March 2003 3:04 PM
To: ids@iiug.org
Subject: alter index to not cluster for a primary key [545]
Hi All,
Our system is running IDS 9.21-FC3X-1 on HP-UX 11.0 (64-bit)
I have a problem attempting to alter an index from clustered to not
clustered for the following tables schema definition:
create table "DBO".org_financial_char
(
credit_appl_id decimal(12,0) not null constraint
"DBO".c_financial_cha_n1,
person_id smallint not null constraint "DBO".c_financial_cha_n2,
fin_char_id smallint not null constraint "DBO".c_financial_cha_n3,
......
...... 45 other columns
......
last_upd_datetime datetime year to fraction(5),
primary key (credit_appl_id,person_id,fin_char_id) constraint
"DBO".c_financial_cha_p
) extent size 6750 next size 6750 lock mode row;
revoke all on "DBO".org_financial_char from "public";
create index "DBO".i1_financial_char on "DBO".org_financial_char
(fin_char_type) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_2 on "DBO".org_financial_char
(credit_appl_id,person_id,person_address_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_3 on "DBO".org_financial_char
(credit_appl_id,co_holder_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_4 on "DBO".org_financial_char
(credit_appl_id,person_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_5 on "DBO".org_financial_char
(credit_appl_id,person_id,liab_id) using btree in dbs10dat1;
Now, as the primary key was created at the table level, the Informix engine
has assigned it an index name of 154_643.
I can see the index within the sysindexes view of the database, and when I
use dbaccess to display info about the
table it shows it is clustered:
Index_name Owner Type/Clstr Access_Method Columns
i1_financial_char DBO dupls/No btree fin_char_type
154_643 DBO unique/Yes btree credit_appl_id
person_id
fin_char_id
i_financial_cha_2 DBO dupls/No btree credit_appl_id
person_id
person_address_id
i_financial_cha_3 DBO dupls/No btree credit_appl_id
co_holder_id
How can I alter this index to not clustered ?
I get a syntax error when running the following from dbaccess:
alter index " 154_643" to not cluster;
Any help would be great !
Damion Reeves
Database Administrator
EDS Australia
**********************************************************************
CAUTION: This message may contain confidential information intended only for
the use of the addressee named above. If you are not the intended recipient of
this message, any use or disclosure of this message is prohibited. If you
received this message in error please notify Mail Administrators immediately.
You must obtain all necessary intellectual property clearances before doing
anything other than displaying this message on your monitor. There is no
intellectual property licence. Any views expressed in this message are those
of the individual sender and may not necessarily reflect the views of
Woolworths Ltd.
**********************************************************************
Damion;
You can't alter the system-created index.
If you really need to alter that index to cluster, what you'll have to do
is alter the table to drop the primary key constraint, then manually create
a unique composite index on the primary key columns, and then alter the
table to add the primary key constraint back.
Then you will be able to use the index for cluster.
WARNING: When you drop the primary key constraint on the
org_financial_char table, if there are any foreign keys which reference
that primary key, they will also automatically be dropped. This means you
will have to find all the foreign keys which reference this primary key
_before_ you drop the primary key constraint, then later also rebuild them.
Note: I would ALWAYS recommend explicitly creating named indexes on the
columns used for primary (unique index) and foreign (duplicate index) keys
first, then doing an ALTER TABLE to add the constraints later.
HTH...
Mike
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Mike Lowe
Certified Consulting Education Specialist
Data Management Solutions
IBM Software Group
Tel: 303-773-5216 Tie Line: 565-5216
e-Fax: 413-674-2415
mlowe0@us.ibm.com
WWW: ibm.com/software/data
"Reeves, Dam...."
<damion.reeves@ed To: ids@iiug.org
s.com> cc:
Sent by: Subject: alter index to not cluster for a primary key [545]
forum.subscriber@
iiug.org
03/02/2003 09:04
PM
Hi All,
Our system is running IDS 9.21-FC3X-1 on HP-UX 11.0 (64-bit)
I have a problem attempting to alter an index from clustered to not
clustered for the following tables schema definition:
create table "DBO".org_financial_char
(
credit_appl_id decimal(12,0) not null constraint
"DBO".c_financial_cha_n1,
person_id smallint not null constraint "DBO".c_financial_cha_n2,
fin_char_id smallint not null constraint "DBO".c_financial_cha_n3,
......
...... 45 other columns
......
last_upd_datetime datetime year to fraction(5),
primary key (credit_appl_id,person_id,fin_char_id) constraint
"DBO".c_financial_cha_p
) extent size 6750 next size 6750 lock mode row;
revoke all on "DBO".org_financial_char from "public";
create index "DBO".i1_financial_char on "DBO".org_financial_char
(fin_char_type) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_2 on "DBO".org_financial_char
(credit_appl_id,person_id,person_address_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_3 on "DBO".org_financial_char
(credit_appl_id,co_holder_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_4 on "DBO".org_financial_char
(credit_appl_id,person_id) using btree in dbs10dat1 ;
create index "DBO".i_financial_cha_5 on "DBO".org_financial_char
(credit_appl_id,person_id,liab_id) using btree in dbs10dat1;
Now, as the primary key was created at the table level, the Informix engine
has assigned it an index name of 154_643.
I can see the index within the sysindexes view of the database, and when I
use dbaccess to display info about the
table it shows it is clustered:
Index_name Owner Type/Clstr Access_Method Columns
i1_financial_char DBO dupls/No btree fin_char_type
154_643 DBO unique/Yes btree credit_appl_id
person_id
fin_char_id
i_financial_cha_2 DBO dupls/No btree credit_appl_id
person_id
person_address_id
i_financial_cha_3 DBO dupls/No btree credit_appl_id
co_holder_id
How can I alter this index to not clustered ?
I get a syntax error when running the following from dbaccess:
alter index " 154_643" to not cluster;
Any help would be great !
Damion Reeves
Database Administrator
EDS Australia