创建物化视图详解(图解)
创建物化视图详解 一,什么是物化视图 物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 二,作用、 在类似统计功能中,查询操作是无可避免,而这
创建物化视图详解
一,什么是物化视图
物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。
二,作用、
在类似统计功能中,查询操作是无可避免,而这些查询操作如果很频繁,对整体数据库性能是很致命的。而物化视图实现远程数据源与本地数据的实时同步,也就是定时刷新,通过在本地创建物化视图可以大大提高查询效率。
三,流程图:
当用户要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中必须创建了远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。所以需要再在普通视图(view)上建立物化视图,程序通过对物化视图的访问可以大大提高效率。
四,图解步骤(使用PL/SQL)
1,客户端配置网络服务名
2,创建DataBase Links
3,创建物化视图
修改SQL,添加创建视图的模式,刷新的方式和同步时间间隔:
说明:
创建物化视图的模式的方式有两种:ON DEMAND 和ON COMMIT:
ON DEMAND指物化视图在用户需要的时候进行刷新,可以手工刷新,也可以通过JOB定时进行刷新。
ON COMMIT指物化视图在对基表的DML操作提交的同时进行刷新。
刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER
FAST:采用增量刷新,只刷新自上次刷新以后进行的修改。
COMPLETE:对整个物化视图进行完全的刷新
FORCE: Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。
NEVER指物化视图不进行任何刷新。
默认值是FORCE ON DEMAND。
4,在物化视图上创建视图(统计)
5,在统计视图上创建物化视图
五,使用命令创建步骤:
1,创建DB link
-- Drop existing database link
drop database link ZHUHAI.COM;
-- Create database link
create database link ZHUHAI.COM
connect to ZHUHAI
using 'haikou';
2,创建物化视图
CREATE MATERIALIZED VIEW MV_DBDIC
REFRESH FORCE ON DEMAND
START WITH SYSDATE
NEXT SYSDATE+(2/(24*3600))
AS
SELECT "DBDIC"."ID" "ID","DBDIC"."DBNUM" "DBNUM","DBDIC"."NAME" "NAME" FROM "DBDIC"@ZHUHAI.COM "DBDIC";
3,创建视图
create or replace view countview as
select COUNT(*) COUNT
from MV_DBDIC;
4,在步骤3创建的视图上创建物化视图
create materialized view MV_COUNTVIEW
refresh force on demand
as
select *
from COUNTVIEW;
六,注意点:
1,需要先在客户端配置数据源网络服务名
2,数据源的源表必须有主键
备忘,如果有哪位同志有什么疑问,随时可以q我,共同进步
qq :843620202

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Windows operating system is one of the most popular operating systems in the world, and its new version Win11 has attracted much attention. In the Win11 system, obtaining administrator rights is an important operation. Administrator rights allow users to perform more operations and settings on the system. This article will introduce in detail how to obtain administrator permissions in Win11 system and how to effectively manage permissions. In the Win11 system, administrator rights are divided into two types: local administrator and domain administrator. A local administrator has full administrative rights to the local computer

Detailed explanation of division operation in OracleSQL In OracleSQL, division operation is a common and important mathematical operation, used to calculate the result of dividing two numbers. Division is often used in database queries, so understanding the division operation and its usage in OracleSQL is one of the essential skills for database developers. This article will discuss the relevant knowledge of division operations in OracleSQL in detail and provide specific code examples for readers' reference. 1. Division operation in OracleSQL

This article will interest you if you are interested in using GIMP for pixel art creation on Windows. GIMP is a well-known graphics editing software that is not only free and open source, but also helps users create beautiful images and designs easily. In addition to being suitable for beginners and professional designers alike, GIMP can also be used to create pixel art, a form of digital art that utilizes pixels as the only building blocks for drawing and creating. How to Create Pixel Art in GIMP Here are the main steps to create pixel pictures using GIMP on a Windows PC: Download and install GIMP, then launch the application. Create a new image. Resize width and height. Select the pencil tool. Set the brush type to pixels. set up

Many friends expressed that they want to know how to create a family in Gree+ software. Here is the operation method for you. Friends who want to know more, come and take a look with me. First, open the Gree+ software on your mobile phone and log in. Then, in the options bar at the bottom of the page, click the "My" option on the far right to enter the personal account page. 2. After coming to my page, there is a "Create Family" option under "Family". After finding it, click on it to enter. 3. Next jump to the page to create a family, enter the family name to be set in the input box according to the prompts, and click the "Save" button in the upper right corner after entering it. 4. Finally, a "save successfully" prompt will pop up at the bottom of the page, indicating that the family has been successfully created.

The modulo operator (%) in PHP is used to obtain the remainder of the division of two numbers. In this article, we will discuss the role and usage of the modulo operator in detail, and provide specific code examples to help readers better understand. 1. The role of the modulo operator In mathematics, when we divide an integer by another integer, we get a quotient and a remainder. For example, when we divide 10 by 3, the quotient is 3 and the remainder is 1. The modulo operator is used to obtain this remainder. 2. Usage of the modulo operator In PHP, use the % symbol to represent the modulus

In iOS17, Apple has added a contact poster feature to its commonly used Phone and Contacts apps. This feature allows users to set personalized posters for each contact, making the address book more visual and personal. Contact posters can help users identify and locate specific contacts more quickly, improving user experience. Through this feature, users can add specific pictures or logos to each contact according to their preferences and needs, making the address book interface more vivid. Apple in iOS17 provides iPhone users with a novel way to express themselves, and added a personalizable contact poster. The Contact Poster feature allows you to display unique, personalized content when calling other iPhone users. you

As a programming language widely used in the field of software development, C language is the first choice for many beginner programmers. Learning C language can not only help us establish the basic knowledge of programming, but also improve our problem-solving and thinking abilities. This article will introduce in detail a C language learning roadmap to help beginners better plan their learning process. 1. Learn basic grammar Before starting to learn C language, we first need to understand the basic grammar rules of C language. This includes variables and data types, operators, control statements (such as if statements,

Title: Realme Phone Beginner’s Guide: How to Create Folders on Realme Phone? In today's society, mobile phones have become an indispensable tool in people's lives. As a popular smartphone brand, Realme Phone is loved by users for its simple and practical operating system. In the process of using Realme phones, many people may encounter situations where they need to organize files and applications on their phones, and creating folders is an effective way. This article will introduce how to create folders on Realme phones to help users better manage their phone content. No.
