In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article focuses on "what is the difference between Oracle normal view and materialized view", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the difference between Oracle normal view and materialized view"!
Materialized view is a special physical table, and "Materialized" view is relative to ordinary view. Ordinary view is a virtual table, which has great limitations in application. Any query to the view, Oracle, is actually transformed into the query of the view SQL statement. This has no real benefit to the overall query performance.
1. Types of materialized views: ON DEMAND, ON COMMIT
The difference between the two lies in the refresh method. ON DEMAND, as the name implies, only when the materialized view "needs" to be refreshed, it is refreshed (REFRESH), that is, to update the materialized view to ensure consistency with the base table data; while ON COMMIT means that once the base table has COMMIT, that is, the transaction commits, it immediately refreshes and updates the materialized view to make the data consistent with the base table.
2. ON DEMAND materialized view
The creation of materialized view itself is very complex and needs to optimize the parameter setting, especially for large-scale production database systems. But Oracle allows you to do it in the simplest way, similar to a normal view, so it inevitably involves default values. In other words, we should pay special attention to the default value handling of the important definition parameters of the materialized view by Oracle.
Characteristics of materialized views:
(1) materialized view is, in a sense, a physical table (and not just a physical table), which is supported by the fact that it can be queried by user_tables.
(2) materialized view is also a kind of segment, so it has its own physical storage properties.
(3) materialized views will occupy the disk space of the database, which can be proved by the query results of user_segment.
Create statement: create materialized view mv_name as select * from table_name
By default, Oracle defaults to FORCE and DEMAND if you do not specify a refresh method and refresh mode.
3. How can the data of materialized views be updated with the base table?
Oracle provides two ways, manual refresh and automatic refresh. The default is manual refresh. In other words, we manually execute a system-level stored procedure or package provided by Oracle to ensure that the materialized view is consistent with the base table data. This is the most basic refresh method. Automatic refresh, that is, Oracle creates a job through which the same stored procedure or package is called and implemented.
4. The characteristics of ON DEMAND materialized view and the difference between ON COMMIT materialized view and ON COMMIT materialized view.
The former does not update the materialized view without refreshing (manually or automatically), while the latter updates the materialized view without refreshing-- as long as COMMIT occurs in the base table.
Create a materialized view that is refreshed regularly:
Create materialized view mv_name refresh force on demand start with sysdatenext sysdate+1 (specifies that the materialized view is refreshed once a day)
The materialized view created above is refreshed every day, but no refresh time is specified. If you want to specify a refresh time (for example, a regular refresh at 10:00 every evening):
Create materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate+1,'dd-mm-yyyy'),'22 to_char 0012), 'dd-mm-yyyy hh34:mi:ss')
5. ON COMMIT materialized view
The creation of the materialized view of ON COMMIT is not much different from the materialized view of ON DEMAND created above. Because ON DEMAND is the default, ON COMMIT materializes the view and needs to add another parameter.
It's important to note that you can't just specify ON COMMIT when you define it, and you have to attach a parameter.
Create an ON COMMIT materialized view:
Create materialized view mv_name refresh force on commit as select * from table_name
Note: in the actual creation process, the base table needs to be constrained by a primary key, otherwise an error will be reported (ORA-12014)
6. Refresh of materialized view
Refresh: refers to when and how the materialized view synchronizes with the base table after the DML operation occurs. There are two modes of refresh: ON DEMAND and ON COMMIT. (as mentioned above)
There are four ways to refresh: FAST, COMPLETE, FORCE, and NEVER.
FAST refresh uses an incremental refresh, refreshing only the modifications made since the last refresh. COMPLETE refresh completely refreshes the entire materialized view. If you choose FORCE mode, Oracle will determine whether it can be refreshed quickly when refreshing, and if so, use FAST mode, otherwise use COMPLETE mode. NEVER means that the materialized view does not do any refresh.
You can change the refresh method of the materialized view that has been created, such as changing the refresh method of the materialized view mv_name to refresh it at 10:00 every night:
Alter materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate+1,'dd-mm-yyyy'),'22 to_char 0012), 'dd-mm-yyyy hh34:mi:ss')
7. Materialized views have the same characteristics as tables, so we can create indexes for them just like tables, in the same way as tables.
8. Delete the materialized view:
Although the materialized view is managed with the table, in the frequently used PLSQL tools, you can't delete the table by deleting the table (right-clicking on the table, selecting 'drop' does not delete the materialized view). You can use statements to do this:
Drop materialized view mv_name==
Other references the difference between normal view and materialized view
The ordinary view and the materialized view are not the same thing at all. First of all, we understand the basic concept. The ordinary view does not store any data, but it only has the definition. In the query, it is converted to the corresponding definition SQL to query, while the materialized view converts the data into a table, which actually stores the data, so that the query data does not have to be associated with a lot of tables, if the table is very large. A lot of work is done in temporary tablespaces.
There are three characteristics of normal view:
1. It is something that simplifies the design and codes clearly. It does not improve performance. Its existence will only degrade performance (such as 7 table associations in one view and 8 tables in another view. Programmers do not know and find it convenient to associate two views to make another view.), his existence has not finished the convenience in design.
2, the second is security, in the authorization to other users or view point of view, multiple table association is only allowed to view, not allowed to modify, a single table can also be controlled with WITH READ ONLY, of course, some projects based on the view to do object-oriented development, that is, to do INSTAND OF triggers on the view, as far as I am concerned, although the development is convenient, it may not be a good thing.
3. If you look at different dimensions from different angles, the view can divide dimensions and permissions, and integrate multiple dimensions, that is, what you want can be seen from different angles, while the table is an entity. Generally, there are fewer dimensions (for example, the personnel table is associated with the identity table, and you can view the dimensional statistics of people from the personnel table. From the identity point of view, you can see who or how many people there are in different kinds of identities). Secondly, another view, such as the system view USER_TABLE, TAB, USER_OBJECTS, these views, different users must see different, looking at their own things.
Materialized views are used in OLAP systems. Of course, a small part of the functions of OLTP systems will improve performance a little bit, because table association costs a lot, so in development, many people like to transfer this price to a regular rollover. Of course, ORACLE also provides this function, that is, to convert the information of views (or a large SQL) into physical data storage. Then provide different strategies: regular brush or timely brush, incremental brush or global brush and so on can be selected according to the actual situation, in short, you are poor is the table, not the view.
About the difference between refresh and index
I don't know if you mean the refresh of the materialized view, because the concept of refresh is very general. I understand it as the refresh of the materialized view at this point. As mentioned above, this is a strategy and method. In fact, it is synchronized to the materialized view according to the SQL of the log information by creating a corresponding LOG on the view association table. Generally speaking, timing is generally a global brush. In time, it is usually a local brush.
And index this is said to be more, it can be said that the index is a special course, generally speaking, the index generally has a general index, bitmap index, unique index (and full-text index, generally not used), in fact, careful study will find that no matter what kind of index is based on the B+ tree, and the storage method is the same as the table, is a paragraph as a unit, but there is a tree relationship within.
1, the ordinary index is to find the first one according to the B+ tree (the index is in order), and then find the unqualified healthy values in the backward order based on the current one.
2. The bitmap encodes and stores the values of the leaf node according to the type of bitmap on the leaf node (if the field has three values of 1, 2 and 3, there will be three bitmaps on the leaf node. Each bitmap is stored as 1, 2 and 3 according to the healthy value and ROWID order, so the statistics is very fast under RBO, and it will generally be considered as a general index under CBO).
3. It is also found according to the B+ tree. If you find it, you will no longer do anything because it is unique.
Because B+ lookup is a query similar to a table, and you still have to query back to the table after getting the ROWID, so the cost of this process is faster than the result of the full table scan, and the ORACLE will choose whether to take the index or the full table scan. Of course, the way you choose for CBO and RBO is different, and there are many details. CBO depends on table statistics, and RBO depends on trying.
At this point, I believe you have a deeper understanding of the difference between "Oracle normal view and materialized view". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.