RE: SELECT SKIP

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 15 Feb 2001 16:09:48 -0800
Message-ID: <F001.002B60F4.20010215155529@fatcity.com>

Hi Jack,

If it works as expected, option 1 is no doubt the neatest. However, remember that this is an undocumented feature, so play with it by all means, but don't build it into anything important. If you were to hit problems (like your ORA-3113 error) once it was in production, you would have a difficult time getting Oracle support to listen to you!

Let us know if you find anything else interesting. Remember don't rely on the default database order in which records are returned.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
Sent: Friday, 16 February 2001 9:37
To: Steve Adams; ORACLE-L_at_fatcity.com

Steve,

Yes, it does work, but not on our Documents table, which is partitioned! After getting your reply I tried it again on another table (Documents is our main table and I just tend to have Documents on the brain - that's why I picked it originally) and it worked on the other, non-partitioned table!

Now, I'm assuming that it's because Documents is partitioned that I get the ORA-03113, but it also has a CLOB column with an Intermedia index on it. Also, the Document_ID column is the PK for Documents, but it is a Global Index, not Local. So I wonder what it is exactly about Documents that causes the ORA-03113?

Also, I noticed that the locked rows that are skipped by the 2nd session are counted when satisfying the RowNum predicate. That is, to get two rows returned from the 1st session's query, I specify RowNum < 3. However, to get two unlocked rows in the 2nd session's query, I must specify RowNum < 5, otherwise I get "no rows selected".

That kind of defeats its usefulness if I want to use the same query in several sessions to return just two unlocked rows in each - I'd have to know how many other sessions had issued the statement to specify the right RowNum figure. Any ideas on how to work around that?

The only things I can think of are:

Putting the Select in a Cursor and Looping through it until I get the number of unlocked rows I want. This is probably the best, but I need to test it.
Using a Sequence, from which each session would get NextVal, thus providing a count for each succeeding session to set its RowNum. Coordinating the fetching of NextVal from the sequence by several, perhaps, parallel sessions, not to mention creation and dropping of the sequence by the first and last sessions, could get tricky, though.
Inserting, updating (incrementing), then deleting a row in a "scratch" table. An 8i Temporary Table wouldn't work since the rows for each session wouldn't be visible to the other sessions.

Fun stuff to explore!

Thanks.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com

-----Original Message-----

Hi Jack,

After having created a suitable table, a cut and paste of that SQL returns "no
rows selected" running 8.1.6 on NT. No error message. I've used it under 8.1.6
on Solaris before, and it has worked there too.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Feb 15 2001 - 18:09:48 CST