Partitioned tables - Objectives - Oracle Indexes and Partitioned Tables

Discussion

• Nirja Shah   -Posted on 01 Oct 15
  Heap table
  - A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap.
  
  - Data is stored in the heap without specifying an order.
  
  - Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted.
  
  - To guarantee the order of rows returned from a heap, you must use the ORDER BY clause.
  
  Partitioned tables
  - The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database.
  
  - Partitioning can make large tables and indexes more manageable and scalable.
  
  - Partitioned tables and indexes are available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
  
  Object table
  - It is a special kind of table in which each row represents an object.
  
  - Example of Creating the person_obj_table Object Table
  
  CREATE TABLE person_obj_table OF person_typ;
  
  - You can view this table in two ways:
  
  1. As a single-column table in which each row is a person_typ object, allowing you to perform object-oriented operations
  
  2. As a multi-column table in which each attribute of the object type person_typ; such as idno, name, and phone; occupies a column, allowing you to perform relational operations
  
  Index-organized table
  - It has a storage organization that is a variant of a primary B-tree.
  
  - Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner.
  
  - Each leaf block in the index structure stores both the key and nonkey columns.
  
  - Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability.
  
  - Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize.
  
  - Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated.

➨ Post your comment / Share knowledge