Index on a table with 243,131,879 rows
Sagar Thanawala could not create a unique index on a table of 243 million rows on IDS 7.31.FD8, despite 10GB added temp space and an assertion failure. Jack Parker questioned the lack of fragmentation, memory and free space. Obnoxio suggested PSORT_DBTEMP, PSORT_NPROCS (CPUs minus 1) and PDQPRIORITY 50. Art Kagel asked for the exact error. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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