I did some googling and couldn't find a clear answer to an oracle performance question. Maybe we can document it here. I am building an MV that is pretty simple but on fairly large tables. The query like many things can be written more than one way. In my case when written as a select statement two solutions have similar costs / execution plan, but when placed inside of a create materialized view the execution time changes drastically. Any insight into why?

• Tab1 is aprox 40M records.
• Tab2 is aprox 8M records.
• field1 is a primary key on Tab1, it is not a PK or unique on Tab2 but tab 2 does have an index on this field.
• field2 is not a key nor is it indexed on either table (boo)

Queries are:

Q1:

SELECT
    CR1.Several_Fields
FROM 
    SCHEMA1.tab1 T1
WHERE T1.field2 like 'EXAMPLE%'
AND T1.field1 not in (
    SELECT T2.field1
    FROM SCHEMA1.tab2 T2
)
; 

Q2:

SELECT
    CR1.Several_Fields
FROM 
    SCHEMA1.tab1 T1
WHERE T1.field2 like 'EXAMPLE%'
AND  not exists (
    SELECT 1
    FROM SCHEMA1.tab2 T2
    WHERE T1.field1 = T2.field1
)
;

The two queries as select statements run similarly in time, and explain plan has them both utilizing the index scan rather than full table scans as I would expect. What is unexpected is that Q2 runs vastly faster (47 seconds vs 81 days per v$session_longops) when run in an mv creation like:

CREATE MATERIALIZED VIEW SCHEMA1.mv_blah as
(
  Q1 or Q2
);

Does anyone have any insight, is there a rule here to not use IN if possible for mviews only? I know of the tricks between in and exist when indexes do not exist between the tables but this one had me baffled. This is running against an oracle 11g database.