Last updated on NOVEMBER 28, 2016
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2 to 12.1]
Information in this document applies to any platform.
CTAS for a nested table fails with next error as reported in the alert log:
The incident detail trace file shows a current sql like:
create table stage_trkd.st_company_periods_idx3
nested table CPER_PERIOD_SET
store as N_ST_COMPANY_PERIODS_IDX3 ( tablespace TS_SNAPSHOT)
tablespace TS_SNAPSHOT as
select * from stage_trkd.vst_company_periods_idx3
The nested table is based on a view with a 'with' clause using a /*+materialize*/ hint for performance reasons.
The /*+materialize*/ hint normally improves performance because a subquery defined by a 'with' clause is materialized into a Global Temporary Table (GTT).
Removing the /*+materialize*/ hints, the CTAS succeeds but is is too slow for practical use, e.g. takes about 4 hours for nearly 800000 rows.
The call stack from the tarce file shows functions like:
... qkaIsLoadEquiPartitioned qkaidlSetup qks3tAllocSIDL qks3tAllocIDL qks3tAllocIDL opitca kksLoadChild kxsGetRuntimeLock kksfbc kkspsc0 kksParseCursor opiosq0 kpooprx kpoal8 opiodr ...
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms