Oracle Database Partitions Types in 10g..

Hi Guys, Today will discuss about Oracle Database Partitions...


Partitioning is an approach or an option which is supplied by oracle  which splits the tables into small chunks.

Main Purpose :-

> To enhance the SQL performance by scanning a small set of rows instead of  complete table.

> To reduce the time in backups.

>  Index Range Scan Become more Inefficient.

> In 11g You Can Transport the Tablespace with the Single Partitions.


When to Partitioned a Table?

1. Oracle recommends When table size is reached up-to 2gb with high I/O is always considered to be partitioned.

2. Tables which are considering historical data where only current data is maintained and the old data will be Archived on Storage.


Some Basic and Mostly used Partitions Types up-to 10g :- 


   Range Partitions : If your Business data is on Range Basis it will support By Ranges Ex:- Dates

 >  Below i have created a Range-partitioned table which distribute the data Range-Wise
    on monthly basis.

>  In this Range-Partition table i gave 3 partitions (p1,p2,p3) which distributes the data on the range of months
    in the same tablespace.

>  This partition has created on the join_date column.

Example of Range Partition :-

SQL> create table emp_part
    (
    empno number(5),
    ename varchar2(5),
    job varchar2(5),
    join_date date
    )
    partition by range (join_date)
    (
   partition p1 values less than (TO_DATE ('01-JAN-2017','DD-MON-YYYY')) tablespace ts4,
   partition p2 values less than (TO_DATE ('01-FEB-2017','DD-MON-YYYY')) tablespace ts4,
   partition p3 values less than (TO_DATE ('01-MAR-2017','DD-MON-YYYY')) tablespace ts4
   );

Table created.

                                                          =====  XXX =====

 List Partitions : The main advantage of List Partition is you can Group and Organize Un-ordered set of  data in a natural way. We can distribute the data in many ways by using List Partitions for example if your Business data is on Region,Designation or Country basis we can easily distributes this type of  data by making List Partitions.

> Below Example i have created Partition-table on LIST basis for job Designations.

SQL> create table emp_part
    (
    empno number(4),
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2),
    comm number(7,2),
    deptno number(2)
    )
    partition by list (job)
    (
    partition p1 values ('CLERK'),
    partition p2 values ('SALESMAN'),
    partition p3 values ('MANAGER'),
    partition p4 values ('ANALYST'),
    partition p5 values ('PRESIDENT'));

SQL> SELECT OWNER,TABLE_NAME,PARTITIONING_TYPE FROM ALL_PART_TABLES WHERE OWNER='SAM';

OWNER                  TABLE_NAME       PARTITIONING_TYPE

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

SAM                            EMP_PART                             LIST

                                                    ==== XXX ====

  Hash Partitioning : is a technique in which oracle maps data to a partitions Based on a Hashing algorithm.  This hashing algorithm evenly distributes Rows among partitions by giving same size.
       It is used with many distinct values (not identical) where there are no searches on ranges.

Syntax :-

 CREATE TABLE EMP_HASH
(
EMPNO NUMBER(5),
ENAME VARCHAR2(5),
LOCATION VARCHAR2(5))
PARTITION BY HASH (LOCATION)
(
PARTITION PH1 TABLESPACE USERS,
PARTITION PH2 TABLESPACE USERS
);

Table created.



Happy Learning...















   

ORA-14255 Table is not Partitioned by Range,List

> A Table that Contains only One Partition Cannot be Dropped. You must drop the Table.


SQL> CREATE TABLE KKHASH
    (
    EMPNO NUMBER(5),
    ENAME VARCHAR2(5),
    LOCATION VARCHAR2(5))
    PARTITION BY HASH (LOCATION)
    (
    PARTITION H1 TABLESPACE USERS,
    PARTITION H2 TABLESPACE USERS
    );

Table created.


SQL> ALTER TABLE KKHASH DROP PARTITION H1;

         ERROR at line 1:

ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method


SQL> DROP TABLE KKHASH;
       
Table dropped.


Happy Learning...

How to Find Global & Local Indexes in Oracle Partitions.

SQL> CREATE INDEX  EMP_IDX_G ON EMP_TAB (JOIN DATE)
         GLOBAL PARTITION BY RANGE (JOIN DATE)
         (PARTITION P1 VALUES LESS THAN (TO_DATE ('01-JAN-2020','DD-MON-YYYY')) tablespace users,
          PARTITION P2 VALUES LESS THAN (TO_DATE ('01-JAN-2021','DD-MON-YYYY')) tablespace users,
          PARTITION P3 VALUES LESS THAN (TO_DATE ('01-JAN-2022','DD-MON-YYYY')) tablespace users,
          PARTITION P4 VALUES LESS THAN (MAXVALUE) tablespace users
        );

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                                INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ --------------------------------
EMP_TAB                                          EMP_IDX_G                       GLOBAL



SQL> CREATE INDEX IX_EMP ON EMP_TAB (SDHSCD,SDMEDN,SDCASN) LOCAL;

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                     INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ -------------------------------
EMP_TAB                                  IX_EMP                               LOCAL




Happy Learning...

How to Calculate Index Size and Table Size in Oracle

Hi Guy's we can calculate all indexes sizes and table size in the below query.
where table name is PHTRNHST and rest of all are the indexes.


SQL> BREAK ON REPORT
SQL> COMPUTE SUM OF SIZE_MB ON REPORT
SQL> SELECT segment_name, SUM (bytes) / (1024 * 1024) size_mb FROM sys.dba_extents WHERE segment_type in('TABLE','INDEX')  AND segment_name in('PHTRNHST', 'X_1','X_TRANSACTION','X_RETURN','X_RET_1','IND_MRM,D_S') and owner='HMIS' group by segment_name;

SEGMENT_NAME                 SIZE_MB
--------------------------------------------------------
X_RETURN                                       80
X_RET_1                                          120
X_1                                                   60
PHTRNHST                                      344
X_TRANSACTION                           128
                                                     ----------
sum                                                   732

Happy Learning...