SELECT takes 100 ms; CREATE table as select - or - INSERT in select take 15 minutes

Question

I have a very simple SELECT * query with a WHERE NOT EXISTS .

 SELECT * FROM "BMAN_TP3"."TT_SPLDR_55E63A28_59358" SELECT_TABLE WHERE NOT EXISTS (SELECT * FROM "BMAN_TP3"."USER_DEF_ATTRIBUTES" EXISTS_TABLE WHERE "SELECT_TABLE"."UDA_NAME" = "EXISTS_TABLE"."UDA_NAME") 

This request is about 100 ms to execute and fetch <2000.

If this query is embedded in CREATE TABLE AS or INSERT INTO , it runs in 15 minutes .

 CREATE TABLE BMAN_TP3.TT_UDA_TEST TABLESPACE BMAN_TP3_U AS ( SELECT * FROM "BMAN_TP3"."TT_SPLDR_55E63A28_59358" SELECT_TABLE WHERE NOT EXISTS (SELECT * FROM "BMAN_TP3"."USER_DEF_ATTRIBUTES" EXISTS_TABLE WHERE "SELECT_TABLE"."UDA_NAME" = "EXISTS_TABLE"."UDA_NAME") ) 

I have a UNIQUE INDEX in the UDA_NAME field of both the USER_DEF_ATTRIBUTES tables (alternate key) and TT_SPLDR_55E63A28_59358 .

If I delete WHERE NOT EXISTS , it will take half a second.

---

EDIT:

If i use

 LEFT OUTER JOIN "BMAN_TP3"."USER_DEF_ATTRIBUTES" ON "SELECT_TABLE"."UDA_NAME" = "USER_DEF_ATTRIBUTES"."UDA_NAME" WHERE "USER_DEF_ATTRIBUTES"."UDA_NAME" IS NULL 

instead of WHERE NOT EXISTS is executed in half a second.

I can’t explain why WHERE NOT EXISTS so slow!

---

EXPLAIN for CREATE TABLE AS with WHERE NOT EXISTS: (15 minutes)

EXPLAIN for CREATE TABLE AS with LEFT OUTER JOIN: (500 ms)

---

EXPLAIN for SELECT only with WHERE DOESN'T EXIST: (100 ms)

EXPLAIN for SELECT only with LEFT OUTER JOIN: (100 ms)

It seems that when you select it, it performs the same operations, but when you create the table, it performs different operations for WHERE NOT EXISTS and LEFT OUTER JOIN

Answer 1

Ok, I found it.

This is an alternate key for UDA_NAME for table USER_DEF_ATTRIBUTES .

If I turn it off and create a UNIQUE INDEX in the same field, it will work for 500 milliseconds.

In any case, I'm not sure about the reason for this behavior.