Hi,
I'm moving a non partitioned big table (460 million rows) to a partitioned IOT.
Why creating an IOT (partitioned specifying every tablespace) as select, needs space on user default temp tablespace?
The plan is:
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 19M(100)| | | | |
| 1 | LOAD AS SELECT | | | | | | 256K| 256K| |
| 2 | TABLE ACCESS FULL | TB_XXXXXX | 462M| 35G| 1046K (2)| 04:04:13 | | | |
----------------------------------------------------------------------------------------------------------------
Shouldn't there be a SORT in the plan?
In v$SQL_WORKAREA_ACTIVE:
select * from v$SQL_WORKAREA_ACTIVE;
OPERATION_TYPE OPERATION_ID POLICY SID WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE TABLESPACE
-------------------- ------------ ------ ---- -------------- ------------- --------------- ------------ ------------- ------------------ -------------
SORT (v2) 2 AUTO 365 108134400 108134400 108188672 262159360 1 48657072128 <default user tmp>
I guess there is no way to avoid this sort using the CTAS or to do it in the destination tablespace, am I right? I could use create + insert as select, but that way, tons of undo space is required.
Any better strategy?