Creating a materialized view filling up the complete temp space

Question

I want to create a materialized view(mv) please see below SQL query. When I try to create the materialized view my temp tablespace completely being (~128g) used and given below error SQL Error: ORA-12801: error signaled in parallel query server P007 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP1 12801. 00000 - "error signaled in parallel query server %s"

then I checked in the OEM it used parallelism 8 degree. So I disabled the parallelism using alter statement (ALTER SESSION DISABLE PARALLEL QUERY). Then the mv ran long and took several hours and got created. Please suggest is there any approaches to create it with out using much space temp space. The count for the select query for this MV is around 55 million rows. Any suggestions really appreciated.

DB: Oracle 11gR2

CREATE MATERIALIZED VIEW TEST NOLOGGING REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS select table4.num as "Number",table4.num as "SNum", table4.status as "S_status", 'Open' as "NLP", create_table2.fmonth as "SMN", table6.wgrp as "SOW", (table2.end_dt - create_table2.dt) as "elp", table6.d_c as "SDC", create_table2.fiscal_quarter_name as "SQN", 'TS' as "SSL", table3.table3_id as "SR Owner CEC ID", table4.sev as "ssev", SUBSTR(table8.stech,1,INSTR(table8.stech,'=>')-1) as "srtech", SUBSTR(table8.stech,INSTR(table8.stech,'=>')+2) as "srstech", table5.sr_type as "SR Type", table5.problem_code as "SR Problem Code", --null as "SR Entry Channel", --null as "SR Time in Status (Days)", table6.center, table6.th1col, table6.master_theater, table6.rol_3, table7.center hier_17_center, table7.rol_1 table7.rol_2, table7.rol_3 wg, table2.dt as "SBD", table2.wk_n as "SBFW", table2.fmonth as "SBFM", table3.defect_indicator as "Has Defect", table2.sofw, table2.sofm from A table1 join B table2 on (table1.date_id = table2.dw_date_key) join C table3 on (table1.date_id = table3.date_id and table1.incident_id = table3.incident_id) join D table4 on (table3.incident_id = table4.incident_id and table4.key_d <= table3.date_id and table3.table3_id = table4.current_owner_table3_id) join E table5 on table4.incident_id = table5.incident_id join B create_table2 on (table5.creation_dw_date_key = create_table2.dw_date_key) join F table6 on (table1.objectnumber=table6.DW_WORKGROUP_KEY) join G table7 on (table1.objectnumber=table7.DW_WORKGROUP_KEY) left outer JOIN H table8 ON (table8.natural_key= table5.UPDATED_COT_TECH_KEY) where table4.bl_incident_key in (select max(bl_incident_key) from D b where b.incident_id=table3.incident_id and b.key_d <= table3.date_id and b.current_owner_table3_id = table3.table3_id) and table2.fiscal_year_name in ('FY2013','FY2014')

Answer 1

Without knowing your system, tables or data i assume, that

• some of the 8 tables have many rows (>> 55 millons)
• join-predicates and filters will not reduce the amount of data significantly
• so nearly all data will be written to the mv

Probably the execution plan will use some hash-operations and/or sort-aggregations. This hashing and sorting cannot be done in memory, if hash and sort segments are too big. So this will be done in temp.

8 parallel slots will probably use more temp than 1 session. So this can be reason for the ora.

You can

• accept the several hours. normally such operations are done at night or weekend it doesn't matter, if it takes 4 or 1 hour.
• Increase temp
• Try to scale the degree of parallelism by a hint: create .... as select /*+ parallel(n) */ table4.num... Use 2 or 4 or 8 for n to have 2,4,8 slots

• Try some indexes for the joined columns, i.e. TABLE1(DATE_ID, INCIDENT_ID) TABLE1(OBJECTNUMBER)

  TABLE2(DW_DATE_KEY) TABLE2(FISCAL_YEAR_NAME)

  TABLE3(DATE_ID, INCIDENT_ID, TABLE3_ID) TABLE3(INCIDENT_ID, TABLE3_ID, DATE_ID)

  TABLE4(INCIDENT_ID, CURRENT_OWNER_TABLE3_ID, KEY_D, BL_INCIDENT_KEY)

  TABLE5(INCIDENT_ID) TABLE5(CREATION_DW_DATE_KEY) TABLE5(UPDATED_COT_TECH_KEY)

  TABLE6(DW_WORKGROUP_KEY)

  TABLE7(DW_WORKGROUP_KEY)

  TABLE8(NATURAL_KEY)

And use explain plan for the different sqls to see wich plan oracle will generate.