{"id":49,"date":"2014-08-06T11:19:19","date_gmt":"2014-08-06T11:19:19","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=49"},"modified":"2016-05-10T11:54:40","modified_gmt":"2016-05-10T11:54:40","slug":"deferred-segement-creation-parallel","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=49","title":{"rendered":"Deferred Segement Creation PARALLEL"},"content":{"rendered":"

Following up on Chris Antognini’s findings regarding deferred segment creation (here<\/a> and here<\/a>) I noticed another restriction that still finds no mention even in the latest Oracle documentation<\/a>. I vaguely remember to have come across some issues in lower 11gR2 releases and some of them have been fixed in later patch sets. The problem went along the line of:<\/p>\n

\r\nalter session set deferred_segment_creation = true;\r\n\r\ncreate table t2 parallel 4\r\nas \r\n  select \/*+ parallel (t1, 4) *\/ *\r\n  from t1 where 1 = 2\r\n;\r\n\r\nselect segment_name, segment_type from user_segments where segment_name = 'T1';\r\nSEGMENT_NAME SEGMENT_TYPE\r\n------------ ------------\r\nT1           TABLE\r\n<\/pre>\n
Note: This test war run against Oracle 11.2.0.1 as this particular issue is fixed in at least 11.2.0.4 and later<\/p>\n
Now, why in gods name would you run CTAS in parallel when you know the query does not return any rows you might think.
\nWell, the problem becomes more subtle when we start combining features, namely partitioning and parallel DDL. Following I have prepared a few test cases to demonstrate the issue.<\/p>\n
First let’s create the source table to select from:<\/p>\n
\r\ncreate table t1\r\nas\r\n  select\r\n      rownum id\r\n    , round(dbms_random.value(1,4), 0) code\r\n  from dual connect by level <= 100\r\n;\r\n<\/pre>\n
The CODE column contains four distinct number values ranging from 1 to 4.<\/p>\n
Next, we create a list partitioned table by using CTAS (create table as select) from our source table. All parts of this SQL run serially:<\/p>\n
\r\ncreate table t3\r\nsegment creation deferred\r\npartition by list (code) (\r\n    partition code_1 values (1)\r\n  , partition code_2 values (2)\r\n  , partition code_3 values (3)\r\n  , partition code_4 values (4)\r\n)\r\nas\r\n  select *\r\n  from t1\r\n  where code = 1\r\n;\r\n\r\nselect segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T3' order by segment_name, partition_name;\r\nSEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME\r\n------------ ---------------   --------------\r\nT3           TABLE PARTITION   CODE_1\r\n<\/pre>\n
This works as expected. Only the relevant partition was materialized as a segment to hold the data for CODE value 1.<\/p>\n
This time, we create the same table structure using parallel DDL:<\/p>\n
\r\ncreate table t4\r\nsegment creation deferred\r\nparallel 4\r\npartition by list (code) (\r\n    partition code_1 values (1)\r\n  , partition code_2 values (2)\r\n  , partition code_3 values (3)\r\n  , partition code_4 values (4)\r\n)\r\nas\r\n  select *\r\n  from t1\r\n  where code = 1\r\n;\r\n\r\nselect segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T4' order by segment_name, partition_name;\r\nSEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME\r\n------------ ---------------   --------------\r\nT4           TABLE PARTITION   CODE_1\r\nT4           TABLE PARTITION   CODE_2\r\nT4           TABLE PARTITION   CODE_3\r\nT4           TABLE PARTITION   CODE_4\r\n<\/pre>\n
Oracle has materialized all four partitions even though only one of them contains data. The same also happens when the source query doesn’t return any rows, e.g. when CODE = 5.<\/p>\n
Usually, there’s not much sense in running DDL in parallel while the SELECT providing the data runs serially. I have left out parallel processing of the SELECT part for simplicity as my tests have shown that it doesn’t affect the segment creation in any way.<\/p>\n
Foot note: All test have been run on Oracle 12.1.0.2 and 11.2.0.4 except when where otherwise<\/p>\n","protected":false},"excerpt":{"rendered":"
Following up on Chris Antognini’s findings regarding deferred segment creation (here and here) I noticed another restriction that still finds no mention even in the latest Oracle documentation. I vaguely remember to have come across some issues in lower 11gR2 releases and some of them have been fixed in later patch sets. The problem went […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-49","post","type-post","status-publish","format-standard","hentry","category-internals"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/49","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=49"}],"version-history":[{"count":2,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/49\/revisions"}],"predecessor-version":[{"id":51,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/49\/revisions\/51"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=49"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=49"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=49"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}