Skip to Main Content
  • Questions

  • Complete MV refresh not using Smart Scans in 12c

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Guru.

Asked: November 28, 2016 - 6:00 pm UTC

Last updated: November 29, 2016 - 2:21 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

I have a query which i am trying to load the data using 2 options:

1. Materialized View Refresh with Non-Atomic Refresh
2. Insert into Select

Materialized View Refresh: When i try to Complete refresh a Materialized View in 12c Database residing on Exadata, The query was not making use of Smart Scan on a Large Table which is Partitioned. It is scanning through all the partitions and taking 61 minutes for the refresh to complete.


Insert into Select with append hint: When i try to make use of Insert into Select approach , the query is making use of Cell Offloading and the data load completes in 3 minutes.

Kindly advice if there are any differences in the way the MV is refreshed and not making use of the Offloading process.

Thanks for your help!!

and Connor said...

Ultimately, materialized refreshes are just standard SQL under the covers, and I'm unaware of any inbuilt limitation to prohibit the use of smart scans.

I would activate a sql trace on the refresh - and lets have a look at the SQL's that are generated, and then see what's stopping them from being smart scanned.

I did the following simple example here

create materialized view MV1 
refresh complete 
on demand
as select owner, count(*), sum(object_id) from t
group by owner;


and the refreshes (atomic=true and false respectively) yielded:

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MCDONAC"."MV1"("OWNER","COUNT(*)","SUM(OBJECT_ID)") SELECT "T"."OWNER",COUNT(*),SUM("T"."OBJECT_ID") FROM "T" "T" GROUP BY "T"."OWNER"


/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "MCDONAC"."MV1"("OWNER","COUNT(*)","SUM(OBJECT_ID)") SELECT "T"."OWNER",COUNT(*),SUM("T"."OBJECT_ID") FROM "T" "T" GROUP BY "T"."OWNER"


the latter looks like a standard insert-append, but you can take the SQL_ID from the trace file and check v$sql for offloading etc.

Hope this helps.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions