ORA-600 [qkaIsLoadEquiPartitioned_1] With CTAS Nested Table and /*+MATERIALIZE*/ Hint
(Doc ID 2033857.1)
Last updated on FEBRUARY 03, 2019
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [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 ...
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document