CODEWITHSUNDEEP
sqloracle-databaseperformancematerialized-views
Greg
Greg

Reputation: 65

Materialized View Performance of Exists vs In

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?

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.

Upvotes: 5

Views: 341

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

This looks like a known bug. If you have access to My Oracle Support look at Slow Create/Refresh of Materialized View Based on NOT IN Definition Query (Doc ID 1591851.1), or less usefully if you don't, a summary of the problem is available.

The contents of the MOS version can't be reproduced here of course, but suffice to say that the only workaround is what you're already doing with not exists. It's fixed in 12c, which doesn't help you much.

Upvotes: 3

Related Questions