CREATE TABLE "CorrectText"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"OrgStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"NewStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"CorrectionMode" INTEGER DEFAULT 0,
CONSTRAINT "ID_Key" PRIMARY KEY ("ID")
)
VERSION 2.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 32768
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Cannot Create Index

CREATE INDEX "CorrectText_OrgStr"
ON "CorrectText"
("OrgStr" COLLATE "UNI_CI" ASC)

Andrew,

Change the CREATE TABLE statement to this:

CREATE TABLE "CorrectText"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"OrgStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"NewStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"CorrectionMode" INTEGER DEFAULT 0,
CONSTRAINT "ID_Key" PRIMARY KEY ("ID")
)
VERSION 2.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 12288  <<<<<<<<< 4096-byte higher value
BLOB BLOCK SIZE 32768
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

Andrew,

Change the CREATE TABLE statement to this:

CREATE TABLE "CorrectText"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"OrgStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"NewStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"CorrectionMode" INTEGER DEFAULT 0,
CONSTRAINT "ID_Key" PRIMARY KEY ("ID")
)
VERSION 2.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 12288  <<<<<<<<< 4096-byte higher value
BLOB BLOCK SIZE 32768
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Tim Young
Elevate Software
www.elevatesoft.com

Can I ALTER TABLE TO CHANGE INDEX SIZE (it contains a lot of data) ?

Can I ALTER TABLE TO CHANGE INDEX SIZE (it contains a lot of data) ?

Andrew


Even if you have to DROP the various indices and recreate them it should not loose any data so just try it. Naturally, make a copy of the data first, just in case.

Roy Lambert

Thanks Roy,

I was waiting for Tim to explain why it only works with INDEX SIZE 8240 ? (still waiting)

CREATE TABLE "CorrectText"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"OrgStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"NewStr" VARCHAR(1022) COLLATE "UNI_CI" DEFAULT '',
"CorrectionMode" INTEGER DEFAULT 0,
CONSTRAINT "ID_Key" PRIMARY KEY ("ID")
)
VERSION 2.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 8240
BLOB BLOCK SIZE 32768
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

8240 if built by edbMgr

8256 if built by Delphi

Please explain

On 8/25/2021 4:16 PM, Andrew Hill wrote:
>
> I was waiting for Tim to explain why it only works with INDEX SIZE 8240 ? (still waiting)
>

Not Tim but not sure what exactly you're asking !?

You have a table with the default index page size (8192)

You're trying to create indexes that cannot fit into table index page -
the error tells you that index page must be at least 8240.

Any larger value would also work - in fact Tim suggested to bump it by
full 4K (to 12288) which would also leave you some room in the future if
you decide to add more secondary indexes.

Raul

Raul, thanks for your answer HOWEVER this does not explain why edbMgr works with a less value and Delphi code requires a larger one ?

Raul


>You're trying to create indexes that cannot fit into table index page -
>the error tells you that index page must be at least 8240.

Whilst I get that "it" doesn't fit in I do not understand what "it" is. Can you explain? Just for interest rather than any need to know.

Roy