Oracle Parallel Query

Parallel query of Oracle Parallel query allows an SQL SELECT statement to be divided into several smaller queries. the query of each part runs concurrently and then the results of each part are combined, provide the final answer. Www.2cto.com let's look at an example: 1. execute the following SQL statement to create a table named big_table, during which you will be asked to enter the value of 1 (How many rows of data will be inserted into the big_table table ), the number of rows I entered is 10 million. SQL code create table big_table as select rownum id,. * from all_objects a where 1 = 0/alter table big_table nologging; declare l_cnt number; l_rows number: = & 1; begin insert/* + append */into big_table select rownum,. * from all_objects a; l_cnt: = SQL % rowcount; commit; while (l_cnt <l_rows) loop insert/* + APPEND */into big_table select rownum + l_cnt, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT _ ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY from big_table where rownum <= l_rows-l_cnt; l_cnt: = l_cnt + SQL % rowcount; commit; end loop; end;/alter table big_table add constraint big_table_pk primary key (id)/begin dbms_stats.gather_table_stats (ownname => user, tabname => 'Big _ table ', method_opt => 'for all indexed columns', ca Scade => TRUE); end;/select count (*) from big_table; 2. query the non-parallel execution state. the time consumed by select count (status) from big_table. SQL code explain plan for select count (status) from big_table; select * from table (dbms_xplan.display); shows the execution result on my computer. the time consumed is 6 minutes and a half. 3. modify the table to allow parallel execution, re-execute the resolution plan www.2cto.com SQL code alter table big_table parallel; explain plan for select count (status) from big_table; select * from table (dbms_xplan.display ); shows the execution result on my computer. It takes 1 minute and 40 minutes. If you read the plan from the bottom up (starting from ID = 6), it shows the specific steps of parallel execution. Full table scan is divided into several smaller scans (step 1 ). Each scan summarizes its COUNT (STATUS) value (step 1 ). These subresults will be transmitted to the parallel query Coordinator (step 1 and step 2), which will further summarize these results (step 2) and output the answer.