Count Millions of Rows Fast with Materialized Views: Databases for Developers: Performance #6
Vložit
- čas přidán 22. 06. 2020
- Querying returning totals per day, week or month can process billions of rows. But only return a handful.
You can make these queries orders of magnitude faster by precomputing the result using materialized views.
Watch this video to learn how these work.
For details on fast refresh restrictions, see:
www.oracle.com/pls/topic/look...
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement. - Věda a technologie
Thanks. This is hands down the best tutorial I've ever watched, on anything!
Thanks, glad you found this useful :)
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! :)
You're welcome, glad you find these useful :)
Sir.. Your teaching style is unique.Easily understood.. Thanks.. Much appreciated
You're welcome, thanks!
Now, I see where mysql lacks hugely. Thank you man. Fantastic video as usual.
You're welcome, thanks!
@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)
10 out of 10. Wish you good luck!!!!
At first I saw the bricks and though wtf is this.... they really really helped. Great Job
That is really great and simple explanation, thanks
I love your teaching vibe!!!
Thanks, glad you enjoy these!
Am I your 10,000th subscriber? Very engaging video!
I do believe you are, thanks :)
9:01 "Coming for you with axes" 🤣
Subscribed sir!
top, bem didático
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.
Thanks, I'll bear that in mind for future videos.
great
how does the materilized view are behind the scence syncing the changes in the underlying table?
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
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?
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!
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)
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.
@@TheMagicofSQL Thanks
@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?
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.
Thanks for the response.
Great info! I suppose this doesn't help if I have to make a query based on a time range though :(
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
@@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.
You can have up to 1,000 columns in an MV, 5 should be no trouble :)
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!
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
how can we make a materialized view in java... hibernate...Urgent Help
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
Can we do fast refresh in complex materialized view? If its possible how to achieve it?
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
Sir,,it's Oracle,
SQL server different syntax
The videos on this channel use Oracle SQL
i thought this for clickhouse XD
- How count items fast?
- Just remember quantity of them, dummy.