Re: Reversal of Records

> to get the desired results. I thought I read somewhere that a Serial > column cannot have a Dups index (after all, it would not be possible to > have duplicates on a Serial column). Is this now a restriction in version > 4.00? Nevertheless, our version 2.10 lets me declare a Dups index on the > Serial column. The idea of a SERIAL field is to basically "serialize" records as they are entered, like invoice numbers, etc. In a "pure" relational model, each row in a table should be "unique", SERIAL fields are one way of making sure that all of your records are unique. Since they are intended to be unique, putting a UNIQUE index on the field makes complete sense. That's the way the field is created, and the index is created. The index is a generic, everyday index. You can drop/change/recreate this index whenever you want. The SERIAL field will still increment properly, and doesn't require the index to operate. However, by changing the or removing the index you change it's default behavior. For some applications, a DUP index on the SERIAL field makes sense. You get the general default behavior, but maybe your application needs to have two or more records with the same SERIAL number. No problem. > I assume the limitation in using the index occurs because all empty slots > in the .dat file will be used before the file is increased in size. Here's > the results: > I. Added four records: 1,2,3,4 > II. Removed record 2, then added record 5. > III. Query without using the index gave: 1,5,3,4 > Query using the index (specified >0 on the serial col): 1,3,4,5 > IV. Now added back record 2. > Query without using the index gave: 1,5,3,4,2 > Query with index: 1,3,4,5,2 > QUESTIONS: > Why didn't the index retrieve the records in Step IV above so that > record 2 would come after record 1? After all, the index was able > to put record 5 after 4 (and not after 1 where it is physically > located). What you've demonstrated is EXACTLY the way a SERIAL field behaves. You'll notice that the records are in the order of ENTRY, not the order of the data (assuming the your 1,2,3,4 is not your serial field). If you look at the SERIAL field data on those records, they probably read something like: Record Serial 1 1 3 3 4 4 5 5 2 6 < -- Note this Using the index on the SERIAL field puts them in the ORDER of the SERIAL field (in this case 1,3,4,5,6) rather than your data (1,2,3,4,5). > Why does Perform retrieve the rows in a different order when it is > using an idex? I thought indexes were merely supposed to speed the > search process and not affect the order in which rows are read. INFORMIX uses a B-Tree style for it's indexes (almost everybody does). Basically, indexes are used to provide different access paths to your data. Your data is stored on the disk in, as we've seen, basically an arbitrary order. It is clear that depending on when records are added, and when they're deleted affect where a record is physically stored on the disk. When the Engine goes to look for any data you select, it must scan through the each record to find the ones that match your criteria. When it finds a matching record, it stores the ROWID off somehwere and keeps looking. When it has searched everything, it goes back to it's list of ROWIDs and starts reading the records, which it then feeds back to you the user. On large tables, this is obviously quite wasteful, particularly when you are looking for just one record. Indexes help alleviate the need for these wasteful searches. The way an index works, is by taking the "key", the fields in the index, and sorting them. It sorts them and puts them into a data structure known as a B-Tree. When you go to look up a name in a phone book, for example, a general case might open the phone book in the middle and see the name at the top of the page. If the name you're looking for is after the name on the page, you may then go to the middle of the back of the book and look again. You keep dividing the rest of the book in two until you find the page you need, and then look up the number. This is known as a binary search (you keep dividing by two), and even with the largest number of records, you can find your page in just a few thumbs. This is basically how a B-Tree is structured. But another thing about how the B-Trees in INFORMIX is designed is that not only can you find the record you wish quickly, but each index points to the next record in the list. If you wished to "count all of the JONES" in the phone book, you'd first find the first JONES, then start counting until you run out, because all of the JONES are kept together by the sort. INFORMIX works this way too. So, indexes not only allow you to find records quickly, they allow you to use the sort of the index in your reporting. This is much more efficient than sorting on the fly because it's only done once and then maintained one record at a time rather than EACH time you ask for it with lots of records. When you put ">0" in the SERIAL field, the Engine noticed that it had a convenient index that happened to be in the same order. Now it has two options. It could either scan through the records in Physical ROWID order, looking for each record with a SERIAL field >0 OR it could go straight to the 0's using the index and follow the index along. At this point it will decide to use the index because it feels that it is more efficient. The database doesn't KNOW (like we do) that NONE of SERIAL fields are 0, it just assumes that the index is more efficient. One thing you might try is to use "!=0" instead of ">0". I'll bet that you discover the query returns the data in the "physical" order, rather than the index order. Just about anytime you use "!=" in a query, the Engine will generally NOT use an index as it would basically be more efficient to scan the records than use the index. > other stuff to Tony deleted > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > 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 > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= It's kinda long, but I hope that helps clear things ups. Will (uunet!la4gen!villy)