ORACLE - The most popular database system in the world

OracleOracle is one of the largest corporations in the world. It was established in 1997 by a present president Larry Ellison, Bob Miner and Ed Oates. Oracle provides over 370 thousand clients all over the world, including all 100 companies being on Fortune 100 list, with the most complete, open and integrated software and computer hardware system.

Oracle Database is the most popular product of the company. Database management system (DBMS), developed from the very beginning of the company's existence, is currently available in 11g version. The basic language used for communication with Oracle database is standard query language SQL. Moreover, DBMS is characterized by built-in internal procedural language PL/SQL. PL/SQL is an extension of SQL language empowering creation of such constructions as loops, conditional instructions and variables. It can be used for creating triggers, functions and procedures. Java is the third built-in Oracle database language, which can also be used for creating stored procedures.

In accordance with analyses carried out in 2010, DBMS Oracle software has about 48% market share, which constitutes more than joint shares of five most important competitors (source: Market Share: RDBMS Software, Worldwide, 2010). Nowadays, DBMS Oracle is available in several editions, which vary with regard to different possibilities, and consequently the price. A flagship and most expanded edition is Oracle Enterprise Edition, which might be extended by adding options, e.g. table partition. Oracle Standard Edition is a bit less expensive edition, which is deprived of the possibility of extending options and it does not have all possibilities included in Enterprise edition. Another edition is Oracle Standard Edition One, which is characterized by fewer licence limitations with respect to Oracle Standard Edition. There is also available Oracle Express Edition, a free database edition, Oracle Personal designed for an individual use, and Oracle Lite for mobile use.

The most important characteristics of DBMS Oracle include:

  • Universality - the possibility of creating own stored procedures, own data types and enclosing specialized options (in Enterprise Edition version).
  • Openness - compatibility with ISO SQL norm, compatibility with XML, XML Schema, XSLT, CORBA standards etc. Access to data through typical interfaces ODBC, JDBC and access through WWW.
  • Reliability - automatic recovery after breakdowns, advanced mechanisms of doing backups.
  • High performance - shared resources for repeated queries and advanced cost query optimizer.
  • Security - advanced computer network security mechanisms and differentiated access rights.
  • Mobility - available entirely functional compatible versions for several dozen different platforms.

ORALCE - advanced support for data warehouses

Oracle database can be perfectly used in the area of data warehouse and ETL processes. Long-term experience in the area of database allowed to develop advanced mechanisms supporting processing large data sets. Many of those mechanisms are available only in Oracle Enterprise Edition and this edition is the most suitable in case of large systems.

The goal of Oracle corporation policy is to hold a position of the leader within the area of data warehousing. Therefore, in addition to the Oracle 11g Enterprise Edition, the offer contains a complete hardware-software solution called HP Oracle Database Machine and supporting applications - for instance Automatic Storage Management.

The most important mechanisms delivered by Oracle database aimed at supporting functioning of data warehouses are listed below.

  • Supporting cluster systems - RAC (Real Application Cluster) mechanism allows few instances of DBMS system, located on different servers, for a parallel access to the same database. Such solution allows for connection of user's applications to different machines, and access to the same data. Thanks to that the load is balanced with regard to processing SQL queried, consecutive applications and users are connected to the server, which is currently less loaded.
  • On-line replication service - the task of Oracle Streams mechanism is to empower to propagate all changes between databases in real time. All DMI and DDL operations (data modifications) executed on one base are automatically transferred to the other bases. It allows to maintain fully redundant environment.
  • Automatic performance management and tuning support - Oracle provides a number of mechanisms allowing for performance optimization and supervision over database load. Among them there are i.a. Automatic Memory Management (AMM), which automatically divides available RAM memory between different areas of database engine, depending on the present load, or Automatic Workload Repository (AWR), which provides administrators with a number of statistics and hints concerning the layout and type of database load.
  • Automatic management of data space - Automatic Storage Management (ASM) tool provides database administrators with a coherent, environmental-independent interface for management of data space. It allows to automatize space allocation, as well as it optimizes its usage.
  • Partitioning tables and indexes - partition allows to divide data in a table into fragments (each fragment is a sort of a separate sub-table). Values, which are most frequently used for searching are assumed as a partition key, for instance the date of loading data. Thanks to the partition mechanism, during making a query, a database obtains a direct access to data fragment without the necessity of searching an index or whole table.
  • Query plan hints - Oracle Hints mechanism allows to indicate database, during creating SQL query, in which sequence fragments of this query need to be executed, whether indexes have to be used etc. Thanks to that mechanism we gain a full control over a query plan, what is useful when we are precisely familiar with specificity of data in a table, and Oracle query optimizer is not able to make a good plan for executing the query.
  • Parallel queries - when using Oracle Hints mechanism it is possible to describe the numbers of query parallels, i.e. in how many threads it will be executed. In case of parallel a query is divided into fragments. The query execution results are combined and sent to the user.
  • Materialized views - materialized views mean fixed query results stored in a database. A basic difference between an ordinary view and a materialized view is that in the first case querying a view each time generates queries to the tables indicated by this view, and in case of a materialized view data are already prepared and stored in a special data structure. Thanks to that, in case a view hides complicated join of many tables, data are immediately available. The fact that materialized views have to be periodically updated (it might be done automatically) constitutes a limitation of materialized views. It happens so because stored data are not updated together with changes of source tables. Such solution is perfect in case of data warehouses, where the speed of executing queries on a large data set is essential, but there is no pressure that data have to be always up to date.
  • Query optimization in a star scheme - star scheme queries are queries characteristic for data warehouses. In such query there is one large table of facts and a few smaller dimension tables connected to the main table. Oracle has many mechanisms supporting the execution of such queries.
  • Data compression - data compression mechanism built in Oracle allows to save large data space for data storage. During creating a table we may choose, whether it is supposed to be compressed or not. Compressed tables use less data space on a disk, however, this mechanism is not beneficial in case of tables which are frequently updated (delete and update operations). An additional advantage of a compression mechanism is that it significantly decreases the number of disk readings, transferring load into a processor, what may improve the system performance where discs are bottlenecks.

The mechanisms mentioned above are only a trial sample of possibilities of Oracle 11g Enterprise Edition within the scope of support of data processing in data warehouse. Thanks to a long-term experience in this area the employees of Dimensional are able to effectively use all mechanisms offered by database. Theoretical knowledge supported by experience at working with large data sets allow our experts to design data processings correctly and create effective solutions for optimization tasks.

The official website of: Oracle