| Subject | Re: "index root page is full" error populating temp table |
|---|---|
| Author | hvlad |
| Post date | 2011-02-25T12:23:53Z |
--- In firebird-support@yahoogroups.com, Ken Coffman wrote:
Do you issue many CREATE INDEX or ALTER INDEX ACTIVE statements for the same table ?
If you can provide me with reproducible example i will find the exact reason.
Regards,
Vlad
>It is *very* outdated
> Hi,
>
> I am currently working on a data extract project and have hit upon a problem that seems like a Firebird limitation but I wish to confirm if this is the case.
>
> My development environment:
>
> Firebird Version:
>
> 2.1.2 (x86)
> The extract performs various aggregations on data to pull statistics and output to XML for import into 3rd party software. We had initially implemented elaborate queries for each section of the extract but hit major performance issues - it would never finish. We discovered that by breaking the queries into pieces and executing them in stored procedures to build the data incrementally, it would run much faster. To achieve this we have employed a few temporary tables which we fill with the aggregated data and then do a simple select as the final step. There are currently 3 or 4 temp tables with an average of 200 columns and a few indexes where required for joins and sorting.Why do you think it is related with GTT ?
>
> Unfortunately with larger customer databases we have found the first stored proc fails after a minute with this error:
>
> ISC ERROR CODE:335544351
>
> ISC ERROR MESSAGE:
> unsuccessful metadata update
> cannot add index, index root page is full.
Do you issue many CREATE INDEX or ALTER INDEX ACTIVE statements for the same table ?
> The point at which this occurs seems to vary between test platforms. It occurs even on fresh boot with only 1 active connection to the DB. Can anyone tell me how this could be happening?This is or bug in Firebird or you doing something to overfill index root page. For example often creation\deletion indices. And i think you doing this - at least error "unsuccessful metadata update" points in this direction.
If you can provide me with reproducible example i will find the exact reason.
> Are there any Firebird config settings that could help?None.
> My guess is that we may be pushing the limits of temp tables; it would be nice to find out what those practical limits are.The same as for regular tables.
> Given that these are in-memory tablesNo, they are not in-memory tables
> I have considered dropping the indexes to see if that works without too much sacrifice to performance.I don't think it is a good idea :)
Regards,
Vlad