Re: Large table query with cpu throttling

Part of the following are a resent (2nd time), as I found the freelist website a lot of garbage (unicode problem?). Not sure why.

Thank you very much Patrick and ukja.dion.

2008/2/19 ukja.dion_at_gmail.com:
> But what's the merit of the partition when you should extract all the
> records(as Op said) and don't need to delete some region of data
> periodically?
> I can't infer any necessity of partition from OP's request.
>

Another reason is I am not the owner of the database :-), it was legacy, and many applications have been running on top of it without any issue, so I am afraid modifying its design is unlikely.

> My recommendation for CPU throttling is
> (pseudo code)
>
> loop
> bulk collect from your_table to arrary;
> exit when no rows found exit loop;
> do something with your fetched array;
> sleep for a short time; (dbms_lock.sleep(0.1));
> end loop;
>
>
>
>

Good solution, as my original was a hodgepodge of scripts + pl/sql. now this is entirely pl/sql - thank you very much.  (unless more complex stuff need to be done with the fetched array - then C may come in).

But after some thoughts, further to my comment above:

Soln 2 won't work: It is impossible to automatically generate the entire universe of the rowid values - the space of possibilities is just too large.

Soln 3 won't work either: "SELECT rowid" returned a result that is not always the same as "SELECT rowid order by rowid" and so it is impossible to do batching of first 100 rows, then next 100 rows and so on (by using "SELECT rowid" without the "order by"). The solution will work if I can do a "order by rowid", but this is impossible, as there are transactions that regularly update the table, and to construct a "order by rowid" seemed impossible (assuming every minute I extract only 100 rows, so every minute there is a need to reorder the table by rowid - of 1 billion rows?)

So ALL my previous proposed solution seemed not working - at least in the head so far. Any suggestions?

--
http://www.freelists.org/webpage/oracle-l