Re: "Transaction too long", but

In article <555la8$cq4@cssun.mathcs.emory.edu>, "Collins, Mark" <MCOLLINS@iahnbcis.attmail.com> writes >>> 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;>>> ! > Make sure no-one else is within a transaction at the time. >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. > This will only happen if the server runs out of memory. Check SHMTOTAL is not set. Online should be able to use up to 5Mb of memory per sort. When this is exceeded you get new tablespaces created as above and they get merged as required. >"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 Yes UNLESS you create a temporary dbspace (when yuo create it set the Temporary flag to N. Then set DBSPACETEMP in your onconfig file to this dbspace. NOTHING WRITTEN TO A TEMPORARY DBSPACE IS LOGGED. This is because IT CAN ONLY BE USED FOR TEMPORARY TABLES. >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 would agree, possibly an "update statistics" is in order to enable usage of the composite index rather than creating a new one. Whilst it is true that There are less rows per page with a composite index adding a smallint to the indexed columns makes very little different on a unique index with 23 million rows. After all a smallint can at worst have 256 values >>> 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. > Same here. > >Mark Collins >mcollins@us.dhl.com > -- David Williams