Table Partitioning Learning

[ahmedkhaled]

بسم الله الرحمن الرحيم 

اعترافا بالجميل لهذا الموقع الذي تعلمت منه ,اقدم لاخواني بحث عن

(Table partition methods)

اسال الله ان ينفع به من يقرا فيه

لو في اي سؤال او تعليق ارحب به لتعم الفائده لي ولغيري

يحق لادارة الموقع تثبيت الموضوع اذا رات انه يتطلب الامر 

ولكم جزيل الشكر جميعا

 

 

 

Table

Partitioning Methods

 

 

 

Partitioning table is one of the most important features on oracle database, which makes the dealing with the huge data become easy and quickly.  

 

1-        Range partitioning.

2-     Hash partitioning.

3-     List partitioning.

4-     Reference Partition.

5-     Composite range-hash partitioning.

6-     Composite range-list partitioning.

 

 

 

1.     Range partition using for mapping rows to partitions based on ranges of column(s) values

1.1.      Create the needed TABLESPACE

     CREATE TABLESPACE  SAL_Q1(SAL_Q2, SAL_Q3, SAL_Q4)

     DATATFILE 'DTQ1'

     SIZE 20 M;

1.2.   Create table with range partition

      CREATE TABLE sales

      (INV_NO     NUMBER,

       SALE_MON   NUMBER,

       SALE_YEAR  NUMBER

      )

      PARTITION BY RANGE (SALE_YEAR  , SALE_MON)

     (PARTITION sales_Q1 VALUES LESS THAN (1999, 04) TABLESPACE SAL_Q1 ,

      PARTITION sales_q2 VALUES LESS THAN (1999, 07) TABLESPACE SAL_Q2,

      PARTITION sales_q3 VALUES LESS THAN (1999, 10) TABLESPACE SAL_Q3,

      PARTITION sales_q4 VALUES LESS THAN (2000, 01) TABLESPACE SAL_Q4

     );

 

1.3.   Select from the table using the partition

          Select * from sales partition (sales_q2)

1.4.   Adding partition

       ALTER TABLE sales

       ADD PARTITION sales_q5 VALUES LESS THAN (2000,04) TABLESPACE SAL_Q5

1.5.   Merging Partitions

ALTER TABLE SALES

MERGE PARTITIONS SALES_Q4,SAL_Q5 INTO PARTITION SAL_Q5

·         Note1: You must specify the lowest range then the biggest ….

·         Note2: You cannot put the biggest range into the lowest range.

·         Note3: Merge used instead of drop partition if you need to preserve the data.

   

 

 

 

2.        Hash Partitioning using if your data does not easily lend itself to range partitioning

Command & Example :

2.1.         Creating needed table space

2.2.         Create table with hash partition 

Create table emp

(id  number,

Name varchar2(200),

Job_title char)

PARTITION by hash (job_title)

(PARTITION p1 TABLESPACE h1,

 PARTITION p2 TABLESPACE h1,

 PARTITION p3 TABLESPACE h3

 )

2.3.         Select from the table using the partition

     Select * from sales partition (p1)

2.4.         Adding partition

Alter table sales add partition p4 tablespace h4

2.5.         COALESCE PARTION, this will remove the last partition and redistribute its contents into one or more partitions determined by hash function

ALTER TABLE

COALESCE PARTITION

 

 

 

 

 

 

3.        List partitioning using if you have specific list of values you need to mapping

3.1.         Create the needed tablespace

3.2.         Create the table with list partition 

CREATE TABLE SAL_BY_REGION

      (DEPTNO          number,

       DEPTNAME       varchar2(20),

       QUARTERLY_SALES number(10, 2),

       STATE          varchar2(2))

   PARTITION BY LIST (STATE)

      (PARTITION NORTHEWST VALUES ('OR', 'WA'),

       PARTITION SOUTHWEST VALUES ('AZ', 'UT', 'NM'),

       PARTITION NORTHEAST VALUES  ('NY', 'VM', 'NJ'),

       PARTITION SOUTHEAST VALUES ('FL', 'GA')

      );

3.3.         Select from the table using the partition

   Select   *

   from     SAL_BY_REGION

   partition (SOUTHEAST)

3.4.         Adding partition

   alter table SAL_BY_REGION add partition EX values      ('NY','WA')

3.5.         MERG PARTITIONS

ALTER TABLE SAL_BY_REGION

MERGE PARTITIONS EXMP, northwest INTO PARTITION northwest

3.6.            Modifying  partition

3.6.1.                     Adding values

   ALTER TABLE SAL_BY_REGION

    MODIFY PARTITION NORTHEWST

          ADD VALUES ('NO');

3.6.2.  Dropping values

         ALTER TABLE SAL_BY_REGION

          MODIFY PARTITION NORTHEWST

                           ADD VALUES ('NO');

 

 

 

4.Reference-Partitioned using if you need to partition your table depend on the master table

4.1.         Create  table with reference partition

Create table TST_CLIENT PRIMARY KEY

( C_ID       number,

  C_NAME     varchar2(200),

  C_COUNTRY  CHAR

)

PARTITION BY LIST (C_COUNTRY)

(PARTITION P1 VALUES('E'),

PARTITION  P2 VALUES  ('K'))

 

CREATE TABLE CLIENT_INVOICE

(INV_NO    NUMBER,

 QTY   NUMBER,

 C_ID  NUMBER NOT NULL,

 CONSTRAINT CL_INV FOREIGN KEY (C_ID)

 REFERENECE T_CLIENTS(C_ID))

 PARTITION BY REFERENCE (CL_INV)

 

 

 

4.        Composite range-hash partitioning using if you need to create table with the tow  partitions (Composite partitions)

 

4.1.         Create the needed TABLESPACE (tp1,tp2,tp3,tp4)

4.2.    Create table  composite  partition .

CREATE TABLE  TST_COMP_HASH_RANG

   (ID        number ,

    MON       number,

    RANGE     number

   )

    partition by range (MON)

    SUBPARTITION by hash (RANGE)

    (

     SUBPARTITION  p1   VALUES LESS THAN (4)   TABLESPACE  tp1,

     SUBPARTITION  p2   VALUES LESS than (8)   TABLESPACE  tp2

     (

      SUBPARTITION  sp1 TABLESPACE  tp3 ,

      SUBPARTITION  sp2 TABLESPACE  tp4

     )

    )

4.3.         Select  from the table

4.3.1.  Select form table using the master partition

Select  * from  TST_COMP_HASH_RANG partition (p1)

4.3.2.  Select from table using the sub partition

Select * from TST_COMP_HASH_RANG SUBPARTITION  (sp1)

4.4.         Adding partition

Alter table  TST_COMP_HASH_RANG

MODIFY PARTITION P1

ADD SUBPARTITION p3 TABLESPACE TP3

4.5.         Merging  Partitions

ALTER TABLE TST_COMP_HASH_RANG

MERGE PARTITIONS P1,P2 INTO PARTITION P2

SUBPARTIONS 4

 

 

 

 

5.        Composite Range-List using if you need to partitioning the table as ranges and divided this ranges partitions to sub list partition

 

5.1.         Create the needed TABLESPACE  (tp1,tp2)

5.2.         Create table with range list partition

  CREATE TABLE TST_LIST_HASH

  (ID       number,

   MON      number,

   ACTION  number

   )

  Partition by range (MON) SUBPARTITION  by list (action)

  ( partition p1 values less than (4)  TABLESPACE  tp1

   (SUBPARTITION  AB  values  ('A','B'),

    SUBPARTITION  CD   values ('C','D')

    ),

   Partition  p2 values less than (8) TABLESPACE  tp2

   (SUBPARTITION  AB2    VALUES ('A','B'),

     SUBPARTITION  CD2    VALUES ('A','B')

    )

   )

5.3.         Select command

  SELECT * FROM TST_LIST_HASH  PARTITION (CD)

 

5.4.         Adding partition

  ALTER TABLE TST_COP_LIST_RANGE

  MODIFY PARTITION P1

  ADD SUBPARTITION EF VALUES ('E','F') TABLESPACE TP2

5.5.         Merging partitions

ALTER TABLE TST_LIST_HASH

MERGE SUBPARTITION EF, CD2 INTO PARTITION CD2

5.6.             Modifying  partition

5.6.1.        Adding values

     ALTER TABLE TST_LIST_HASH

      MODIFY SUBPARTITION AB

      ADD VALUES (NO');

5.6.2.        Dropping values

   ALTER TABLE TST_LIST_HASH

        MODIFY SUBPARTITION AB

        DROP VALUES (NO');

 

 

 

 

Common topics

1- Dropping a table partition

 

1.1.  Drop partition command is:

   Alter table T1 drop partition P1

1.2.  Drop sub partition command is:

   Alter table T1 DROP SUBPARTITION SP1

2- Exchange portions

2.1.         RANG, HASH AND LIST PARTITION TYPES.

LATER TABLE  TAB1

EXCHANGE PARTITION P1 WITH TABLE TAB2

Note : Table in alter table exchange partition must be non partitioned table and have the same number of columns, the same columns type and same columns size.

2.2.         Hash-Partitioned Table with a Range-Hash Partition

         ALTER TABLE T1 EXCHANGE PARTITION P2 WITH TABLE T2

2.3.         Exchanging a Sub partition of a Range-Hash Partitioned Table

           ALTER TABLE T1 EXCHANGE SUBPARTITION SP1         WITH TABLE T2 INCLUDING INDEXES;  

3- Renaming Partitions

5.7.            Rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. For example: ALTER TABLE TAB1 RENAME PARTITION P1 TO P2

5.8.  Renaming a Table Sub partition Likewise, you can assign new names to sub partitions of a table. In this case you would use the ALTER TABLE ... RENAME SUBPARTITION syntax.

4- Moving partitions tablespace

ALTER TABLE T1 MOVE PARTITION  P1 (Was using tablespace called  tp3)

TABLESPACE TP1

 

 

 

 

 

https://docs.oracle.com/cd/B10500_01/server.920/a96521/partiti.htm

https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006455

https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm

[ahmedkhaled]

انا كنت عامل التنسيق من اليسار الى اليمين مش عارف ليه طلع كده  لو امكن من ادارة الموقع تعديله يبقى جزاكم الله خيرا او اني احذفه واعمله تاني

[Amgad]

السلام عليكم ورحمة الله 

 

يمكنك استخدام رمز  من القائمة بالصف العلوي الثاني في المنتصف لتنسيق كود الأوامر... كما هو موضح في الصورة المرفقه  

ولكن ستختفي الألوان 

 

شكرا لك على المشاركة المتميزه 

جزاك الله خيرا 

 

مثال لما سبق لك عرضه 

1.     Range partition using for mapping rows to partitions based on ranges of column(s) values
1.1.      Create the needed TABLESPACE
     CREATE TABLESPACE  SAL_Q1(SAL_Q2, SAL_Q3, SAL_Q4)
     DATATFILE 'DTQ1'
     SIZE 20 M;
1.2.   Create table with range partition
      CREATE TABLE sales
      (INV_NO     NUMBER,
       SALE_MON   NUMBER,
       SALE_YEAR  NUMBER
      )
      PARTITION BY RANGE (SALE_YEAR  , SALE_MON)
     (PARTITION sales_Q1 VALUES LESS THAN (1999, 04) TABLESPACE SAL_Q1 ,
      PARTITION sales_q2 VALUES LESS THAN (1999, 07) TABLESPACE SAL_Q2,
      PARTITION sales_q3 VALUES LESS THAN (1999, 10) TABLESPACE SAL_Q3,
      PARTITION sales_q4 VALUES LESS THAN (2000, 01) TABLESPACE SAL_Q4
     );

[ahmedkhaled]

واياكم 

[ahmedkhaled]

لكن الالوان مهمه للتوضيح حضرتك عارف الدفيلوبر كلهم عارفين الالوان دي بتسهل في فهم الكوماند

الي محتاج الموضوع ياخده وينسخه على ورد فايل وهايشتغل تمام ان شاء الله