Oracle8 VLDB Enhancements

Enhancements to Oracle 8 to improve the performance of VLDBs include table and index partitioning, parallel dml, increased size limits and enforced constraints. Partitioning 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 have a dramatic impact on performance of your Oracle database 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.

Parallel Data Manipulation

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: • By rowid – for parallel queries only, provides parallelism within a partition • By slave processes – for parallel INSERT and SELECT for non-partitioned tables • By partition – for parallel insert, update and delete operations on partitioned tables

Database Size Limits

Oracle8 makes more efficient 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 2000 bytes and 4000 bytes respectively.

Enforced Constraints

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. See the Oracle8 new features overview for a summary of other new features. ----------------------------------------------------------- Looking to sky-rocket productivity, slash costs and accelerate innovation? Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our training course schedule, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.