Re: "Transaction too long", but

>> I want to create an index on a table which contains 25.000.000 rows. >> The index is of the type "varchar" with a max. length of 60 bytes. >> >> Because I got a "transaction too long" error, I turned logging off. >> Nevertheless I got the same error again (error-no. 458). >> >> The statement: >> isql $DATABASE - <<! >> CREATE INDEX titel_desk ON titel_worte (desk) IN dbs_4;>> ! One thought springs to mind from experience building an index on a table with a similar number of rows (version 7.10.UC2, HP-UX 9.04). It appears that the engine scans the base table and extracts keys and rowids into a 64 KB buffer and sorts it in memory. It then writes these 32 pages to the temporary dbspace as a distinct tablespace (verify this with "onstat -t"). This continues, adding tablespaces until the entire table has been scanned. After the initial pass, the engine then goes through the resulting tables and merges them two at a time, then repeats until, eventually, there are only two tables left. The final merge of these two tables then builds the index. When I did this on 23M rows for an integer key, it created over 4100 tablespaces. "So what?", you ask. In chapter 20 of the Admin Guide (ver 7.1), there is a list of events which are always logged, even if the database has no logging. These include the "create table" statements and addition of extents to existing tables. I'm just guessing here, but this MAY include the temporary tablespaces created during an index build. Now that I've used all this bandwidth leading up to this, how large are your logical logs? Given your larger key size, I can imagine your index build creating well over 50,000 tablespaces, of which the creation of each might be logged. Of course, I might be wrong. Try it and run "onstat -t" to watch how many tablespaces are created and "onstat -l" to follow usage of logical logs. If the logs fill up, run onlog and see what is filling them. >> By the way: the creation on an index for a "smallint"-field worked >> fine for the same table. There also already exists a unique composit >> Index consisting of the above mentioned varchar "desk" and the a.m. >> "smallint"-field, which was created when the table was created. Not trying to second-guess you here, but if you already have "desk" at the head of a composite index, why do you need to create a separate index with only "desk"? Won't your queries use the composite? >> I can't really experiment with this one because of the size of the >> table (and actually the size of the whole database). My sympathies. Have a similar situation here. Let us know if this helped, or what the final solution was. Mark Collins mcollins@us.dhl.com