Home sql How to use the PARALLEL to fulfill the request?

How to use the PARALLEL to fulfill the request?




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
    level, 'memo for' || level,
    case when mod (level, 5) = 0 THEN 10 when mod (level, 3) = 0 then 20 else 30 end,
from dual connect by level & lt; = 1e5;
exec dbms_stats.gather_table_stats (user, 'tab', cascade = & gt; true);
---------- ----------
    10 20000
    20 26667
    30 53333

So the task is created and defined chain execution:

  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);

zaapuska example to update the table:

  stmt varchar2 (32767);
  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

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

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

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions