Basically, it does two steps.

1. Divides the table data into small chunks.
2. Apply DML change on each chunk parallely.

This method improves the performance the large DML operations, without consuming too much of rollback segment.

Lets check the below example.

TABLE_OWNER   -> DBACLASS
TABLE_NAME    ->  TEST100
DML STATEMENT -> Update dbaclass.test100 set OBJECT_ID=10 where NAMESPACE=1;

1. Create one task:

 SQL> execute DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME=>'query_test100_update');

PL/SQL procedure successfully completed.

2. CHUNK the table by row_id:

BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
                TASK_NAME=> 'query_test100_update',
                TABLE_OWNER =>'DBACLASS',
                TABLE_NAME => 'TEST100',
                BY_ROW=> TRUE,
                CHUNK_SIZE => 10000
  );
END;
/

3. Run the DML update procedure(RUN_TASK)

SET SERVEROUTPUT ON
DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN 
 -- DML to be execute in parallel
  l_sql_stmt := 'Update dbaclass.test100 set OBJECT_ID=10 where NAMESPACE=1 and rowid BETWEEN :start_id AND :end_id';

  -- Run the task
  DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
    TASK_NAME      => 'query_test100_update',
    SQL_STMT       => l_sql_stmt,
    LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
    PARALLEL_LEVEL => 10
  );  
END;
/

 
PL/SQL procedure successfully completed.

Elapsed: 00:00:21.07

DML completed successfully and it took 21 seconds only.

The post DBMS_PARALLEL_EXECUTE in oracle PL/SQL appeared first on DBACLASS.

SQL> variable text char
SQL> exec :text:='X'

PL/SQL procedure successfully completed.

SQL> select * from DUAL where DUMMY=:text;

D
-
X

Now in 12.2 is so simple

SQL> variable text char='X'
SQL> select * from DUAL where DUMMY=:text;

D
-
X

The post VARIABLE new feature in Oracle 12.2 appeared first on DBACLASS.