Table Partitioning an Oracle table - Tips & Tricks

We will discuss various table partition concepts with examples with some fundamental concepts. We ll cover topics:

A) Partitioning Concepts
B) When to Partition a Table
C) When to Partition an Index
D) Oracle Interval Partitioning Tips
E) About Interval Partitioning
F) Query performance with partitioning 
G) Index partitioning with Oracle
H) Basic Partitioning Strategies On Tables
J) Partition Advisor :

A) Partitioning Concepts:

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.

Partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster.  Databases become unmanageable, and serious problems occur:

• Files recovery takes days, not minutes
• Rebuilding indexes (important to re-claim space and improve performance) can take days
• Queries with full-table scans take hours to complete
• Index range scans become inefficient

Advantages:

As per Oracle Documentation, Partitioning offers following advantages:

• Increased availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
• Easier administration of schema objects reducing the impact of scheduled downtime for maintenance operations.
• Reduced contention for shared resources in OLTP systems
• Enhanced query performance: Often the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

B) When to Partition a Table:

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.

C) When to Partition an Index :

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.

D) Oracle Interval Partitioning Tips:

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.

Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition.

Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result "ORA-14400: inserted partition key does not map to any partition"

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem.

E) About Interval Partitioning:

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

 Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using :

SQL> alter table <table_name> set interval(expr).

Interval Partitioning: Introduced in 11g, interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition is made persistent. The additional segments are allocated as the data arrives. The additional partitions and local indexes are automatically created.

Click Here to read more interval partitioning

F) Query performance with partitioning :

The Oracle engine can take advantage of the physical segregation of table and index partitions in several ways:

>> Disk load balancing —Table and index partitioning allows the Oracle data warehouse DBA to segregate portions of very large tables and indexes onto separate disk devices, thereby improving disk I/O throughput and ensuring maximum performance.

>> Improved query speed —The Oracle optimizer can detect the values within each partition and access only those partitions that are necessary to service the query. Since each partition can be defined with its own storage parameters, the Oracle SQL optimizer may choose a different optimization plan for each partition.

>> Faster parallel query —The partitioning of objects also greatly improves the performance of parallel query. When Oracle detects that a query is going to span several partitions, such as a full-table scan, it can fire off parallel processes. Each of processes will independently retrieve data from each partition. This feature is especially important for indexes, since parallel queries don't need to share a single index when servicing a parallel query.

>> Partitioning Pruning --  Partitioning pruning ( Partition elimination) is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude.

For example,
suppose an application contains an TRANX_ORDERS table containing an historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the TRANX_ORDERS table. If the table had 2 years of historical data, this query would access one partition instead of 104 partitions. This query could potentially execute 100x faster simply because of partition pruning. 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.

>> Partition-wise Joins --  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 at least one of these tables is partitioned on the join key. Partition-wise joins break a large join into smaller joins of 'identical' data sets for the joined tables. 'Identical' here is defined as covering exactly the same set of partitioning key values on both sides of the join, thus ensuring that only a join of these 'identical' data sets will produce a result and that other data sets do not have to be considered. Oracle is using either the fact of already (physical) equi-partitioned tables for the join or is transparently redistributing (= “repartitioning”) one table at runtime to create equi-partitioned data sets matching the partitioning of the other table, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

G) Index partitioning with Oracle:

The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.

The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.

a) Local partitioned indexes:

Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table. In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.

CREATE INDEX idx_year_txorder ON tranx_order (order_date) LOCAL;

OR

CREATE INDEX year_idx
on tranx_order (order_date)
LOCAL
(PARTITION tranx_order_p1 TABLESPACE tbs1,
PARTITION tranx_order_p2 TABLESPACE tbs2,
PARTITION tranx_order_p3 TABLESPACE tbs3);

Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on tranx_order, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.

b) Global partitioned indexes

A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.

For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.

Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:

SQL> CREATE INDEX idx_item_tranx
on tranx (item_nbr)
GLOBAL
(PARTITION idx_item_tranx1 VALUES LESS THAN (1000),
PARTITION idx_item_tranx2 VALUES LESS THAN (2000),
PARTITION idx_item_tranx3 VALUES LESS THAN (3000),
PARTITION idx_item_tranx4 VALUES LESS THAN (4000),
PARTITION idx_item_tranx5 VALUES LESS THAN (5000));

Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.

H) Basic Partitioning Strategies On Tables:

Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is

placed into individual partitions:

•  Range
•  Hash
•  List

a) Range Partitioning

Range partitioning was the first partitioning method supported by Oracle in Oracle 8. Range partitioning was probably the first partition method because data normally has some sort of logical range. For example, business transactions can be partitioned by various versions of date (start date, transaction date, close date, or date of payment). Range partitioning can also be performed on part numbers, serial numbers or any other ranges that can be discovered.

Examples-1: Using any column as range
CREATE TABLE employees
(
empid number(10) NOT NULL,
empname VARCHAR(30),
hired DATE DEFAULT SYSDATE,
job_code number(5) NOT NULL,
store_id number(2) NOT NULL,
CONSTRAINT empid_pk PRIMARY KEY (empid)
)
PARTITION BY RANGE(store_id)
(
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

In this partitioning scheme, all rows corresponding to employees working at stores 1 through 5 are stored in partition p0, to those employed at stores 6 through 10 are stored in partition p1, and so on. Note that each partition is defined in order, from lowest to highest. This is a requirement of the PARTITION BY RANGE syntax; One can think of it as being analogous to a series of if ... elseif ... statements in programming language in this regard.

Example-2: using year as range

Partition the table by RANGE, and for the partitioning expression, employ a function operating on a DATE, TIME, or DATETIME column and returning an integer value, as shown here:

SQL>
CREATE TABLE SALES_PART
(TIME_ID    NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION p_first VALUES LESS THAN ('01-APR-2014'));

-- Set interval partitioning
SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'month'));
OR
SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'year'));

Example: 3 : using year as range with adding interval partition 

SQL> CREATE TABLE SALES_PART
(TIME_ID    NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION p_first VALUES LESS THAN ('01-APR-2014'));

SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));
-- Will be Inserted to first partition
insert into SALES_PART values (1,'01-JAN-2013',10,10);
insert into SALES_PART values (1,'01-JAN-2013',10,10);
insert into SALES_PART values (1,'01-FEB-2014',10,10);
insert into SALES_PART values (1,'01-MAR-2014',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-APR-2014',10,10);
insert into SALES_PART values (1,'01-MAY-2014',10,10);
insert into SALES_PART values (1,'01-JAN-2015',10,10);
insert into SALES_PART values (1,'01-DEC-2015',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-FEB-2015',10,10);
insert into SALES_PART values (1,'01-JAN-2016',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-DEC-2016',10,10);

-- after Verification
select count(1) from SALES_PART partition(P_FIRST); -- 4
select count(1) from SALES_PART partition(SYS_P46); -- 4
select count(1) from SALES_PART partition(SYS_P47); -- 2
select count(1) from SALES_PART partition(SYS_P48); -- 1

select count(1) from SALES_PART partition(SYS_P48); -- 1
select count(1) from SALES_PART partition(SYS_P44); -- 1
select count(1) from SALES_PART partition(SYS_P45); -- 1

Example:4: Creating partitions in different table-spaces:

CREATE TABLE emp_dumy
(
empid number(10) NOT NULL,
empname VARCHAR(30),
hired DATE DEFAULT SYSDATE,
job_code number(5) NOT NULL,
store_id number(2) NOT NULL,
CONSTRAINT empid_pk PRIMARY KEY (empid)
)
PARTITION BY RANGE(store_id)
(
    PARTITION p0 VALUES LESS THAN (6) tablespace tbs1,
    PARTITION p1 VALUES LESS THAN (11) tablespace tbs2,
    PARTITION p2 VALUES LESS THAN (16) tablespace tbs3,
    PARTITION p3 VALUES LESS THAN (21) tablespace tbs4
);

b) Hash Partitioning

Oracle's hash partitioning distributes data by applying a proprietary hashing algorithm to the partition key and then assigning the data to the appropriate partition. By using hash partitioning, DBAs can partition data that may not have any logical ranges. Also, DBAs do not have to know anything about the actual data itself. Oracle handles all of the distribution of data once the partition key is identified.

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause.

Example-1: 

The following examples illustrate two methods of creating a hash-partitioned table named dept. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 16;

-- All partitions will be created in HR tablespace(assume).

OR
In the following example, names of individual partitions, and tablespaces in which they are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions inherit this attribute.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE HR1, PARTITION p2 TABLESPACE HR1,
PARTITION p3 TABLESPACE HR2, PARTITION p4 TABLESPACE HR3);

-- To store in diff disk, assume tablespaces created in diff. diskgroup.

*Creating a Hash-Partitioned Global Index:

Hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the following statement creates a hash-partitioned global index:

Example : Creating a hash-partitioned global index

CREATE INDEX hg_idx_tab ON tab (c1,c2,c3) GLOBAL
     PARTITION BY HASH (c1,c2)
     (PARTITION p1  TABLESPACE tbs1,
      PARTITION p2  TABLESPACE tbs2,
      PARTITION p3  TABLESPACE tbs3,
      PARTITION p4  TABLESPACE tbs4);

c) List Partitioning

List partitioning was added as a partitioning method in Oracle 9i, Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g.. business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. List partitioning gives the DBA the ability to group together seemingly unrelated data into a specific partition.

Example-1: 
CREATE TABLE dept
(deptno number not null,
 deptname varchar2(20),
 state varchar2(50),
 locationid number(5))
 PARTITION BY LIST (locationid)
 (PARTITION loc1 VALUES (10101,10102),
 PARTITION loc2 VALUES (10103, 10104),
 PARTITION loc3 VALUES  (10105,10106, 10107),
 PARTITION loc4 VALUES (10108, 10109,10110),
  PARTITION loc5 VALUES (10111, 10112)) tablespace HR;

-- insert the values
insert into dept(deptno,deptname,state,locationid) values (1,'accounts','TN',10101);
insert into dept(deptno,deptname,state,locationid) values (2,'finance','AP',10103);
insert into dept(deptno,deptname,state,locationid) values (3,'human resource','UP',10109);
insert into dept(deptno,deptname,state,locationid) values (4,'operations','TN',10102);

-- Verify the records in partition
select count(1) from dept partition(loc1);  --output will be 2
select count(1) from dept partition(loc2);  --output will be 1
select count(1) from dept partition(loc3);  --output will be 1

I) Interval Partitioning: ( Oracle11g new feature)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

J) Partition Advisor :

The SQL Access Advisor in Oracle Database 11g has been enhanced to generate partitioning recommendations, in addition to the ones it already provides for indexes, materialized views and materialized view logs. Recommendations generated by the SQL Access Advisor – either for Partitioning only or holistically - will show the anticipated performance gains that will result if they are implemented. The generated script can either be implemented manually or submitted onto a queue within Oracle Enterprise Manager.

With the extension of partitioning advice, customers not only can get recommendation specifically for partitioning but also a more comprehensive holistic recommendation of SQL Access Advisor, improving the collective performance of SQL statements overall.

The Partition Advisor, integrated into the SQL Access Advisor, is part of Oracle's
Tuning Pack, an extra license option. It can be used from within Enterprise Manager or via a command line interface.

Click here to read more from oracle-base site.

In short about partition:

Partitioning is for all applications. Oracle Partitioning can greatly enhance the manageability, performance, and availability of almost any database application. Partitioning can be applied to cutting-edge applications and indeed partitioning can be a crucial technology ingredient to ensure these applications’ success. Partitioning can also be applied to more common place database applications in order to simplify the administration and costs of managing such applications. Since partitioning is transparent to the application, it can be easily implemented because no costly and time-consuming application changes are required. This is a pricing option with licence.

Manage Table Partitions in Oracle 11g

About Table Partition:
Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Advantages:
1) Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
2) Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
3) Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.
4) Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
5) Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
6) Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.
7) No. archival required for your table in case of big table and less use of old records.

Note: It is a pricing option with Oracle. Be sure before use. Again it is an enterprise option only.

Useful queries related to table-partition:

-- find all partitions count

select table_owner,
  table_name,
  partition_name,
  segment_created,
  high_value 
from all_tab_partitions
where table_owner in('HR','CRM','PAYROLL','XYZ')
    

-- drop partition (If a partition not in use / wrongly high value given but don't have data in it)
SQL> alter table HR.EMP drop partition EMP_PART_1;

-- find max value of primary key column
select max(EMPid) from HR.EMP # If used sequence based pk

-- find no. of records of a parttion
select count(*) 
from HR.EMP partition(EMP_PART_2)

-- add new partition
alter table HR.EMP 
add PARTITION EMP_PART_3 values less than (1000000)

--  alter table to add new range partition dynamically ( New feature in Oracle 11g onwards)

alter table HR.EMP  set interval (100000);

Click here to read more about Table and Index partitioning

Compress table/ table-partition / tablespace using Oracle 11g

DBMS_COMPRESSION Example

With starting Oracle 11g compression concept, this question may arise that, how to estimate the size of tables after compression. In addition to my previous posting that, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example.

Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on)  also to get an estimation about that, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.

-- Create table

create table hr.bigtab as
select * from dba_objects;  

Note : you can append same data for 100 or 1000 times with a loop to create actually a big table.

-- Check the tablespace

select * from all_tables where table_name='BIGTAB' 

-- Can be moved to any tablespace if created in SYSTEM.

alter table BIGTAB move tablespace HR_TBLSPC;

-- Gather table stats 

exec dbms_stats.gather_table_stats('HR','BIGTAB');

-- Now compress the table

alter table owner.table_name compress | nocompress;
OR
ALTER TABLE owner.table_name 
MODIFY PARTITION partition_name COMPRESS FOR ALL OPERATIONS;

alter table hr.bigtab compress;

-- Find compression statistics

set serveroutput on
declare
 v_blkcnt_cmp     pls_integer;
 v_blkcnt_uncmp   pls_integer;
 v_row_cmp        pls_integer;
 v_row_uncmp      pls_integer;
 v_cmp_ratio      number;
 v_comptype_str   varchar2(60);
begin
 dbms_compression.get_compression_ratio(
 scratchtbsname   => upper('HR_TBLSPC'),
 ownname          => 'HR',
 tabname          => upper('BIGTAB'),
 partname         => NULL,
 comptype         => dbms_compression.comp_for_query_high,
 blkcnt_cmp       => v_blkcnt_cmp,
 blkcnt_uncmp     => v_blkcnt_uncmp,
 row_cmp          => v_row_cmp,
 row_uncmp        => v_row_uncmp,
 cmp_ratio        => v_cmp_ratio,
 comptype_str     => v_comptype_str);
 dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
 dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
 dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/

output:

Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Estimated Compression Ratio: 12.3
Blocks used by compressed sample: 1199
Blocks used by uncompressed sample: 14842

PL/SQL procedure successfully completed

Compress on Partitioned Table:

The following examples show the various compression options applied at table and partition level.

-- Table compression.
CREATE TABLE sample_tab_1 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
COMPRESS FOR ALL OPERATIONS;

-- Partition-level compression.
CREATE TABLE sample_tab_2 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
PARTITION BY RANGE (created_date) (
  PARTITION sample_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) COMPRESS,
  PARTITION sample_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2014', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION sample_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2014', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
  PARTITION sample_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);Table-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TABLES views.

SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
sample_TAB_1                     ENABLED  FOR ALL OPERATIONS
sample_TAB_2

2 rows selected.

SQL>Tables defined with partition-level compression and no table-level compression display NULL values in these columns.

Partition-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TAB_PARTITIONS views.

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;

output:

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR

------------------------------ ------------------------------ -------- ------------------

sample_TAB_2                     sample_TAB_Q1                    ENABLED  DIRECT LOAD ONLY

sample_TAB_2                     sample_TAB_Q2                    ENABLED  DIRECT LOAD ONLY

sample_TAB_2                     sample_TAB_Q3                    ENABLED  FOR ALL OPERATIONS

sample_TAB_2                     sample_TAB_Q4                    DISABLED

4 rows selected.

SQL>

Tablespace compression:

Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.

CREATE TABLESPACE test_tbs
  DATAFILE '/u02/oradata/datafiles/prod/test_ts01.dbf'
  SIZE 1M
  DEFAULT COMPRESS FOR ALL OPERATIONS;

SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TBS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------
ENABLED  FOR ALL OPERATIONS

1 row selected.

SQL>

ALTER TABLESPACE test_tbs DEFAULT NOCOMPRESS;

SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TBS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED

1 row selected.

SQL>

DROP TABLESPACE test_tbs INCLUDING CONTENTS AND DATAFILES;

When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.

The restrictions associated with table compression include:

• Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
• Compressed tables must not have more than 255 columns.
• Compression is not applied to lob segments.
• Table compression is only valid for heap organized tables, not index organized tables.
• The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
• Table compression cannot be specified for external or clustered tables.

Compression Advisor:

Overview:

Compression Advisor provides an estimate of the compression ratio that can be realized through the use of the Oracle Advanced Compression option. This estimate is based on analysis of a sample of data and provides a good estimate of the actual results you may obtain once you implement the OLTP Table compression feature in your environment.

The Compression Advisor PL/SQL package you use is dependent upon which Oracle Database release you currently have deployed. Those customers that want to use Compression Advisor with Oracle Database 9i Release 2 through Oracle Database 11g Release 1 will use the DBMS_COMP_ADVISOR package available for download below. Customers that want to use Compression Advisor with Oracle Database 11g Release 2 through Oracle Database 12c will use the DBMS_COMPRESSION package that is included with the database.

This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1. A compression advisor (DBMS_COMPRESSION) is included with Oracle Database 11g Release 2 and Oracle Database 12c.

Using Compression Advisor:

This procedure can be used with Oracle Database 9i Release 2 through Oracle Database 11g Release 1. Running this procedure will create tables in the default tablespace of the user running the procedure. While these tables will get dropped at the end of the procedure they will consume space while the procedure runs. Oracle recommends creating a tablespace specifically for storing these tables and assigning it as the default tablespace to the user running the procedure. The DBMS_COMP_ADVISOR advisor package is only available as a free download.

Compression Advisor consists of the DBMS_COMP_ADVISOR package containing the following procedure:

 getratio(
    ownername            IN     varchar2,
    tabname                IN     varchar2,
    sampling_percent   IN     number
  );

where

- 'ownername' is the schema that the table belongs to
- 'tabname' is name of the table for which compression ratio is to be estimated
- 'sampling_percent' is any value between 0.000001 and 99

The output of this procedure is the estimated compression ratio.

Example:

SQL>  set serveroutput on
SQL>  exec dbms_comp_advisor.getratio('SH','SALES',10);

Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression option: 2.96

PL/SQL procedure successfully completed.

Note:  Compression Advisor concepts collected from Oracle magazine.

SQL & PL/SQL Performance Tuning – for Beginners

-- Performance Tuning – for Beginners

-- Using Oracle SQL & PL/SQL

1) Introduction:

Performance tuning is a major part in a database, as well as database maintenance. As the transaction data increases performance degrades, PL/SQL enhancements becomes mandatory for a database. Any Online transaction processing(OLTP) systems have both real time and MIS related reports along with huge transactions. So, performance tuner has a major role in  OLTP systems. 

2) About Oracle Execution Plan:

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

3) Gather schema/ Table statistics

exec DBMS_STATS.gather_schema_stats('HR');

exec DBMS_STATS.gather_table_stats('HR','employee');

Let us discuss rules on performance tuning. See the examples wherever given.

1 : Avoid Using * in SELECT Clauses

 The dynamic SQL column reference (*) gives you a way to refer to all of the columns of a table. Do not use the * feature because it is very inefficient -- the * has to be converted to each column in turn. The SQL parser handles all the field references by obtaining the names of valid columns from the data dictionary and substitutes them on the command line, which is time consuming.

2: Reduce the Number of Trips to the Database

Every time a SQL statement is executed, ORACLE needs to perform many internal processing steps; the statement needs to be parsed, indexes evaluated, variables bound, and data blocks read. The more you can reduce the number of database accesses, the more overhead you can save.

For example:

There are 3 distinct ways of retrieving data about employees who have employee numbers 0342 or 0291.

 Method 1 (Least Efficient) :

SELECT EMP_NAME, SALARY, GRADE

FROM EMP
WHERE EMP_NO = 0342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP
WHERE EMP_NO = 0291;

Method 2 (Most Efficient) :

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 0342 OR EMP_NO = 0291;

         

Note: One simple way to increase the number of rows of data you can fetch with one database access and thus reduce the number of physical calls needed is to reset the ARRAYSIZE parameter in SQL*Plus, Suggested value is 200.

3: Use TRUNCATE instead of DELETE

 When rows are removed from a table, under normal circumstances, the rollback segments are used to hold undo information; if you do not commit your transaction, Oracle restores the data to the state it was in before your transaction started.

With TRUNCATE, no undo information is generated. Once the table is truncated, the data cannot be recovered back. It is faster and needs fewer resources.

Use TRUNCATE rather than DELETE for wiping the contents of small or large tables when you need no undo information generated.

4: Counting Rows from Tables

 Contrary to popular belief, COUNT(*) is faster than COUNT(1). If the rows are being returned via an index, counting the indexed column – for example, COUNT(EMPNO) is faster still.  Use count(rowid) when there is no indexed column or primary key column.

5: Minimize Table Lookups in a Query

To improve performance, minimize the number of table lookups in queries, particularly if your statements include sub-query SELECTs or multi-column UPDATEs.

For example:

Least Efficient :    

SELECT         TAB_NAME

FROM            TABLES

WHERE TAB_NAME = (SELECT      TAB_NAME

                                          FROM           TAB_COLUMNS

                                          WHERE        VERSION = 604)

AND              DB_VER = (SELECT     DB_VER

                                            FROM         TAB_COLUMNS

                                            WHERE      VERSION = 604)

               

Most Efficient :

SELECT         TAB_NAME

FROM            TABLES

WHERE (TAB_NAME, DB_VER) = (SELECT    TAB_NAME, DB_VER

                                                              FROM       TAB_COLUMNS

                                                             WHERE    VERSION = 604)

Multi-column UPDATE example:

Least Efficient :

UPDATE     EMP

SET              EMP_CAT = (SELECT MAX(CATEGORY)

                                             FROM    EMP_CATEGORIES),

SAL_RANGE = (SELECT MAX(SAL_RANGE)

    FROM    EMP_CATEGORIES )

                           WHERE EMP_DEPT  = 0020;

Most Efficient :

UPDATE      EMP

SET              (EMP_CAT, SAL_RANGE) =

(SELECT     MAX(CATEGORY), MAX(SAL_RANGE)

 FROM         EMP_CATEGORIES)

WHERE       EMP_DEPT = 0020;

6 : Use Table Aliases

Always use table aliases & prefix all column names by their aliases where there is more than one table involved in a query. This will reduce parse time & prevent syntax errors from occurring when ambiguously named columns are added later on.

7: Use EXISTS in Place of DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries used to determine information at the owner end of a one-to-many relationship (e.g. departments that have many employees).

For example:

Least Efficient :

SELECT         DISTINCT DEPT_NO, DEPT_NAME

FROM            DEPT D, EMP E

WHERE          D.DEPT_NO = E.DEPT_NO

Most Efficient :

SELECT         DEPT_NO, DEPT_NAME

FROM            DEPT D

WHERE EXISTS (SELECT       ‘X’

                      FROM         EMP E

                      WHERE       E.DEPT_NO = D.DEPT_NO);

EXISTS is a faster alternative because the RDBMS kernel realizes that when the sub-query has been satisfied once, the query can be terminated.

8 : Avoid Calculations on Indexed Columns

If the indexed column is a part of a function (in the WHERE clause), the optimizer does not use an index and will perform a full-table scan instead.

Note : The SQL functions MIN and MAX are exceptions to this rule and will utilize all available indexes.

For example:

 Least Efficient :

SELECT . . .

FROM            DEPT

WHERE         SAL * 12 > 25000;

Most Efficient :

SELECT . . .

FROM            DEPT

WHERE         SAL > 25000 / 12;

9 : Exact Position of Tables & Columns

Exact Position:

When we run a query, the execution starts from last to first. The table which contains less number of records should be kept last.

                        Ex: Table1(1000 records), Table2(100)

                        Select * from table1, table2.

This will not affect the query cost wise but in long term data retrieval will be faster.

Exact position of columns:

At time of joining 2 tables master record should be right hand side of = Symbol.

 Method 1 (Least Efficient) :

Select * from master, child 
where master.columnname=child.columnname;

 Method 2 (Most Efficient) : 

Select * from master, child 
where  child.columnname=master.columnname;

>>> Here data retrial will be faster

10: Null Comparison

We should try to avoid comparing the NULL values column. Use of this kind of column will not allow the optimizer to use of Indexes present on the table.

Method -1 (Least Efficient) :

 Select rt.requestid

  FROM bookrequests rt

 where (:iv_request_id is NULL OR rt.requestid = :iv_request_id);

Method -2 (Most Efficient) :

if (:iv_request_id is not null) THEN

  Select rt.requesttestid

    FROM bookrequests rt

   where rt.requesttestid = :iv_request_id);

Elsif (:iv_request_id IS NULL) THEN

  Select rt.requesttestid FROM bookrequests rt

where rt.requestid is null);

End If;

11: Compatible datatype of columns:
At time of joining tables a due focus should be on the datatype compatibility of the columns used in Join conditions or where clauses.

Method 1 (Least Efficient) :

Sql> Select barcode, price From products Where barcode = 5467;                                                              

Method 2 (Most Efficient) :    

Sql> Select barcode, price From products Where barcode = ‘5467’;                                                           

Such conditions should not be used at all, even though if its required this may require oracle definded datatype conversion functions like To_char ,To_date etc.

• Rectification of such kind of errors will result in a lot Cost reduction.
• If  DataType Conversion is performed , Should be used on INPUT Variables not on Table Columns.

12: Inline views,Replacement of Subquery

• When performing Left outer or Right Outer join ,try to replace it by using Inline Views for small / master tables/ fact tables.

SELECT e.empno,e.ename,d.deptno,d.dname

FROM (SELECT empno, ename from emp@dblink) e, dept d;

• If the same sub query is used in multiple locations of a query, it can be replaced by making use of Functions.
• Functions will avoid great context Switch and also code will be reusable.

Summary of techniques for each type of subquery:

	Standard Subquery		Anti-join Subquery
	IN	EXISTS	NOT IN	NOT EXISTS
Correlated subquery	Redundant Boolean predicates. Can always be replaced with a standard join	Automatic Transformation to nested loop join	Rewrite as select distinct outer join	Rewrite as select distinct outer join
Non-correlated subquery	Automatic transformation to nested loop join	Never appropriate	Rewrite as nested loop join with minus operator	Never appropriate

13 : Use of Function for Left join, Subquery

Method 1 (Least Efficient) :

select e.empno, e.ename, t.trandate, t.tranamount
  from transaction t
  inner join emp e on t.createdby = e.empno
order by t.tranamount desc;

                                                                   

Method 2 (Most Efficient) :

select counterno,f_showname(t.createdby) "Ename", t.trandate, t.tranamount
  from transaction t
 order by t.tranamount desc;

Using co-related sub-query:
                                                                  

Method 1 (Least Efficient) :

Select pbn.billno

  from Publicbillno pbn

 where pbn.locationid = iv_locationid

   and pbn.billno not in

       (select pb.billno

          from Publicbill pb

         where pb.locationid = iv_locationid)

Method 2 (Most Efficient) : 
Select pbn.billno

          from Patientbillno pbn

         where pbn.locationid = iv_locationid

           and not exists (select pb.billno

                         from Publicbill pb

                        where pb.locationid = iv_locationid

                          and pb.billno = pbn.billno)

14 : Usuage of terms Exists , DECODE,CASE,LIKE

• Making use of Clauses like `EXISTS,DECODE,CASE‘ will help to reduce the cost of queries.
• Use of EXISTS in place of IN. EXISTS will give better performance when the main query returns less records
• CASE will be more effective than an IF condition.
• Avoid LIKE clause,  If it is used, it should be more specific to the requirement.
• CASE can be used in SELECT statements and  Pl/SQL block where more IF-ELSE used.

14.1) Use DECODE to Reduce Processing

 The DECODE statement provides a way to avoid having to scan the same rows repetitively or to join the same table repetitively.

 For example:

 SELECT   COUNT(rowid), SUM(SAL)

FROM  EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’;

SELECT  COUNT(rowid), SUM(SAL)

FROM            EMP

WHERE DEPT_NO = 0030

AND              ENAME LIKE ‘SMITH%’;

 You can achieve the same result much more efficiently with DECODE:

 SELECT COUNT(DECODE(DEPT_NO, 0020, ‘X’, NULL)) D0020_COUNT,

                 COUNT(DECODE(DEPT_NO, 0030, ‘X’, NULL)) D0030_COUNT,

                 SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,

                 SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL

                 FROM    EMP

     WHERE  ENAME LIKE ‘SMITH%’;

Similarly, DECODE can be used in GROUP BY or ORDER BY clause effectively.

14.2) Use EXISTS in Place of IN for Base Tables

Many base table queries have to actually join with another table to satisfy a selection criteria. In such cases, the EXISTS (or NOT EXISTS) clause is often a better choice for performance.

     

Least Efficient :

SELECT         *

FROM            EMP              (Base Table)

WHERE         EMPNO > 0

AND               DEPTNO IN (SELECT DEPTNO

                                               FROM   DEPT

                                               WHERE LOC = ‘MELB’)

Most Efficient :

SELECT         *

FROM            EMP

WHERE         EMPNO > 0

AND               EXISTS (SELECT      ‘X’

                                       FROM        DEPT

                                       WHERE     DEPTNO = EMP.DEPTNO

                                       AND          LOC = ‘MELB’)

14.3) Use NOT EXISTS in Place of NOT IN

In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge. The NOT IN clause is the all-time slowest test possible as it forces a full read of the table in the sub-query SELECT. Avoid using NOT IN clause either by replacing it with Outer Joins or with a NOT EXISTS clause as shown below:

SELECT . . .

FROM            EMP

WHERE         DEPT_NO NOT IN (SELECT   DEPT_NO

                                                          FROM    DEPT

                                                          WHERE  DEPT_CAT = ‘A’);

To improve the performance, replace this code with:

Method 1 (Efficient) :

SELECT . . .  

FROM            EMP A, DEPT B

WHERE         A.DEPT_NO = B.DEPT_NO (+)

AND               B.DEPT_NO IS NULL

AND               B.DEPT_CAT(+) = 'A'   

Method 2 (Most Efficient) :

SELECT . . .

FROM            EMP E

WHERE         NOT EXISTS (SELECT          ‘X’

                                                 FROM              DEPT

                                                 WHERE           DEPT_NO = E.DEPT_NO

                                                 AND                 DEPT_CAT = ‘A’);

14.4) Use Of Not Exist and Exist

Method 1 (Least Efficient) :

Select count(rowid)  From Products Where barcode NOT IN (Select barcode From Clothing);

Time in Secs : >500secs

Method 2 (Most Efficient) :

Select count(rowid)  From Products P  Where NOT EXISTS  (  Select C.barcode From Clothing C   Where C.barcode = P.barcode);

Method 3 (Least Efficient) : 

Time in Secs: 6.1 secs

Select prod_id,qty  From Product Where prod_id=167 and item_no IN (select item_no from items)

                                                                                          Time in Secs : >120secs

Method 4 (Most Efficient) :

Select prod_id,qty  From Product  prd Where prod_id=167 and exists  (select ‘x’ from items itm  where b.itemno=a.itemno);

                                                                                   Time in Secs: 2.0 secs

15 : Avoid NOT on Indexed Columns

In general, avoid using NOT when testing indexed columns. The NOT function has the same effect on indexed columns that functions do. When ORACLE encounters a NOT, it will choose not to use the index and will perform a full-table scan instead.

Least Efficient : (Here, index will not be used)

SELECT . . .

FROM            DEPT

WHERE         DEPT_CODE <> 0;

Most Efficient : (Here, index will be used)

SELECT . . .

FROM            DEPT

WHERE         DEPT_CODE > 0;

In a few cases, the ORACLE optimizer will automatically transform NOTs (when they are specified with other operators) to the corresponding functions:

NOT >           to       <=

NOT >=         to       <

NOT <           to       >=

NOT <=         to       >

16: Avoid IS NULL and IS NOT NULL on Indexed Columns

Avoid using any column that contains a null as a part of an index. ORACLE can never use an index to locate rows via a predicate such as IS NULL or IS NOT NULL.

In a single-column index, if the column is null, there is no entry within the index. For concatenated index, if every part of the key is null, no index entry exists. If at least one column of a concatenated index is non-null, an index entry does exist.

17: Using Global Temporary Table (GTT)

• When ever a huge data is being processed in a procedure its always better to use GTT.
• It will improve the performance as it will also reduce the overhead of data truncation as in normal table, The procedure would also contain a delete statements or else a table truncation.
• GTT will be allocated separately for multiple sessions accessing the table.
• This will also allow us to use concept like BULK COLLECT, COLLECTION

Syntax:

Create global temporary gtt_tablename

(col1  datatype, Col2 datatype, …..

) on commit preserve rows [ On commit delete rows];

Note: On commit preserve rows: All data retain in the table till the session closed but On commit delete rows delete all the rows from the GTT as soon as commit fires even if the session not closed. GTT indexes are auto managed by oracle. No water mark created when number of deletion occurs.

Examples:

Method 1 (Least Efficient) :

SELECT NVL(T.BILLID, T.TRANSACTIONID) AS TRANID   
FROM CRM.MAINTRANSACTION  T,  HR.SHIFTMAINTENANCE SM          
     WHERE T.SHIFTNO = SM.SHIFTNO AND
           TRUNC(TRANDATE) = '19-JUN-2013' AND T.FLAG = 1 
AND   T.TRANEVENT NOT IN (28) AND T.CREATEDBY = 8129
            AND T.Locationid=10201

                                                                                                   Execution Time : 300 sec

Method 2 (Most Efficient) :

INSERT INTO CRM.GT_MAINTRANSCTION SELECT * FROM CRM.MAINTRANSCTION

WHERE TRANDATE='19-JUN-2013'

SELECT NVL(T.BILLID, T.TRANSACTIONID) AS TRANID  FROM CRM.GT_MAINTRANSACTION  T,  HR.SHIFTMAINTENANCE SM          
     WHERE T.SHIFTNO = SM.SHIFTNO AND
           TRUNC(TRANDATE) = '19-JUN-2013' AND T.FLAG = 1 AND
           T.TRANEVENT NOT IN (28) AND T.CREATEDBY = 8129
            AND T.Locationid=10201

                                                                                                         Execution Time : 40 sec 

18 : Use BULK COLLECT and FORALL

When inserting rows in PL/SQL, developers often place the insert statement inside a FOR loop.  To insert 1,000 rows using the FOR loop, there would be 1,000 context switches between PL/SQL and the Oracle library cache.

Oracle allows you to do bulk binds using the PL/SQL FORALL loop, which requires only one context switch.  This is achieved by the FORALL statement passing an entire PL/SQL table to the SQL engine in a single step.  Internal tests show that this process results in substantial performance increases over traditional methods.

Clicker here to read more

Example:

Method 1 (Least Efficient) :

DECLARE

TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

    pnums  NumTab;  pnames NameTab;

BEGIN

 FOR i IN 1..20000 LOOP  -- use FOR loop

       INSERT INTO parts VALUES (pnums(i), pnames(i));

 END LOOP;

END;                                                                                     Time taken in sec: 11.0

/

Example:

Method 1 (Most Efficient) :

DECLARE

   TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

   TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

pnums  NumTab;

pnames NameTab;

BEGIN

FORALL I in 1 .. 20000 -- use FORALL

       INSERT INTO parts VALUES (pnums(i), pnames(i));

END;                                                                                                     Time Taken in Secs: 0.5 sec

/

Using  Bulk Collect

Example :

Method 1 (Least Efficient) :

Declare
   Type bcode is table of products.barcode%TYPE;
   i int;
   barc bcode;
  cursor cur_seq is
        select barcode from products where rownum<100001;

Begin
        i:=0;
        for cur_dta in cur_seq loop 
         barc:=cur_dta(i).barcode;
         i:=i+1;
        end loop;

End; 

                                                            Execution Time : 20 sec

Method 2 (Most Efficient) :

declare

    Type bcode is table of products.barcode%TYPE;

        i int;

        barc bcode;

begin

select barcode BULK COLLECT into barc from products where rownum<100001;

end;

                                                                                                                                                                           Time taken : 1.41 sec

19 : Use of Index

> Indexes helps a lot in increasing the Performance of a Query. Its like storing the address of a perticular Record.

> We can create Bitmap Index, functional Index, Normal Index based on requirement.

> If we create a Unique index on a column data should be unique  in the column.

> Oracle also provides a new concept of Invisible Index for performance monitoring of a specific query without affecting the other query referencing the Table

20: Using table partition

> Table partition is helpfull when a table contain a huge amount of data.

> It breaks the Table into smaller segments called partition thus resulting into faster data retrival.

> Partition can be Various Type. Range , Hash, List Partition

> For Partitioned table Query writing will be same as for the Normal Tables.

> We can also Go Partioned Index

> After performing the partition managment we need to gather the Statistics

> No Special Query Writing method for Partiton Tables..

CREATE TABLE Emp    ( Emp_no NUMBER,       Emp_sal varchar2(30) )

              PARTITION BY RANGE (Emp_no)

              ( PARTITION emp1 VALUES LESS THAN (10) TABLESPACE tbsa,

               PARTITION emp2 VALUES LESS THAN (20) TABLESPACE tbsb);

Note: Use bigger values for range partition ( in lakh)  in case of production environment.

CREATE TABLE Emp (Emp_no NUMBER,  Emp_sal varchar2(30))

               PARTITION BY HASH (Emp_no)

               PARTITIONS 4  STORE IN (gear1, gear2, gear3, gear4);

CREATE INDEX local_idx_SRC ON BILLING.TESTLALIT (PATIENTSERVICEREQUISTID)  LOCAL;

CREATE INDEX global_part_idx ON  BILLING.TESTLALIT(PATIENTSERVICEREQUISTID)

GLOBAL PARTITION BY RANGE(PATIENTSERVICEREQUISTID)

(PARTITION p1 VALUES LESS THAN(1000),

 PARTITION p4 VALUES LESS THAN(10000));

Note : MAXVALUE value can be used if further you don’t want to add new partition.

Caution: If any partition is filled up and new partition is not added, then your entire transaction will be stopped. So be careful when you are adding new partitions. But in Oracle 11g onwards there is solution to add partitions automatically.

-- add partition manually

Alter table owner.partition_tablename add PARTITION partitionname values less than (value)

e.g.,

sql> alter table hr.employee add PARTITION employee _4 values less than (1000000)

-- add partition automatically / add partition dynamically ( Oracle 11g onwards)

Sql> alter table owner.partition_tablename set interval (100000);

--e.g.,

alter table hr.employee set interval (500000);

Click Here to view more about Table partitioning

Click here to read Top 10 best practices for performance tuning

Do's

 

Ø  Use the cursor FOR loop – Whenever you need to read through every record fetched by a cursor, use the for loop instead of open cursor, fetch & close cursor steps

Ø  Work with records – Always fetch from an explicit cursor into a record declared with %ROWTYPE, as opposed to individual variables.

Ø  Write small programming blocks for all the queries/code which is re-used and call these programs in the procedures / functions instead of re-coding.

Ø  Use the %TYPE or %ROWTYPE declaration attributes to define the data type of those of variables to avoid data type mismatch. {If those database elements change, compiled code is discarded. When recompiled, the changes are automatically applied to the code}.

Ø  Use Bulk Collects, Bulk Fetch if the result for Select Stmt fetches more data

Ø  Use bind variables for comparing known values in the where clause of the queries. For eg. Where status=1 can be reframed as status=:a where a stores 1.

Ø  Coding a few simple queries in place of a single complex query is a better approach, because the individual SQL statements are easier to optimize and maintain.

Ø  Using if-else-end if conditions, write multiple queries instead of single query in which the columns are compared with null or not null values,

Ø  Use UNION ALL instead of UNION if the result set of both the queries are same

Ø  Use ‘For all’ Statements for repeated DML statements with an exception of using it cautiously for update statements

Ø  Use DECODE and CASE - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected

Don’ts

Ø  Avoid the LIKE predicate – Always replace a "like" with an equality, when appropriate

Ø  Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster

Ø  Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:    eg. Where cust_nbr = “123”, Where substr(ssn,7,4)= 1234

Ø  Avoid IN/NOT IN and use EXISTS/NOT EXISTS

Ø  Do not use “SELECT *” under any circumstances inside application code

Ø  Do not use count(*). Replace count(*) with count(rowid)

Ø  Avoid using unnecessary brackets. Unnecessary brackets increase parsing time and adversely affect query performance

Ø  Avoid usage of != or <> (not equal to) operators, instead use other relational operators like ‘>’ and ‘<’

Sure it will help. Don't forget to post comments. I hope sure we will post.