Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Reader.

Asked: November 01, 2016 - 11:26 am UTC

Last updated: November 10, 2016 - 5:32 pm UTC

Version: 10.2.0.1

Viewed 1000+ times

You Asked

Hi! Team,
Is there a way whereby we can get all rowids of a table without querying the table but some dba views or other stuffs? Actually we have a procedure which calculates cost of goods based on FIFO method. We have a table where all inventory related entries are get recorded. There can be more than 100 million records for a given financial year. We scan this table for a particular financial year and calculate cost. This takes huge time to complete the process. My idea is get rowids of this table and make smaller sets of data out of it and then submit jobs to process these sets. If a CPU is having 10 cores then we would submit 8 jobs assuming each job would be serviced by a particular core so that process can be finished much faster. Is it a good idea? Would it be the case that each job would be taken up by a different core?
Any other suggestion will be appreciated.
Thanks and regards.

and Connor said...

We've done the work for you

Check out DBMS_PARALLEL_EXECUTE

https://docs.oracle.com/database/121/ARPLS/d_parallel_ex.htm#ARPLS233

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reader, November 10, 2016 - 1:33 pm UTC

Thanks for the prompt response. But we are in 10.2.0.1 version. If I issue a query like : select rowid from big_table would it query some dictionary internally instead of the table itself?
Regards
Chris Saxon
November 10, 2016 - 5:32 pm UTC

You can calculate chunks yourself using dba_extents.

Bryn Llewellyn discusses how to do this in his "Transforming one table to another: SQL or PL/SQL?" whitepaper and talk:

https://blogs.oracle.com/plsql-and-ebr/entry/transforming_one_table_to_another

Thanks for the information

A reader, November 12, 2016 - 8:20 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library