Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Daniel.

Asked: March 02, 2017 - 4:15 pm UTC

Last updated: March 07, 2017 - 11:44 pm UTC

Version: 12.2.0.2

Viewed 1000+ times

You Asked

I need to do a heavy operation on each row of a big table.

In order by paralelize this work I've created a table taking the rowid(C1) from the original table and adding a numeric field 'C2'. If C2=0 this row has not been proccessed and if 2C=1, the row has been proccessed.

desc lanza
Name Null? Type
C1 ROWID
C2 NUMBER

Each proccess:
lock the table
take n rows
update this rows c2=1
free the lock
take the original table rowid's
proccess this row
while there are rows with c=0

This is the code:

loop
lock table lanza in exclusive mode;
select rowid BULK COLLECT INTO filas1
from lanza
where c2=0 and
rownum <1000;
if filas1.last is null
then
commit;
exit;
end if;
update lanza set c2=1
where rowid in (select *
from TABLE ( cast(filas1 as filas)));
commit;
select c1 BULK COLLECT INTO filas2
from lanza
where rowid in (select *
from TABLE ( cast(filas1 as filas)));

Do some things...

end loop;

The problem is in the declaration on the type lista.
I can't declare it as:
SQL> create or replace type filas is table of rowid;
2 /

Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE FILAS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/24 PLS-00531: Unsupported type in a VARRAY or TABLE type: 'ROWID'.

If I declare this as:
create or replace type filas is table of varchar2(18);
/
the execution plan of the select is:


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID f5x5vts2ht6rr, child number 0
-------------------------------------
UPDATE LANZA SET C2=1 WHERE ROWID IN (SELECT * FROM TABLE ( CAST(:B1 AS
FILAS)))

Plan hash value: 4255393934

-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | | |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| 1 | UPDATE | LANZA | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 650M| 32M| 752M (0)|
| 3 | TABLE ACCESS FULL | LANZA | 1 | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 999 | | | |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(ROWID=CHARTOROWID(VALUE(KOKBF$)))


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level



How should I do it in order to avoid the full scan?

Regards.

and Chris said...

Rather than trying to build your own parallel solution, look at dbms_parallel_execute.

This came in 11.2. You can use it to split a table up into chunks by rowid, number columns or a SQL query.

You can find great worked examples at:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542
https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2

Rating

  (2 ratings)

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

Comments

I can't use it.

Daniel Ramos, March 07, 2017 - 9:12 am UTC

I has given you incomplete information, I apologize.

I have standard license. I can't use parallel features.

I'm looking for a solution compatible with standard license.

Sorry to make you lost time.

Regards.
Chris Saxon
March 07, 2017 - 3:10 pm UTC

Dbms_parallel_execute is available in Standard Edition!

Alex, March 07, 2017 - 4:49 pm UTC

I remember back in older releases Tom mentioned to use parallelism with caution, because it is basically telling Oracle to use as much of the server's resources as it feels like to accomplish the task. So in theory one session could bring everything to it's knees. Is this still true on 12c?
Connor McDonald
March 07, 2017 - 11:44 pm UTC

Every serial query tells the database - you can use my session to work the server as hard as you can to get the result. But its only one process, so its pretty much going to consume 1 core at most.

parallel 'n' is telling the database "you can use up to 2n processes to work as hard as they can to get the result". So this could consume up to 2n cores on your server. It's not a great deal different to that many individual processes running flat out - but of course, it would be unwise to set no limits on what 'n' could be.

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