Enhancements to Oracle 8 to improve the performance of VLDBs include table and index partitioning, parallel dml, increased size limits and enforced constraints.
This is one of the most useful new features for very large databases and systems that are required to be operational 24 hours per day, 7 days per week.
Large tables and indexes can now be split in to more manageable sections (up to 64,000 partitions per table/index) which can be manipulated independently or as a group. Thus data can be loaded into one partition whilst queries are being performed against other partitions.
Partitioning can improve performance as the optimizer automatically eliminates partitions that do not need to be scanned to satisfy a query and if the associated index is also partitioned, then the index scan will be much quicker as there will be less data to read.
Each partition can have its own physical storage parameters and can reside in its own tablespace allowing partitions to be stored on separate devices. This means that a failure of one device will affect only the partitions held on that device and not the whole table.
The feature for parallel queries provided by Oracle7 have been extended in Oracle8 to allow updates, inserts and deletes to be run in parallel as well. This is mainly of benefit to very large databases such as data warehouses but can also benefit OLTP systems.
The parallel execution of statements is automatically managed by the server and can be spread across multiple instances. Parallelism is achieved by one of three methods:
Oracle8 makes better use of resources than Oracle7 and is thus able to support more users and larger databases. The maximum database size has been increased to 512 petabytes, the maximum number of data files has been increased to 65 000 and the maximum number of columns per table is now 1000 all of which can be LOBS (long/long raw). Also the maximum lengths of char and varchar2 columns have been increased to 2 000 bytes and 4 000 bytes respectively.
In Oracle7 constraints are either enabled or disabled (on or off). In Oracle8 a new state has been introduced – enforced (what you might call half on). This is a useful feature for large databases or non stop operations which need to perform bulk loads of data and want to minimise the length of time for which the database is unavailable.
When a constraint is enforced, existing data is not checked for compliance, but subsequent inserts are. As enforcing a constraint does not lock the table, updates against the table can be performed immediately after the bulk load has finished.
---------------------------------------
Need to improve productivity, save time and reduce costs? Training is a highly cost-effective, proven method of boosting productivity. Click here for details of our scheduled Oracle training courses or let us know your requirements.
Looking for more Oracle tips and tricks? If you're looking for no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our ezine. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.