Index on a table with 243,131,879 rows
Hello,
We have a table with 7 existing indexes and I want to create a unique index.
The setup is :-
IBM Informix Dynamic Server Version 7.31.FD8 -- On-Line -- Up 2 days 04:34:2
0 -- 507768 Kbytes
1] I used a create stmt :-
CREATE UNIQUE INDEX i_event ON test_event (combo_idnum, tpt_idnum,
tpo_idnum, event_num) IN padbdbs9
2] Also, I added 10 GB of tempdbs BUT still index is NOT getting created.
3] There was some assertion failure on this index BUT when I ran oncheck -cI ,
the check did NOT throw up any error.
Please give me the possible solution of How to create this index ???
Here's the schema of the table "test_event" :-
{ TABLE "padbodbc".test_event row size = 39 number of columns = 10 index size =
63
}
create table "padbodbc".test_event
(
event_idnum serial not null ,
combo_idnum integer not null ,
datecode integer not null ,
tpt_idnum integer not null ,
tpo_idnum integer not null ,
tporev_idnum integer not null ,
stncfg_idnum integer not null ,
event_num smallint not null ,
event_value float,
event_status char(1) not null ,
primary key (event_idnum) constraint "padbodbc".c_event_pk
);
revoke all on "padbodbc".test_event from "public";
create index "padbodbc".i_eve_dc on "padbodbc".test_event (datecode);
alter table "padbodbc".test_event add constraint (foreign key
(combo_idnum) references "padbodbc".test_combo on delete
cascade constraint "padbodbc".c_event_f1);
alter table "padbodbc".test_event add constraint (foreign key
(tpt_idnum) references "padbodbc".test_point constraint "padbodbc"
.c_event_f2);
alter table "padbodbc".test_event add constraint (foreign key
(tpo_idnum) references "padbodbc".test_otemplate constraint
"padbodbc".c_event_f3);
alter table "padbodbc".test_event add constraint (foreign key
(tporev_idnum) references "padbodbc".tpo_revision constraint
"padbodbc".c_event_f4);
alter table "padbodbc".test_event add constraint (foreign key
(stncfg_idnum) references "padbodbc".station_config constraint
"padbodbc".c_event_f5);
Let me know ...
sagar
10 GB
table without fragmentation? Interesting. On an instance with 500MB
of memory? You might be a little undersized.
Do you have the 6.2GB free space required in padbdbs9?
What is the assertion failure you are seeing?
j.
----- Original Message -----
From: "SAGAR THANAWALA" <sagarthanawala@yahoo.com>
To: <ids@iiug.org>
Sent: Monday, May 09, 2005 6:40 AM
Subject: Index on a table with 243,131,879 rows [4884]
> Hello,
>
> We have a table with 7 existing indexes and I want to create a unique
index. The setup is :-
>
> IBM Informix Dynamic Server Version 7.31.FD8 -- On-Line -- Up 2 days
04:34:2
> 0 -- 507768 Kbytes>
>
> 1] I used a create stmt :-
>
>
> CREATE UNIQUE INDEX i_event ON test_event (combo_idnum, tpt_idnum,
> tpo_idnum, event_num) IN padbdbs9>
> 2] Also, I added 10 GB of tempdbs BUT still index is NOT getting created.
>
> 3] There was some assertion failure on this index BUT when I ran
oncheck -cI ,> the check did NOT throw up any error.
>
> Please give me the possible solution of How to create this index ???
>
> Here's the schema of the table "test_event" :-
>
> { TABLE "padbodbc".test_event row size = 39 number of columns = 10 index
size =
> 63
> }
> create table "padbodbc".test_event
> (
> event_idnum serial not null ,
> combo_idnum integer not null ,
> datecode integer not null ,
> tpt_idnum integer not null ,
> tpo_idnum integer not null ,
> tporev_idnum integer not null ,
> stncfg_idnum integer not null ,
> event_num smallint not null ,
> event_value float,
> event_status char(1) not null ,
> primary key (event_idnum) constraint "padbodbc".c_event_pk
> );
> revoke all on "padbodbc".test_event from "public";>
> create index "padbodbc".i_eve_dc on "padbodbc".test_event (datecode);
>
>
> alter table "padbodbc".test_event add constraint (foreign key
> (combo_idnum) references "padbodbc".test_combo on delete
> cascade constraint "padbodbc".c_event_f1);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tpt_idnum) references "padbodbc".test_point constraint "padbodbc"
> .c_event_f2);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tpo_idnum) references "padbodbc".test_otemplate constraint
> "padbodbc".c_event_f3);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tporev_idnum) references "padbodbc".tpo_revision constraint
> "padbodbc".c_event_f4);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (stncfg_idnum) references "padbodbc".station_config constraint
> "padbodbc".c_event_f5);
>
>
>
>
> Let me know ...
> sagar
>
>
SAGAR THANAWALA said:
> Hello,
>
> We have a table with 7 existing indexes and I want to create a unique
> index. The setup is :-
>
> IBM Informix Dynamic Server Version 7.31.FD8 -- On-Line -- Up 2 days
> 04:34:2
> 0 -- 507768 Kbytes>
>
> 1] I used a create stmt :-
>
>
> CREATE UNIQUE INDEX i_event ON test_event (combo_idnum, tpt_idnum,
> tpo_idnum, event_num) IN padbdbs9>
> 2] Also, I added 10 GB of tempdbs BUT still index is NOT getting created.
>
> 3] There was some assertion failure on this index BUT when I ran oncheck
> -cI ,
> the check did NOT throw up any error.
>
> Please give me the possible solution of How to create this index ???
>
> Here's the schema of the table "test_event" :-
>
> { TABLE "padbodbc".test_event row size = 39 number of columns = 10 index
> size =
> 63
> }
> create table "padbodbc".test_event
> (
> event_idnum serial not null ,
> combo_idnum integer not null ,
> datecode integer not null ,
> tpt_idnum integer not null ,
> tpo_idnum integer not null ,
> tporev_idnum integer not null ,
> stncfg_idnum integer not null ,
> event_num smallint not null ,
> event_value float,
> event_status char(1) not null ,
> primary key (event_idnum) constraint "padbodbc".c_event_pk
> );
> revoke all on "padbodbc".test_event from "public";>
> create index "padbodbc".i_eve_dc on "padbodbc".test_event (datecode);
>
>
> alter table "padbodbc".test_event add constraint (foreign key
> (combo_idnum) references "padbodbc".test_combo on delete
> cascade constraint "padbodbc".c_event_f1);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tpt_idnum) references "padbodbc".test_point constraint "padbodbc"
> .c_event_f2);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tpo_idnum) references "padbodbc".test_otemplate constraint
> "padbodbc".c_event_f3);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (tporev_idnum) references "padbodbc".tpo_revision constraint
> "padbodbc".c_event_f4);
>
> alter table "padbodbc".test_event add constraint (foreign key
> (stncfg_idnum) references "padbodbc".station_config constraint
> "padbodbc".c_event_f5);
1. Set PSORT_DBTEMP to a set of directories on different spindles where
there is sufficient space for the index builds. Use the same number of
directories as you set PSORT_NPROCS to.
2. Set PSORT_NPROCS to (number of CPUs - 1)
3. Set PDQPRIORITY to 50
Try again.
--
Bye now,
Obnoxio
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
When I was a kid, we walked 6 miles to school every day, sometimes in the
rain or snow. Man, did we feel stupid when we found out there was a bus
...
It would help tremendously if you would post the exact error/assertion/message
you are receiving when trying to build this index!
Art S. Kagel
----- Original Message -----
From: Sagar Thanawala <sagarthanawala@yahoo.com>
At: 5/ 9 8:54
Hello,
We have a table with 7 existing indexes and I want to create a unique index.
The setup is :-
IBM Informix Dynamic Server Version 7.31.FD8 -- On-Line -- Up 2 days 04:34:2
0 -- 507768 Kbytes
1] I used a create stmt :-
CREATE UNIQUE INDEX i_event ON test_event (combo_idnum, tpt_idnum,
tpo_idnum, event_num) IN padbdbs9
2] Also, I added 10 GB of tempdbs BUT still index is NOT getting created.
3] There was some assertion failure on this index BUT when I ran oncheck -cI ,
the check did NOT throw up any error.
Please give me the possible solution of How to create this index ???
Here's the schema of the table "test_event" :-
{ TABLE "padbodbc".test_event row size = 39 number of columns = 10 index size =
63
}
create table "padbodbc".test_event
(
event_idnum serial not null ,
combo_idnum integer not null ,
datecode integer not null ,
tpt_idnum integer not null ,
tpo_idnum integer not null ,
tporev_idnum integer not null ,
stncfg_idnum integer not null ,
event_num smallint not null ,
event_value float,
event_status char(1) not null ,
primary key (event_idnum) constraint "padbodbc".c_event_pk
);
revoke all on "padbodbc".test_event from "public";
create index "padbodbc".i_eve_dc on "padbodbc".test_event (datecode);
alter table "padbodbc".test_event add constraint (foreign key
(combo_idnum) references "padbodbc".test_combo on delete
cascade constraint "padbodbc".c_event_f1);
alter table "padbodbc".test_event add constraint (foreign key
(tpt_idnum) references "padbodbc".test_point constraint "padbodbc"
.c_event_f2);
alter table "padbodbc".test_event add constraint (foreign key
(tpo_idnum) references "padbodbc".test_otemplate constraint
"padbodbc".c_event_f3);
alter table "padbodbc".test_event add constraint (foreign key
(tporev_idnum) references "padbodbc".tpo_revision constraint
"padbodbc".c_event_f4);
alter table "padbodbc".test_event add constraint (foreign key
(stncfg_idnum) references "padbodbc".station_config constraint
"padbodbc".c_event_f5);
Let me know ...
sagar