Enhanced Oracle9i Data Warehouse and Its Value

Welcome to the Oracle community forum, and interact with 2 million technical staff to access the data warehouse. different data sources must be obtained from different data sources, in addition, this huge amount of data is converted into available data for users to provide data support for enterprise decision-making. This process is often called ETL.

Welcome to the Oracle community forum and interact with 2 million technical staff> to enter the data warehouse, you must obtain different data from different data sources, in addition, this huge amount of data is converted into available data for users to provide data support for enterprise decision-making. This process is often called ETL.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

A data warehouse needs to obtain different types of data from different data sources, and convert these huge amounts of data into available data for users, to provide data support for enterprise decision-making. This process is often called ETL (extraction, conversion, and loading ). The extraction process involves extracting data from different sources. For example, some service providers need to extract data from hundreds of websites and then generate available user data. In this process, the most time-consuming part is the conversion and loading of data. In these steps, you must specify rules based on existing data and filter data according to these rules, and load qualified data into the data warehouse. The biggest difficulty in this step is to minimize the impact on the performance of the data warehouse and the source database, and to have certain scalability for different data volumes, and to complete the process in the shortest time.

Currently, most ETL steps are performed using third-party tools. These tools can perform some specific processing on the data before converting and loading the data to the data warehouse. After the data is converted, use the parallel Oracle insertion and loading tool to insert the data to the Oracle database. The primary role of Oracle databases is to manage these data rows, indexes, and constraints. Some ETL processes are serialized, so more database resources need to be used for these conversion and loading processes. If this type of serial loading method is used, the data is first extracted using some third-party tools and then placed in an intermediate transitional area, after PL/SQL or java is used, convert the data in the database and insert the data into the database table. This complex process leads to unavoidable weaknesses in this method: poor scalability, difficult to control in case of errors.

Oracle9i introduced a new "edge loading and edge conversion" method to replace outdated serial processing steps: first convert, then load, or first load and then convert. In this new method, the database participates in the data conversion and loading process and becomes an integral part of the ETL process. In addition, some steps that were originally required do not need to continue to exist, while others can be improved. Oracle 9i provides the following functions to help with this conversion and loading process faster and more efficient.

Oracle Change Data Capture (OCDC) Framework can be used to optimize the Data extraction step in the ETL process and create a reusable execution step. OCDC can capture changed data in the Oracle database. Using symmetric replication and Logminer technology, if the raw data comes from the Oracle database, Oracle can easily synchronize the changed data of several databases and Implement Asynchronous chemical engineering. For non-Oracle databases, Oracle also provides APIs to help third-party software vendors develop tools to integrate non-Oracle databases with Oracle databases.

The External Tables feature allows some External data sources (such as a common text file) to be transparently accessed like data stored in a common Oracle database table. The only restriction of this feature is that the table is read-only and cannot be modified. For SQL, PL/SQL, and Java programs, these virtual tables do not need to be first loaded into the database before they can be read. In this way, the loading and conversion steps are integrated, and intermediate data is no longer needed in the database.

Multi Table Insert is a new feature that any DBA or developer can appreciate. In 8i and earlier versions, if you want to insert the same data into multiple tables, you have to write a stored procedure or execute multiple SQL statements. In Oracle 9i, none of these can be used. An SQL statement can solve this problem. Another noteworthy new feature is the Upset statement. It uses a statement to provide the Update and Insert functions, and executes the Insert or Update statements according to the conditions respectively. Another useful enhancement is to allow multiple blocks in an Oracle database. This is critical for using removable tablespaces between different Oracle databases. Starting from 9i, the size of the movable tablespace may not necessarily be the same block size.

With these new features and other new features not mentioned in this article, Oracle is not only applicable to data processing, but also to data warehouse environments.