tag:blogger.com,1999:blog-72429151172445769322011-04-22T02:48:19.651+07:00a kind of indonesian foodUjanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.comBlogger11100tag:blogger.com,1999:blog-7242915117244576932.post-37022510264622969172007-02-19T14:07:00.000+07:002007-02-27T20:25:07.905+07:00Parallel Query<br /><br />1. Introduced since Oracle 7.1 as Oracle Parallel Query Option (PQO) Parallel operations include:<br />* SELECT, FTS or index range scan spanning multiple partitions<br />* Creating or rebuilding an index<br />* Partition operations such as moving or splitting partitions<br />* CREATE TABLE AS SELECT <br />* INSERT INTO . . . SELECT<br />* Update and delete operations on partitioned tables<br /><br />2. To parallelize a SELECT statement, the following conditions must be met:<br />* FTS or Index range scan involving multiple partitions.<br />* PARALLEL hint in FTS or PARALLEL on table definition.<br />* On an index PARALLEL_INDEX hint or index must have a parallel definition.<br /><br />Example<br />alter table emp parallel (degree 4);<br />select degree from user_tables where table_name = 'EMP';<br />select count(*) from emp;<br />alter table emp noparallel;<br />SELECT /*+ PARALLEL(emp,4) */ COUNT(*) FROM emp;<br /><br />3. Parallel DML<br />* Only when multiple partitions are involved.<br />* Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.<br /><br />Examples<br />alter session enable parallel dml;<br />insert /*+ parallel (emp_big,4,1) */ into emp_big select * from emp;<br />commit;<br />alter session disable parallel dml;<br /><br />4. Parallel DDL<br />Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned.<br /><br />CREATE TABLE...AS SELECT<br />CREATE INDEX<br />ALTER INDEX...REBUILD<br /><br />Not all tables allow these operations to be executed in parallel.<br />Tables with object columns or LOB columns don't allow parallel DDL.<br /><br />5. Parallel Data Loading::<br />SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE<br />SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE<br /><br />6. Parallel Recovery<br />The RECOVERY_PARALLELISM initialization parameter controls<br />the degree of parallelism to use for a recovery.<br />You can override that setting for a specific situation<br />by using the RECOVER command's PARALLEL clause.<br /><br />Example<br />RECOVER TABLESPACE tab PARALLEL (DEGREE 4);<br />RECOVER DATABASE PARALLEL (DEGREE DEFAULT);<br /><br />A value of or 1 indicates serial recovery, no parallelism will be used.<br />The RECOVERY_PARALLELISM parameter setting cannot exceed<br />the PARALLEL_MAX_SERVERS setting.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/7242915117244576932-3702251026462296917?l=ujang-en.blogspot.com' alt='' /></div>Ujanghttp://www.blogger.com/profile/04962906686661927128noreply@blogger.com0