Re: Problem with SQL

John Baker Depot Sys Mgt Br writes: > Quoting David A. Snyder, Folcroft, PA, in reply to my original message > about SQL PERFORM that is storing records in the .dat file in reverse order: [ It's not perform that's storing the rows, mind, it's the backend process ] > > When you select data from your table, do an "ORDER BY ROWID". That will > > work until you start removing old records and inserting new ones. The > > slots where the old records have been deleted will be filled before > > expanding the size of the table. > [ ... ] it won't solve our problem. On a new table it is not necessary > to order by the Rowid, becuse the rows are stored properly. On a table which > has had some rows deleted, ordering by the Rowid makes no difference. This is just what DAS said, after all. > The > records are still stored in the reverse order in which they are added. I > tried this with the ACE report since I know of no way to make the Query option > of PERFORM sort by rowids. Perform will sort by rowid anyway, if it's doing a sequential read. If it's doing an indexed read (use "set explain on" and run an sql query) you may get something completely different, since it's ordered by index access. You're saying that fresh data has being added in slots where data was deleted, so rowid is no longer related to time added. There's then no point in sorting on rowid in that case. > If I do a descending sort, I can get the records > printed out properly, but only after some records have been added and deleted. > This is *not* the solution to our problem, since it does not occur with any of > our other 2.10 applications. > > Again, this problem is occurring with just one application. I experimented > on our others (all which have deleted rows) and none of them place newly-added > records at the *front* of the file. In other words, if you use PERFORM and > add a bunch of records to your .dat file, and then delete some and then add > some new ones in, those newly added records will appear at the *end* of your > Current List even after you do another Query. I didn't believe PERFORM imposed any sort order - you don't even have any way of influencing it, unless you can get it to read a cluster index ? You could run a cron job to cluster the table on an index, if your deletes don't occur much during the day (leaving free rows to be filled). After that, extra rows would have to be added at the end. Is it possible to cluster index on a serial column ? You presumably have to add another field, since a unique index is already built on the serial column, then use the composite as the cluster index. If you add a serial field to all the rows (should be invisible for the applications, as long as you haven't got "select * from ..." in the code) is it possible to cluster on the serial column (holds breath , waits for authoritative answer). In that case you could run an "alter table to cluster on ..." on the serial column, and after the clustering, the rows would always be in time-order. Otherwise, perhaps copy the table to a new table (forcing a seq. read), drop the original and rename the new to the original. That should compact the .dat. > Can anyone shed any light on this problem????? I'm not sure this counts ... :-) Hope it helps a little, please let me/us know the results. > John Baker > USAISC - Lex Phone: (606) 293-3644 or 293-3743 > Lexington - Blue Grass Army Depot DSN: 745-3644 or 745-3743 > Lexington, KY 40511-5109 E-mail: jbaker@lexington-emh2.army.mil Cheers - Tony. _______________________________________________________________________________ Tony Heskett th@bnr.co.uk |Voice: (+44) 279 429531 x 2637 BNR, London Road, Harlow, Essex, CM17 9NA |Fax: (+44) 279 454187