How can I optimize the query, if for example, in a huge number of records the table?

I do not understand how to use the PARALLEL ? What is she doing?

Answer 1, Authority 100%

Perhaps you mean prompt PARALLEL :

SELECT / * + PARALLEL (table flows integer) * / & lt; query & gt;

This prompt indicates the optimizer to use multiple processes for simultaneous operation. IMHO, it makes sense to use at high partitsionirovannoy fact table, with multiple directories.

Starting with version 11.2, you can specify instead of a table, view, or even a sub-query.

Answer 2, Authority 200%

From the prompt PARALLEL SQL interpreter fully controls the process parallelization and executes it at the query level (statement-level ). To eat, when an error occurs in the general case, the entire query fails and back all the changes.

In some cases, it would be appropriate to take control parallelization (application-level ), then it will be possible, for example, when an error occurs in a single chain to repeat its performance.

To do this, there is a package DBMS_PARALLEL_EXECUTE . you can get an idea of ​​how it works and what opportunities it would give to a minimum example.

The test data:

create table tab (id number primary key, memo varchar2 (96), flag number, sesid number);
insert / * + append * / into tab
  select
    level, 'memo for' || level,
    case when mod (level, 5) = 0 THEN 10 when mod (level, 3) = 0 then 20 else 30 end,
    null
from dual connect by level & lt; = 1e5;
exec dbms_stats.gather_table_stats (user, 'tab', cascade = & gt; true);
   FLAG COUNT
---------- ----------
    10 20000
    20 26667
    30 53333

So the task is created and defined chain execution:

begin
  dbms_parallel_execute.create_task (task_name = & gt; 'pe');
  dbms_parallel_execute.create_chunks_by_rowid (
    task_name = & gt; 'pe', table_owner = & gt; user, table_name = & gt; 'TAB', by_row = & gt; true, chunk_size = & gt; 10000);
end;
/

zaapuska example to update the table:

declare
  stmt varchar2 (32767);
Begin.
  stmt: = q '[
    update tab t
      set flag = flag + 10
      sesid = sys_context ( 'userenv', 'sessionid')
    where rowid between: start_id and: end_id] ';
  dbms_parallel_execute.run_task (
    task_name = & gt; 'pe', sql_stmt = & gt; stmt, language_flag = & gt; dbms_sql.native, parallel_level = & gt; 10
  );
end;
/

As a result, the update was carried out in parallel in ten sessions:

select sesid, count (1) cntses
from tab
group by sesid;
   sESID CNTSES
---------- ----------
  758 636 12384
  758 630 12126
  [...]
  758 640 12900
10 rows selected.