Count Millions of Rows Fast with Materialized Views: Databases for Developers: Performance #6

[@theaugmenter]

Thanks. This is hands down the best tutorial I've ever watched, on anything!

[@TheMagicofSQL]

Thanks, glad you found this useful :)

[@brandonbraner]

At first I saw the bricks and though wtf is this.... they really really helped. Great Job

[@oah8465]

Now, I see where mysql lacks hugely. Thank you man. Fantastic video as usual.

[@TheMagicofSQL]

You're welcome, thanks!

[@sagarkumar-en1se]

Sir.. Your teaching style is unique.Easily understood.. Thanks.. Much appreciated

[@TheMagicofSQL]

You're welcome, thanks!

[@welltv1142]

Best SQL Tutorial channel as a whole. Theory is explained on such a basic level, so everyone can understand. Please keep up the awesome work! :)

[@TheMagicofSQL]

You're welcome, glad you find these useful :)

[@dhatrimukkamalla]

Am I your 10,000th subscriber? Very engaging video!

[@TheMagicofSQL]

I do believe you are, thanks :)

[@User-ty2ml]

10 out of 10. Wish you good luck!!!!

[@Grantmadeachannel]

@2:45 query rewrite @5:10 fast refresh with log @7:30 DBMS_MVIEW.EXPLAIN_MVIEW @7:43 fast refresh on COMMIT @8:10 query rewrite stale_tolerated (DB can use query rewrite on MV with stale data - maintains MV speed)

[@baddream29]

That is really great and simple explanation, thanks

[@nhungbi2401]

I love your teaching vibe!!!

[@TheMagicofSQL]

Thanks, glad you enjoy these!

[@koskarvounis]

9:01 "Coming for you with axes" 🤣

[@marcelvanLare]

Nicely done. Good followup would be about importance of indices of fast mviews on refresh. Starters forget that. How to deal with a bulk update on base tables in respect to refreh. Normal fast refresh is row by row so slow on bulk. Option atomic_refresh. Option Out_of_place.

[@TheMagicofSQL]

Thanks, I'll bear that in mind for future videos.

[@MrBoomBoom225]

So if you were to dumb this down.. If I'm following this correctly - So with a materialized view it doesn't need to rerun and recalculate the entire query like a traditional view and updates are made if there's a delta with the base tables? If so, why the heck isn't everybody using this then? This sounds like a solution delivered by God!

[@TheMagicofSQL]

That's a reasonable sum up "why the heck isn't everybody using this then?" Good question :) But it's worth pointing out there are a few reasons not to use MVs: - Some queries are ineligible for fast refresh (applying the deltas) - count distinct is the classic example (though Oracle Database does a solution for that now) - For fast queries, the overhead of maintaining the MV isn't worth the speed gains you get - Apps can have 1,000s of queries; creating MVs to support them all is impractical; you're better off reserving these for a few critical-yet-slow queries I do agree that applications would benefit from using these more widely - particularly for reporting aspects

[@shrutisharma3469]

My query is failing due to out of temp storage even after increasing space. perf team suggested use_nl(a,b,c,d,e) like that. can you explain how this works and how it will help?

[@TheMagicofSQL]

The USE_NL hint instructs the optimizer to use nested loops to join the tables. Will it help? That depends on what your current plan is!

[@ash432]

Subscribed sir!

[@glauberbispocruzcarvalho2235]

duudeee, this explanation using bricks was perfect, thank you a lot *-* by the way, i'm trying to learn some advanced concepts of sql, do you have any suggestion about what should i study ? (sorry bout my english i'm not a native speaker)

[@TheMagicofSQL]

Thanks! For advanced SQL, I'd dig into: Analytic/window functions Pattern matching (match_recognize) On devgym.oracle.com we have a huge library of SQL quizzes; you're sure to find some here to expand your skills And I've got lots of articles on blogs.oracle.com/sql/ covering various aspects you may find useful.

[@glauberbispocruzcarvalho2235]

@@TheMagicofSQL Thanks

[@livethemoment8419]

@5:55, why do I need to have both primary key and rowid together while creating materialized view log? Any one of those 2 is sufficient right?

[@TheMagicofSQL]

The more information you include in the MV log, the more refresh options you have. Including both gives you more options in the future. The MV log should include all the columns you want to use in (fast refreshable) MVs. You need to include the ROWID in some types of queries to allow them to fast refresh. Which also means you need the ROWID in the log. The primary key is also often necessary for joins etc.

[@livethemoment8419]

Thanks for the response.

[@tamanebp]

Great info! I suppose this doesn't help if I have to make a query based on a time range though :(

[@TheMagicofSQL]

It depends on what your query and MV are! As long as it's possible to derive the result of your query using the data stored in an MV, it's possible

[@tamanebp]

@@TheMagicofSQL Whoah, I didn't actually expect a reply, thanks! This query could possibly contain up to 5 fields, not including the datetime field, so I wonder if that be too much of a combinatorial explosion. Watching some of your other vids, has given me some other ideas though.

[@TheMagicofSQL]

You can have up to 1,000 columns in an MV, 5 should be no trouble :)

[@kumarsatyam5218]

how does the materilized view are behind the scence syncing the changes in the underlying table?

[@TheMagicofSQL]

As discussed at 5:29, for fast refresh you need to create materialized view logs on the tables used in the MV. The database then reads these changes to update the MV. Without this a refresh reruns the whole query

[@prashant_chhetri09]

Can we do fast refresh in complex materialized view? If its possible how to achieve it?

[@TheMagicofSQL]

What do you mean by "complex"? There are lots of restrictions on what's possible for fast refresh docs.oracle.com/en/database/oracle/oracle-database/23/dwhsg/basic-materialized-views.html#GUID-505C24CF-5D56-4820-88AA-2221410950E7

[@emonymph6911]

@@TheMagicofSQL loved the video, liked and subbed. what's your take on this? if we have a base table with 200 columns (same entity, sku table) and made a real-time materialized view (rtmv) of the same 200 columns. 1) would the rtmv still run faster than base table if we don't have any join operations (or is mv on a non-joined OR non-aggregated table pointless)? 2) if we want to split the 200 columns into smaller tables for specified user departments... could we create views on the rtmv instead of the base table? and is that best practice or a red flag? basically what im wondering is... is it worth creating a very large materialized view and then create regular views that read from it in smaller chunks (shrinking the rtmv to a smaller table views). or do we have to create like 4 different smaller rtmv for each user department? different departments will update sku data in a dynamic grid (apex) but i can't decide if i should use regular views or materialized views for each departments mini table before their inputs get written back to the base table.

[@TheMagicofSQL]

@emonymph6911 1) Yes, that's pretty much pointless! The point of an MV is to precalculate results. If you're just selecting there's no calculation, making the MV a wasted effort. 2) Splitting 200 columns into tables is worth looking into, particularly if you do so in a way that makes most of the columns on each table mandatory. You could then create views over the top of these to simplify (query) access. To write to them views you may need to create instead of insert triggers, which can get complex. You can't write to MVs. Building MVs over the views to precaculate the join(s)may be worthwhile if the tables store lots of rows. In most cases, it's not really worth doing this until the tables are approaching billions of rows though.

[@emonymph6911]

@@TheMagicofSQL thank you very much. "Splitting 200 columns into tables is worth looking into, particularly if you do so in a way that makes most of the columns on each table mandatory." its for a product table so SKU is the natural PK and i asked a oracle big data dba if i should split the SKU price, SKU specs, SKU dimensions into their own tables but he swore by de-normalizing them to 1 product table to avoid joins at all costs if the natural PK is gonna be the same across each mini table (you can assume they would all have the same row count because the SKU is unique and has to be repeated for each table category - he feels the joins will just slow it). so their recommendation was base 200 column table and make some materialized views for each department. i know how to normalize all the way but their advise confused the hell out of me. if the user is going to CRUD on their mini tables (like excel sheets) i would at least think we should have a denormalized base table and CRUD on the SKU price regular-view, SKU spec regular-view, SKU dimension regular-view etc. Correct? Or would you normalize base table and then make materialized views for price, dimension, spec etc and make your users CRUD operations be applied to each correct base table? (assume de-normalized table will grow to 100k rows)

[@kirankumar-fi7pc]

Sir,,it's Oracle, SQL server different syntax

[@TheMagicofSQL]

The videos on this channel use Oracle SQL

[@kanishksaxena7735]

how can we make a materialized view in java... hibernate...Urgent Help

[@TheMagicofSQL]

I'm not familiar with Hibernate, so I'm not sure exactly. It's just a SQL statement though, so you can create it using whatever allows you to run custom SQL

[@dummuvikash4957]

great

[@cartman_soc]

top, bem didático

[@scarlatum]

- How count items fast? - Just remember quantity of them, dummy.

[@kokizzu]

i thought this for clickhouse XD