Hi Pavel,

--- In firebird-support@yahoogroups.com, Pavel Menshchikov
<developer@...> wrote:

>
> Hello Tom,
>
> TC> I've a 10.6GB FDB (made up of 4 long thin tables) and find that
> TC> creating an index takes 3-4 hours+.
>
> Can you show us the index information (using gstat)?

Sure.


CREATE TABLE CANDIDATECVWORD (
CANDIDATEID INTEGER NOT NULL,
WORDID INTEGER NOT NULL,
WORDNO SMALLINT DEFAULT 0 NOT NULL,
LINENO SMALLINT DEFAULT 0 NOT NULL,
CHARNO SMALLINT DEFAULT 0 NOT NULL
);


CREATE TABLE CANDIDATENOTEWORD (
CANDIDATEID INTEGER NOT NULL,
WORDID INTEGER NOT NULL,
WORDNO SMALLINT DEFAULT 0,
LINENO SMALLINT DEFAULT 0,
CHARNO SMALLINT DEFAULT 0
);


CREATE TABLE CANDIDATEUNIQUECVWORD (
CANDIDATEID INTEGER NOT NULL,
WORDID INTEGER NOT NULL
);


CREATE TABLE CVWORD (
ID INTEGER NOT NULL,
CVWORD VARCHAR(50) NOT NULL,
OCCURS INTEGER DEFAULT 0
);


ALTER TABLE CANDIDATEUNIQUECVWORD ADD CONSTRAINT
PK_CANDIDATEUNIQUECVWORD PRIMARY KEY (WORDID, CANDIDATEID);
ALTER TABLE CVWORD ADD CONSTRAINT PK_CVWORDID PRIMARY KEY (ID);


/******************************************************************************/
/**** Indices
****/
/******************************************************************************/
CREATE INDEX CANDIDATECVWORDCANDID ON CANDIDATECVWORD (CANDIDATEID,
WORDID, WORDNO);
CREATE INDEX CANDIDATECVWORDID ON CANDIDATECVWORD (WORDID, WORDNO);
CREATE INDEX CANDIDATENOTEWORDCANDID ON CANDIDATENOTEWORD
(CANDIDATEID, WORDID, WORDNO);
CREATE INDEX CANDIDATENOTEWORDWORDID ON CANDIDATENOTEWORD (WORDID,
WORDNO);
CREATE UNIQUE INDEX IDXCVWORD ON CVWORD (CVWORD);


Database "D:\DB\Firebird\GCS\GRIP5_CV.FDB"

Database header page information:
Flags 0
Checksum 12345
Generation 153
Page size 16384
ODS version 10.1
Oldest transaction 129
Oldest active 130
Oldest snapshot 130
Next transaction 147
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 1
Creation date Jun 3, 2006 10:29:53

Variable header data:
Sweep interval: 20000
*END*


Database file sequence:
File D:\DB\Firebird\GCS\GRIP5_CV.FDB is the only file

Database log page information:
Creation date
Log flags: 2
No write ahead log

Next log page: 0

Variable log data:
Control Point 1:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Control Point 2:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Current File:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
*END*

Analyzing database pages ...

CANDIDATECVWORD (128)
Primary pointer page: 130, Index root page: 131
Data pages: 392704, data page slots: 392704, average fill: 62%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 392703
80 - 99% = 0

Index CANDIDATECVWORDCANDID (0)
Depth: 3, leaf buckets: 64292, nodes: 110742402
Average data length: 3.00, total dup: 1640, max dup: 1
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 64291

Index CANDIDATECVWORDID (1)
Depth: 3, leaf buckets: 42453, nodes: 110742402
Average data length: 0.00, total dup: 90759213, max dup: 10356
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 42452

CANDIDATENOTEWORD (130)
Primary pointer page: 135, Index root page: 136
Data pages: 2138, data page slots: 2138, average fill: 62%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 2137
80 - 99% = 0

Index CANDIDATENOTEWORDCANDID (0)
Depth: 2, leaf buckets: 377, nodes: 602686
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 377

Index CANDIDATENOTEWORDWORDID (1)
Depth: 2, leaf buckets: 233, nodes: 602686
Average data length: 0.00, total dup: 505470, max dup: 4060
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 232

CANDIDATEUNIQUECVWORD (131)
Primary pointer page: 137, Index root page: 138
Data pages: 130421, data page slots: 130421, average fill: 56%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 130420
60 - 79% = 0
80 - 99% = 0

Index PK_CANDIDATEUNIQUECVWORD (0)
Depth: 3, leaf buckets: 18582, nodes: 39517293
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 18582

CVWORD (129)
Primary pointer page: 132, Index root page: 133
Data pages: 5988, data page slots: 5988, average fill: 66%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 5987
80 - 99% = 0

Index IDXCVWORD (1)
Depth: 2, leaf buckets: 923, nodes: 1402097
Average data length: 4.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 922

Index PK_CVWORDID (0)
Depth: 2, leaf buckets: 601, nodes: 1402097
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 601

-----------------------------------------------------------