Re: A wee buglette?

In article <183.365.uupcb@digitec.co.za>, billy.wheeler@digitec.co.za (Billy Wheeler) says: > >I then ran it through the debugger and saw something I consider to be >pretty amazing: the cursor appears to be picking up the generated >entries and processing them through the cursor and then picking up >these generated entries and ... > >Now I don't mind if someone out there is going to tell me I'm a total >ponce (I hope that doesn't break netiquette rules - if it does, I >apologise), but I was always under the impression that a cursor built >it's set up on the basis of the OPEN. Is it possibly because this is >a key-only operation? > A common misconception. The entries in a cursor are NOT determined until you work your way through the cursor. If you are inserting entries as you go along, the new entries are *USUALLY* but not always in the cursor later, as you come to that point in the table. I know this is true, because we have tables with multi-million rows. Building a cursor that spanned the entire table, or most of the table would take enormous amounts of (DISK or MEMORY) and (TIME). If a table scan is indicated, the cursor conditions are evaluated when each row is fetched. If your table has vacant rows (from deletions) your new row may be inserted before the current scan point. If not, then the row is inserted after the current scan point, and would be presented later in the cursor. >I worked around it, but it has shaken my faith in my understanding of >how cursors work. > As well as my faith in SQL as an intuitive and dependable language. >The front-end is RDS 4.11UC1 and the the engine is 5.01UD3 OnLine >running on HP/UX 9.04 on an HP9000 G40. > This is front-end, platform and probably database (Informix, Oracle) independent. >Any comments will be gratefully examined. > Setting isolation to cursor stability might make this go away, but I'm not sure. Jim Burks jburks@promus.com Database Administrator The Promus Companies Memphis, TN USA