[intro music]
Hi there, I am Chris Saxon. The sequel wizard .
In the Cups and Balls episode , we saw how we can use Views
to make rows move between different tables .
Today we're going to take a look behind the curtain and find out
where all those multi colored balls came from.
Let's get started.
In the Cups and Balls trick , I had a Cups table
with different colored views on top of it.
Each View selected out the rows the same color as the View
So we can see in the Cups table got my Red ,Green and Blue balls
but the Red cup just selected out the red ball
Now when I inserted the Blue Green and Red balls into the Red cup, However, we found
we got all the colors of the Rainbow back . How did I do it ?
Lets take a look .
After creating the Cups table, I then inserted all the different colored balls into it.
Like so. Then save them in the table .
These in place , I created a materialized view over the top of the Cups table .
This saved all these different colored balls into the cups_mv.
We can see that by querying it like so
I then deleted all the balls out of the cups table
Gone. And commit it. So at this point , we have the empty Cups table
and materialized View , with all the different colored balls in it.
we can then make these rows reappear, using query rewrite.
As we can see, there is currently no data in our Cups table.
However by setting the query rewrite integrity parameter to stale_tolerated
will be telling Oracle , that we can trust the data,
in the materialized view even though it's out of date .
So we do that set that , when we query the Cups table now,
we find we get , all the colored balls back again .
We can verify we use in query , we do that by the Explain plan
and we can see we've got a materialized view, rewrite operation.
we set query rewrite integrity back to enforced like so,
And query the Cups table again we find it returns nothing.
And Explain plan has come back to a full table access of the Cups table
Query rewrite , what's that all about? I hear you ask
This is a feature that you can use ,in conjunction with materialized views.
As we just saw you can use a materialized view .
to save the results of a query in a table .
If you then execute another SELECT statement,
which is equivalent to the one, that you used in your materialized view,
Oracle can instead of executing your query just look at those pre-saved results.
This can give significant performance benefits,
when you got complex queries, joining multiple tables
or aggregating millions of rows .
Under normal circumstances, Oracle will only use query re-write ,
when the results you saved in your materialized view,
match the results you get , when you actually execute your query .
Now this isn't the case here. We've saved a bunch of multicolored balls
in the materialized view, but just deleted everything out on the table, show that different.
How come we get Oracle to actually use the materialized view in query rewrite .
Lets find out .
The final piece of the puzzle is to understand, how I actually set
the query rewrite integrity parameter.
We look at the setup scripts we can see that
when I said there was no triggers, I was lying.
There aren't any on the views themselves.
However there is a compound trigger, on the Cups table
and what this is doing is counting how many rows were inserted in each statement.
When a single insert statements, inserts three or more
Then we alter the session, this set the query rewrite integrity to stale tolerated.
This means that when I added the red, green and blue balls
we set this enabling us to use, the materialized view
with all those multi colored balls saved in them.
To wrap up, we need to understand
how I got the rows to appear in the Red cup
but not the Green or Blue cups .
Now, the where clause for the Green and Blue cups,
just returns the rows, that the same color as View name .
However with the Red cup
I am returning all the rows that aren't green and aren't blue.
This means we're able to see all those multi colored rows
when we are selecting the information out to the materialized view
At this point you may be saying to yourself, stale data
Why would I want that ?
Why can't my materialized view just always have the up-to-date , correct information?
Well, This 3 reasons why you may wish to use this stale tolerated setting.
Lets take a look at them
Firstly ,they keep the materialized views in sync with the underlying base table
You have to set them to be fast refresh on commit .
Now there's numerous restrictions around the queries you can
use in these types of Views .
The query you are writing is sufficiently complicated
It maybe you hit these issues and can't use this feature.
And has to go with periodic refreshes
Secondly because it won't commit,
Every time you save the change the to the base table , in the materialized view
This will overhead the extra work involved
and this overhead on commit time may be too great for your transactions
in terms of increased response time or actual work on the database.
In this case you may have to go for a scheduled refresh instead,
Finally some tables are insert only, things like transaction table , fact table
In this case when you create materialized view ,
everything looking backwards will be up to date and correct.
You just been missing the most recent inserts
and if you're using these materialized views for historic queries
say Yesterday's information last weeks or even last months ,
then materialized view will work just fine
That was our Behind the curtain look at Magical Cups and Balls Trick .
We saw how we could use a combination of materialized views
along with the query rewrite integrity parameter set this
stale tolerated to view information, as it looked
at some point in the past .
This is a potentially powerful feature,
if you got complicated queries on historic data which just doesn't change.
By using these you can get potentially dramatic performance improvements
in these complex queries .
I am Chris Saxon . Thanks for watching
Thanks for watching . I am Chris Saxon , the sequel wizard.
Subscribe to my YouTube channel to get future updates and more sequel magic
I'll see you next time
[exit music]