Re: Reversal of Records

John Baker Depot Sys Mgt Br writes: > Hello again. ^ [ yet ] :-) > How do I get the standard engine to use an ORDER BY clause > (on a serial field) when it goes out to retrieve records > during a "Query" done while running an INFORMIX-SQL > PERFORM screen? You don't, sorry. > Tell me if I am right or wrong on this point: In SQL (running PERFORM), the > records will be retrieved in the order that C-ISAM has stored them whether > or not > a) you have a serial column in your table, > b) you have declared an index on any column, > or c) you have declared a clustered index on any column. The idea of a serial column is simply to get a time handle on the records. The idea of a clustered index is to force the table to follow the index. The table will read off in index order AS LONG AS IT REMAINS CLUSTERED, i.e. after you've done the deletes but NOT after you've done the deletes and inserts. At that point you have to force a re-cluster. > In other words, you can't change the way the records will be read when running > PERFORM screens (i.e. doing queries). True or false??? True, it's probably going on physical order (i.e., like you more(1)'d the .dat). Perform certainly doesn't care about the serial column. > Quoting Greg Bryan, DHL WORLDWIDE EXPRESS (gbryan@ssf-sys.dhl.com): > > The reason for this is the reason that SERIAL exists - it provides > > an automatically generated primary key. The only implied > > requirements are that a type SERIAL be UNIQUE and NOT NULL. > > In addition, a quick review of several Informix 4.0 manuals > > indicates that there is no promise made there about ordering > > of type SERIAL with respect to INSERT sequence. Frankly, I wouldn't worry. It's dead easy (i.e. low CPU, disk) to +1 a number and get the next unique number. It's expensive to look for gaps in the sequence. If you're hitting 2^32, double the bits, it's still cheaper (Informix support like to join in here ?). > [ ... complaints about how Informix adds records, using old > space ... ] OK. scenario: I have a database to which I add 50000 rows per day. Every day I delete 50000 rows. Each row uses 1KByte. I wish my rows to be retrieved by Perform in the order in which they were added. Therefore, I buy a copy of the engine source ($$$$$$$) and hack it so that new records are only added at the end of the table. My retrievals are now in physical order = time order. Fortunately my disk usage only increases by 50Mbyte/day, and I consider this a small price to pay for the added convenience. Final answer: Sorry, wrong tool. The ace reports will work fine. Perform will only do what you want if you can force a cluster before a query (don't sound too multi-user, does it). Apologies if I/we misled you. Give them 4GL screens or do it some way that time order isn't important any more. Dead horse ? 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