Thanks. This is hands down the best tutorial I've ever watched, on anything!
@TheMagicofSQL4 жыл бұрын
Thanks, glad you found this useful :)
@brandonbraner2 жыл бұрын
At first I saw the bricks and though wtf is this.... they really really helped. Great Job
@oah84652 жыл бұрын
Now, I see where mysql lacks hugely. Thank you man. Fantastic video as usual.
@TheMagicofSQL2 жыл бұрын
You're welcome, thanks!
@sagarkumar-en1se3 жыл бұрын
Sir.. Your teaching style is unique.Easily understood.. Thanks.. Much appreciated
@TheMagicofSQL3 жыл бұрын
You're welcome, thanks!
@welltv11423 жыл бұрын
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! :)
@TheMagicofSQL3 жыл бұрын
You're welcome, glad you find these useful :)
@dhatrimukkamalla3 жыл бұрын
Am I your 10,000th subscriber? Very engaging video!
@TheMagicofSQL3 жыл бұрын
I do believe you are, thanks :)
@User-ty2ml2 жыл бұрын
10 out of 10. Wish you good luck!!!!
@Grantmadeachannel2 жыл бұрын
@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
@nhungbi24017 ай бұрын
I love your teaching vibe!!!
@TheMagicofSQL7 ай бұрын
Thanks, glad you enjoy these!
@koskarvounis3 жыл бұрын
9:01 "Coming for you with axes" 🤣
@marcelvanLare4 жыл бұрын
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.
@TheMagicofSQL4 жыл бұрын
Thanks, I'll bear that in mind for future videos.
@MrBoomBoom2253 жыл бұрын
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!
@TheMagicofSQL3 жыл бұрын
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
@shrutisharma34693 жыл бұрын
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?
@TheMagicofSQL3 жыл бұрын
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!
@ash4323 жыл бұрын
Subscribed sir!
@glauberbispocruzcarvalho22353 жыл бұрын
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)
@TheMagicofSQL3 жыл бұрын
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.
@glauberbispocruzcarvalho22353 жыл бұрын
@@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.
@tamanebp3 жыл бұрын
Great info! I suppose this doesn't help if I have to make a query based on a time range though :(
@TheMagicofSQL3 жыл бұрын
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
@tamanebp3 жыл бұрын
@@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.
@TheMagicofSQL3 жыл бұрын
You can have up to 1,000 columns in an MV, 5 should be no trouble :)
@kumarsatyam52182 жыл бұрын
how does the materilized view are behind the scence syncing the changes in the underlying table?
@TheMagicofSQL2 жыл бұрын
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_chhetri097 ай бұрын
Can we do fast refresh in complex materialized view? If its possible how to achieve it?
@TheMagicofSQL6 ай бұрын
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
@emonymph69113 ай бұрын
@@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.
@TheMagicofSQL3 ай бұрын
@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.
@emonymph69113 ай бұрын
@@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-fi7pc2 жыл бұрын
Sir,,it's Oracle, SQL server different syntax
@TheMagicofSQL2 жыл бұрын
The videos on this channel use Oracle SQL
@kanishksaxena77353 жыл бұрын
how can we make a materialized view in java... hibernate...Urgent Help
@TheMagicofSQL3 жыл бұрын
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
@dummuvikash49572 жыл бұрын
great
@cartman_soc3 жыл бұрын
top, bem didático
@scarlatum2 жыл бұрын
- How count items fast? - Just remember quantity of them, dummy.