SELECT takes 100ms; CREATE table as select - or - INSERT into select take 15mins

Question

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

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 query about 100 ms to execute and fetch < 2000 records.

If this query is nested in a CREATE TABLE AS or in a 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 on UDA_NAME field of both USER_DEF_ATTRIBUTES (alternate-key) and TT_SPLDR_55E63A28_59358 tables.

If I remove the WHERE NOT EXISTS it takes 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 the WHERE NOT EXISTS it runs in half a second.

I cannot explain why WHERE NOT EXISTS is so slow!

---

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

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

---

EXPLAIN for SELECT only with WHERE NOT EXISTS : (100ms)

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

It seems that when selecting it makes the same operations, but when creating the table it does different operations for WHERE NOT EXISTS and LEFT OUTER JOIN

Answer 1

Ok, I found it.

It's the alternate-key on UDA_NAME for table USER_DEF_ATTRIBUTES.

If I disable it and I create a UNIQUE INDEX on the same field, it runs in 500 millis.

Anyway, I'm not sure about the reason of this behavior.