If you are working as a database administrator and have an interest to maintain the Oracle data warehouse in your company, you should need to learn special skill sets and extensive tuning of large SQL queries for obtaining superior optimal performance. Well, backup and recovery strategy is another required skill that you need to pay concentration to because data warehousing is generally a large database with large storage.
What is an Oracle Data Warehouse?
Most of the Business transaction databases are OLTP (Online transaction processing).
A data warehouse is a special relational database that is designed for specific purpose other than Online transaction and processing (OLTP). A data warehouse is designed for query and analysis of Business Data.
It usually contains historical data derived from transaction data, but it can include data from other sources. It separates the analysis workload from the transaction workload and enables an organization to consolidate data from several sources.
Oracle Data warehouse database has all components of Online transaction Processing (OLTP) database features, On top of that data warehouse environment, includes an extraction, transportation, transformation, and loading (ETL) solution, client analysis tools, online analytical processing (OLAP) engine and other applications that manage the process of gathering data and delivering it to business users.
The data warehouse is a database specially designed for specific data analysis based on business requirements. The Data warehouse’s main focus is on various reporting needs to answer various decision support needs. For example, if you are in a marketing business, reports such as who was the most valuable customer.
Well, If a Data warehouse needs to deliver complex and comprehensive reporting information across all available systems in a business, it needs to be linked to various database systems in a given Business. Thus, integrating various parts of the company information is another important part of designing the data warehouse.
Yet another important component of data warehouse design is in maintaining a large amount of data. If you have to generate reports on various permutations and combinations possible to support the decision-making, you need to have to support underlying data somewhere in the database. Storing such a large amount of information is a challenge that obviously requires a large amount of storage.
Storage in the Data warehouse is normally in Terra bytes, depending on the type of Business.
When there is a huge amount of data stored in the database, extracting it through queries to produce required reports becomes a huge challenge. Due to this voluminous data, the performance of queries is also important. To support such special requirements, Oracle Data Warehouse is designed completely with a different approach compared to the traditional Online Transaction Processing (OLTP) relational databases.
Because of the above-mentioned reason, one of the differences between data warehouses is that it is not usually in third normal form (3NF), this type of data normalization is usually common in Online Transaction Processing (OLTP) environments.
Since Data warehouses and Online transaction Processing (OLTP) systems functionality varies and thus their design requirements.
Here are some examples of differences between typical data warehouses and OLTP systems:
Characteristics of Data Warehouse:
In the Data warehouse, end users do not update data directly. However, the data warehouse is updated on a regular basis with data from other OLTP systems and loaded using bulk data loading techniques. This process is referred as ETL (Extraction, Transform and Loading) process.
Due to massive data stored in data warehouse databases, the query optimization is achieved by de-normalized schemas (such as a star schema) and often data is in de-normalized form.
Adhoc query is the very basis of data warehouse design, it may not be possible to know the exact workload of your data in advance, so it is important to design the data warehouse to perform well for a wide variety of possible query operations. For instance, a typical data warehouse query scans thousands or millions of rows.
The data warehouse is a decision support system which supports historical analysis, it stores many months to many years of data. Due to this massive data, a data warehouse requires massive storage requirements.
Characteristics of Online Transaction Processing (OLTP):
In OLTP systems, users update the data regularly with update/insert/delete statements, which also keeps the OLTP system up to date.
OLTP systems often use fully standardized schemas to optimize update / insert/delete performance and to ensure data consistency.
OLTP systems typically store data for only a few weeks or months depending on business needs and only store historical data as needed to successfully meet the demands of the current transaction.
Due to the fact that OLTP systems don’t store any historical data, storage requirement on OLTP systems compared to Data warehouse is limited.
As an Oracle database administrator on Oracle Data Warehouse, you need to understand certain data warehouse concepts.
 Oracle data warehousing concepts =>
- Â Â Â Overview of Data Warehouse Design
- Â Â Â Partitioning and Partition pruning
- Â Â Â Query response time tuning with Parallel operations
- Â Â Â ETL (Extract, Transform and Load data) in the data warehouse.
- Â Â Â Data warehouse performance enhancement using Materialized views
- Â Â Â Query rewrite using materialized views
- Â Â Â Working with Dimensions
- Â Â Â Tune materialized views for fast refresh and query rewrite using SQL Access Advisor and PL/SQL procedures.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.