Index Organized Tables

INDEX ORGANIZED TABLES


Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure.Primarily used with high-updates tables, Oracle Index Organized tables structure reduces table fragmentation.An index-organized table must have a primary key. Oracle index-organized tables are best suited for use with queries based on primary key values.

Disadvantages of IOT

• It takes longer to insert into the IOT
• You don't have good secondary index capabilities

It takes longer to put the data away in the IOT (mostly regardless of the arrival order) but it is retrieved much faster with less resources (assuming you have something like the stock quotes or document management system examples) .It is best to use an IOT when you only query the table's contents by specifying the primary key in the WHERE clause.If you query other, non-PK columns in the WHERE clause, you may find the IOT will slow down your performance.

As there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.All non-key columns beyond the column specified in the INCLUDING clause are stored in the overflow segment.


Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

• Key compression
• Overflow storage area and specific column placement
• Secondary indexes, including bitmap indexes.

Scalar Sub Query Caching

SCALAR SUB QUERY CACHING


A subquery in the FROM clause is called an inline view.A subquery in the WHERE clause is called a nested subquery.

The SQL WITH clause only works on Oracle 9i release 2 and beyond.
Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.


What exactly is a scalar subquery? 

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

The Scalar Sub Query Caching helps in reducing the number of times plsql Functions are called from a SQL. So there occurs a context switching between the SQL Engine and the PLSQL Engine. Context Switching is one the reasons for Performance Hindrance.

But Scalar SubQueries involving PLSQL Functions requires the PLSQL Function to be deterministic so that the PLSQL Functions always returns the same output for the same input.

SUB QUERY FACTORING


Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is executed only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

SUBQUERY FACTORING AND DML


Subquery factoring is part of the SELECT statement itself, which means it can be used anywhere a SELECT is used. For example, the following statement formats are all valid.

INSERT INTO table_name
WITH subquery_name AS (
        SELECT ...
        FROM   ...
        )
SELECT ...
FROM   subquery_name;

UPDATE table_name
SET    column_name = ( WITH subquery_name AS (
                               SELECT ...
                               FROM   ...
                               )
                       SELECT ...
                       FROM   subquery_name );

DELETE
FROM   table_name
WHERE  column_name IN ( WITH subquery_name AS (
                                SELECT ...
                                FROM   ...
                                )
                        SELECT ...
                        FROM   subquery_name );

INSERT INTO table_name
VALUES ( (WITH subquery_name AS (
                  SELECT ...
                  FROM   ...
                  )
          SELECT ...
          FROM   subquery_name) );


         
SUB QUERIES CAN BE INCLUDED IN VIEWS but there is a small "gotcha". Some DDL-generators wrap the view's SQL in parentheses and this raises an exception when subqueries are used, as follows.

SQL> CREATE VIEW view_with_subquery
  2  AS
  3     (
  4      WITH subquery_name AS (
  5         SELECT SYSDATE AS date_column
  6         FROM   dual
  7         )
  8      SELECT date_column
  9      FROM   subquery_name
 10     );
   )
   *
ERROR at line 10:
ORA-32034: unsupported use of WITH clause
The correct way to code this is to remove the outer parentheses from the SQL, as follows.

SQL> CREATE OR REPLACE VIEW view_with_subquery
  2  AS
  3     WITH subquery_name AS (
  4        SELECT SYSDATE AS date_column
  5        FROM   dual
  6        )
  7     SELECT date_column
  8     FROM   subquery_name;

View created.


http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

The above link gives info on the Scalar Sub query Caching in Oracle Explained by Tom Kyte.

Avoid Sorting

AVOID SORTING


Disk Sorting Excessive I/O which severs as the main purpose for Performance Hindrance when i do a sort via the Order by clause and which is not able to fit inside the sort_Area_size allocated in the PGA.Sorting starts in Oracle PGA RAM (defined by the limits of sort_area_size and pga_aggregate_target 5% session limit), and a "disk sort" is invoked when the sort exceeds the maximum PGA allowed for the session.

A disk sort can far outweigh the unwanted effects of regular disk I/O read activity because a disk sort involves both physical reads and physical writes. First, Oracle must perform physical writes to a session's temporary tablespace for the sort activity it cannot handle in memory. Then, the database must turn right around and read that information back from disk to build the result set requested by the SQL query. So in essence, it's a double whammy especially for large result sets that are the product of heavy sort requests.

Oracle states that "The new sort algorithm shows more performance improvement for in-memory sorts.".Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources.For sorting in Oracle 10g release 2 there is a hidden parameter called "_newsort_enabled" that turns-on the new sorting algorithm.
We also see that in-memory sorts are CPU-intensive, and that faster processors will also improve in-memory sort performance.

Disk operations are about 10,000 times slower than a row's access in the data buffers.

If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.


In large active tables with a large number of index scans, row re-sequencing can triple the performance of queries.

Also, excessive disk sorting will cause a high value for free buffer waits, paging other tasks' data blocks out of the buffer.

Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.


SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_SIZE's are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over.

Oracle will always use the cheapest method for sequencing a result set, and the optimizer will use index retrieval (extracting the rows in sorted order) if it consumes fewer resources than a back-end sort. Remember, a sort that cannot fit into RAM will have to be done in the TEMP tablespace, very slow with lots of disk I/O.

As a general rule, the sort_area_size should be large enough that only index creation and ORDER BY clauses using functions should be allowed to use a disk sort.

A Query on the V$sysstat gives info on the sorts performed both in memory and disk sorts and also the number of rows that was sorted.

In general, Oracle sorting occurs under the following circumstances:
 

• SQL using the ORDER BY clause
• SQL using the GROUP BY clause
• When an index is created
• When a MERGE SORT is invoked by the SQL optimizer because inadequate indexes exist for a table join

Other SQL operations that might require disk sorting are:
 

• ANALYZE
• Select DISTINCT
• UNION  causes a SORT to eliminate the duplicates whereas UNIONALL Does not
• INTERSECT
• MINUS
• Sort-Merge joins.

IN MEMORY , ONE PASS AND MULTI PASS SORT

Explanation by Tom Kyte

In memory sort Everything fits in memory, no disk is needed, everything done in ram.

one pass sort We read some amount of data and sort it, the sort work area fills up, we write that to disk. We do it again (read more, sort it) - this too spills to disk. We do this over and over until we've read the entire set of data and sorted each of the chunks. Now we need read a bit of each of the chunks we've sorted on disk and start returning data from there. In the one pass sort, we can read a bit of each chunk and start returning data. If we cannot read a bit of EVERY chunk then....

we are in the realm of the multi-pass sort. We read and merge the first few chunks - resulting in a new set. We read and merge another set of chunks - resulting in a new set. And so on - until we've processed all of the output from the first pass and created a "second pass" of more merged (more sorted) data. Then we merge those in the sort area and start returning data.

Row Chaining

ROW CHAINING

The PCTFREE parameter is used to specify the amount of free space on a data block to reserve for future row expansion. If PCTFREE is set improperly, SQL update statements can cause a huge amount of row fragmentation and chaining.

Improper settings for PCTUSED (e.g., set too small) can cause huge degradations in the performance of SQL insert statements. If a data block is not largely empty, excessive I/O will happen during SQL inserts because the reused Oracle data blocks will become full quickly. Taken to the extreme, improper settings for PCTUSED can create a situation where the free space on the data block is smaller than the average row length for the table. In these cases, Oracle will try five times to fetch a block from the freelist chain. After five attempts, Oracle will raise the high-water mark for the table and grab five fresh data blocks for the insert.

With a true "chained row", part of the DATA of a row is on one block and part of it is on another block.  So, instead of just having a forwarding address on one block -- and the data on another (thats a migrated row), we have data on two or more blocks....

In the case of Row Chaining, Oracle may hinder performance issues only when the requested data is not present in the current block and Oracle has to fetch the requested data from the chained block.

select * from chained_rows  gives information on the chained rows. Even DBA_Tables gives information on chained_cnt.

Any table with a long/long raw will have chained rows.When a table has more than 255 columns, rows that have data after the 255th column are likely to
be chained within the same block. This is called intra-block chaining.With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.


ROW MIGRATION

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the "forwarding address".  So, the original block just has the rowid of the new block and the entire row is moved.

When we FULL SCAN a table, we actually *ignore* the forwarding addresses (the head row piece we call it for a row).  We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there.  Hence, in a full scan migrated rows don't cause us to really do any extra work -- they are meaningless.  Oh sure, the forwarding address is consuming a couple of bytes on the block -- it is overhead -- but frankly, it is meaningless.

When we INDEX READ into a table -- then a migrated row will cause additional IO's.  That is because the index will tell us "goto file X, block Y, slot Z to find this row".  But when we get there we find a message that says "well, really goto file A, block B, slot C to find this row".  We have to do another IO (logical or physical) to find the row.

HOW TO IDENTIFY ROW CHAINING AND ROW MIGRATION

we can detect the Migrated or chained rows in a TABLE or CLUSTER by using the following methods.

Using ANALYZE,REPORT.TXT and V$VIEWS.

Analyze

Before doing this Analyze create the table that can hold chained rows.Execute UTLCHAIN.SQL script found in the ($ORACLE_HOME)/rdbms/admin directory, this can be run to create the CHAINED_ROWS table or else create a similar table with same column and datatypes and sizes as the CHAINED_ROWS table.

SQL>ANALYZE TABLE SCHEMA_NAME.TABLE_NAME LIST CHAINED ROWS;

SQL>SELECT owner_name,table_name,head_rowid from chained_rows where TABLE_NAME = 'YOUR_TABLE_NAME';

REPORT.TXT and V$VIEWS

The Migrated or chained rows can be detected by checking the "table fetch continued row" statistic in V$SYSSTAT or in REPORT.TXT.

Note:REPORT.TXT is a file created during UTLBSTAT and UTLESTAT analyze.

HOW TO ELIMINATE MIGRATED AND CHAINED ROWS

It is VERY important to understand the distinction between migrated and chained rows. Row chaining is UNAVOIDABLE but row migration can be MANAGED and resolved through reorganization.

Note:Chaining if its going to be in all Tables then it becomes design issue and properly the DBlock size is done.

Else to avoid row chaining for very few occasions if you increase the DBlock size the effects of it as follows.

For Eg:

Space in the Buffer cache will be wasted if you are doing random access to small rows and have a large block size .For Eg an Block size of 8 KB and a 50 byte row size the wasted space will be 7950 bytes in the buffer cache in an random access.

The steps involved are:

1)Analyze the table ....list chained rows
2)Copy the rows to another table
3)Delete the rows from the original table
4)insert the rows from step 2 back to original table.

Step 4 eliminates the Migrated rows because Migration only occurs during an UPDATE.

QUERY TO DETECT TABLES WITH MIGRATED OR CHAINED ROWS


select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

ASSM

AUTOMATIC SEGMENT SPACE MANAGEMENT

Prior to Oracle 9i, each block had to be read so the freelist could be checked to see if there was room in the block. In 9i, the bitmap can be checked reducing the number of blocks read unnecessarily.

Starting from Oracle9i, the PCTUSED, FREELISTS, and FREELIST GROUPS parameters are ignored with locally managed tablespaces and ASSM.The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

In an Locally Managed Tablespace, Oracle moves the tablespace information out of the data dictionary tablespace and stores it directly within the tablespace itself. The second major tablespace enhancement in Oracle9i, was automatic segment space management (ASSM). a.k.a. bitmap freelists.   With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals.

Automatic free space management is only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bit mapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

With ASSM, the linked-list freelists are replaced with bitmaps, a binary array that turns out to be very fast and efficient for managing storage extents and free blocks, thereby improving segment storage internals. Usage of ASSM causes some performance hindrance but this has been certainly overcome with the 11G Release 2.ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.

Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction.  When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time.

With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups.Segment space management auto is a good method for handling objects with varying row sizes.

Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes. You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.

Only locally managed tablespaces can use bitmap segment management.

FREELIST
 

This is a list of blocks kept in the segment header that may be used for new rows being inserted into a table. When an insert is being done, Oracle gets the next block on the freelist and uses it for the insert. When multiple inserts are requested from multiple processes, there is the potential for a high level of contention since the multiple processes will be getting the same block from the freelist, until it is full, and inserting into it. Depending on how much contention you can live with, you need to determine how many freelists you need so that the multiple processes can access their own freelist.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

The package DBMS_SPACE contains a procedure called SPACE_USAGE that gives information about how space is being used within blocks under the segment high water mark.

LOCALLY MANAGED TABLESPACES

Movement of the space management out of the data dictionary and into the tablespace have two benefits.  First, the tablespace become independent and can be transportable (transportable tablespaces).  Second, locally managed tablespaces remove the O/O contention away from the SYS tablespace.

Locally managed tablespaces with ASSM ignore any specified values for PCTUSED, NEXT, and FREELISTS specified in the table definition.

DB Links

DATABASE LINKS

The Use of DB Links cause a performance degradation becasue of the Network Lag in pushing the huge amount of data from the remote site to the Local site. So the performance of the Query depends upon the activity which is carried out on the data and also on the amount of data which is carried out on the remote site.

The driving site hint forces query execution to be done at a different site than the initiating instance. If your SQL performs a sort, be aware that the sort will be performed on the LOCAL database.In distributed databases when a table has been partitioned into separate instances, a parallel query can be invoked to read the remote tables simultaneously.

Partitioning

PARTITIONING

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. 
• When the contents of a table need to be distributed across different types of storage devices.

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index. 
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

PARTITIONING FOR PERFORMANCE


By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. These features include:

• Partition Pruning
• Partition-Wise Joins

Oracle partition pruning only accesses those data blocks required by the query.Partitioning Pruning refers to the act of eliminating partitions which are not of concern and scanning only the partitions which are of concern and containing the data which is requested thereby eliminating the need to scan the entire table to search for the requested data and scanning only the partitions on which the data falls.Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.Storing different partitions in different tablespaces allows the database administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table.