oracle table partitioning,Local indexing,oracle parallel query,oracle recovery,oracle olap

Partitioned objects allow Oracle objects, such as tables and indexes, to be broken into smaller, more manageable pieces. Partitioning these objects allows many operations that could normally be performed on only a table or an index to be divided into operations on a partition. By dividing these operations, you can often increase the parallelism of those operations, thus improving performance and minimizing system downtime.

Partitions are enabled via the PARTITION BY RANGE parameter of the CREATE TABLE statement. In this manner, ranges of data are assigned to each individual partition like so: CREATE TABLE emp
(
name CHAR(30),
address CHAR(40),
region INTEGER
)
PARTITION BY RANGE ( region)
(
PARTITION VALUES LESS THAN (10) TABLESPACE tbl0,
PARTITION VALUES LESS THAN (20) TABLESPACE tbl1,
PARTITION VALUES LESS THAN (30) TABLESPACE tbl2
);

Table partitioning

Partitioning is recommended for large tables because it makes them much more manageable. Oracle does not currently support partitioning of clusters. By partitioning a table, you can break that large table into several much smaller pieces. A partitioned table can take advantage of some of the following features:

Partitioned DML

Parallel INSERT, DELETE, and UPDATE operations can occur on a partition basis. Using partitions allows these operations to be conducted either globally or locally within a partition.

Exporting/Importing by Partition

Partitioning allows operations such as exports and imports to be performed on a partition basis. This can reduce the time required by some maintenance operations, such as reorganization of data or reclustering. This also allows you to change the physical layout of your database on a partition basis. If you limit the scope of export and import operations, they can benefit from a large degree of parallelism.

Range Partitioning

Range partitioning is a method whereby the partitioning of data is done based on the value of the data itself. This allows for tremendous flexibility in distributing data based on ranges of data values. Range partitioning allows you to partition high-volume data separately from low-volume data or to separate current from old data.

Local and Global Indexing

New Term: A local index indexes data that resides in only one partition. A global index indexes data that resides on more than one partition. This allows for great flexibility in terms of adding new indexes, reducing index sizes, and allowing for partition independence.

An example of where local indexing might be beneficial is a table where sales records are stored. Using table and index partitioning, you can store data and indexes separately based on calendar months; doing this allows reduced index size and faster index lookups for entries of a particular month. If you partition these entries you can add new months and delete outdated entries without reindexing the entire table. You could keep 12 months of partitions and indexes online in this manner.

Parallel Loading by Partition

With a partitioned table, SQL*Loader can either load an entire table in parallel by partition or simply load a single partition. Either method provides great flexibility.

If you use the conventional path load, the loader automatically distributes the data to the correct partition and updates the local and global indexes. You can also use the loader to load a partitioned table or a partition of a table. Again, indexes are built automatically. It is also possible to direct-load a partition in parallel provided that no global indexes exist, but you must rebuild the local indexes yourself.

Improved Parallelism

The arrival of Oracle8 has heralded tremendous improvement in the area of parallelization. In addition to the new parallel features listed previously, some existing parallel operations have been extended.

Parallel recovery has been improved by allowing rollbacks of parallel DML operations that have failed to be performed in parallel. This parallel transaction recovery is supported on transaction and process failures but not during instance recovery.

New parallel hints have been added for parallel insert operations. The APPEND hint tells the optimizer to append the insert data beyond the high water mark of the segment.

New Index Types

The index-only table is new in Oracle8. With traditional indexes and tables, data and indexes are stored separately. With an index-only table, the data to which the index refers is stored in the leaf block or lowest level block of the index, so the data and indexes are stored together. Depending on your application, this can be an advantage.

Applications that access data primarily via a key value can see an advantage from the use of index-only tables. Because the data is stored within the index, the data is immediately available when the index has reached its lowest level. This can speed data retrieval.

Applications that do not access data primarily via a key value will see no improvement; indeed, performance will likely be degraded in these applications. Any application that involves table scans or requires multiple indexes will not benefit from the index table.

Enhanced Recovery Features

Oracle has made tremendous improvements in the areas of backup and recovery. Most of these new features revolve around the Recovery Manager. Another recovery feature in Oracle8 is the image copy backup, which can improve recovery time in the event of a failure.

Recovery Manager

New Term: Recovery Manager is an online utility designed to assist the DBA with all backup and recovery operations. Not only does it perform the backup and recovery, it maintains a database called the recovery catalog that stores information about these operations.

Image Copy Backup

An image copy backup essentially allows you to copy a datafile to another place on disk or to another disk on your system. In the event of a failure, no recovery is necessary from the image copy; you must simply switch to that backup copy. You must, however, perform a recovery to make that copy current. In the event of a failure, this might be the fastest way to recover.

Oracle Products

As part of the overview of the Oracle system, I would like to briefly cover the optional available Oracle products. Although many of these products are covered elsewhere in this book, you should at least aware of their existence. The Oracle product line is divided into three areas:

The Oracle Server

The Oracle server is the DBMS itself, and includes many options and features such as the Parallel Query option, network protocols, and advanced system administration options. Some of the key options available to the Oracle server include

* Enterprise Manager--This option is fairly new to Oracle, and consists of the management console and intelligent agents. The management console, which is the core element in Oracle's new graphical administrative package, runs only on Windows NT, but can manage any Oracle server. The console allows the DBA to graphically control one or more Oracle systems. The console can be used to configure and manage Oracle instances as well as to diagnose problems and can be configured to alert the DBA in the event of a problem.

* The keys to Enterprise Manager are the intelligent agents, which run on the Oracle server and provide the communication layer necessary for the console to communicate with these systems. The intelligent agents use industry-standard SNMP (Simple Network Management Protocols) to communicate with the console, thus allowing for future expansion.

NOTE:Throughout this book, Enterprise Manager is referenced as the primary method for administering the system. Nonetheless, command-line management is also covered.

* ConText--When integrated with any text system, Oracle ConText can analyze, filter, and reduce text for speed reading and summary viewing. Oracle ConText returns detailed assessments of the text it processes, checking for grammatical errors and rating the quality and style of the writing.

* Media Server--Oracle Media Server provides high-performance, scalable, and reliable multimedia library functions on a wide variety of general-purpose systems. Media Server handles the storage, retrieval, and management of movies, music, photographs, and text articles.

* The Spatial Data option--The Oracle Spatial Data option can be used to manage a database that contains spatial data. This option allows for the storage of spatial or geographical data. If you store the spatial data within the database, the complexity of managing the storage is reduced and the performance is increased.

* The Oracle Web server--The Oracle Web server is designed to provide front-end services to allow World Wide Web access to an Oracle database. This product allows Web users to retrieve information directly from an Oracle database rather than from traditional flat files. This product can be used to enhance the performance and functionality of your Web server via the use of indexes and data caching. With the flexibility of the Oracle RDBMS, the functionality of your Web server can be enhanced via the use of language-sensitive context and other features.

* The Internet Commerce server--The Internet Commerce server is a complete set of tools designed to help you create, run, and administer an Oracle system that is used for Web commerce. Because it is based on the proven technology of the Oracle server, the system can provide these services in a robust and secure fashion.

Development Tools

* One of Oracle's strongest points has been its development tools. Not only are these tools robust and full featured, they are flexible as well. When client/server systems became popular in the early 1990s, the Oracle tools quickly adapted. When HTML and Java applications became popular in the mid-1990s, the Oracle development tools quickly adapted yet again. The adaptability of these tools guarantees that applications developed with them can be quickly adjusted for new uses and technologies.Oracle provides the following tools:

* Designer/2000--This set of modeling tools reduces some of the pain associated with designing systems. These tools, which help with process and data modeling, can be used to provide input into the Developer/2000 system and to develop the fundamental models that are the foundation for your business processes.

* Developer/2000--This set of tools allows you to create an application and roll it out in Windows, Macintosh, Motif, and character mode. Developer/2000 incorporates graphics and images as well as support for multimedia objects such as video and sound in a variety of standard formats.

* Discoverer/2000--This data-analysis tool supports querying, reporting, and the graphical multidimensional analysis of the data warehouse. Its key features include graphical-representation and drill-down features.

* Power Objects--This lightweight, GUI development tool, which is available for Windows, Macintosh, and OS/2, allows the quick development of applications that use relatively small system resources. Power Objects is conceptually similar to Developer/2000, but lacks many of Developer/2000's features.

* Objects for OLE--This set of tools allows you to link OLE-compliant applications to an Oracle RDBMS. This tool provides a quick and easy way to exploit the power of applications such as spreadsheets. Objects for OLE also allows easy linking of database tables into word-processing documents.

* Programmer/2000--This suite of tools helps with the development of SQL, PL/SQL, and stored procedures. These tools can be helpful for application developers.

* Media Objects--Oracle's lightweight tool for developing multimedia applications, Media Objects supports client/server, CD-ROM, and interactive television processes.

* Database Designer--This lightweight version of the Oracle Designer/2000 product can assist in the design and creation of databases. Database Designer, a single-user tool, graphically designs the database tables and generates SQL that can be used to create this database.

Applications

Oracle's application software falls into two main categories: traditional applications and newer OLAP (Online Analytical Processing) applications.

Traditional Oracle Applications

Oracle's suite of traditional applications is used to perform basic and essential business tasks. These applications are used by many of the world's largest companies. The suite provides support for the following areas:

OLAP Applications

The OLAP applications provide a graphical interface for DSS and data-warehousing applications. These tools lend a multidimensional model to the database, providing analysis, forecasting, and statistical operations.

Other Products

Oracle offers many other products that are not mentioned here. These products handle various tasks such as networking, office automation, workgrouping, and so on. Although these products and services are too numerous to cover here, rest assured that Oracle's full line can handle most (if not all) of your database and communication needs.

Better to know some

... than all

Oracle Architecture

Oracle8 New Features

Partitioned Objects