1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

CLustered Primary key in oracle

Discussion in 'General' started by sonalj, Feb 23, 2010.

  1. sonalj

    sonalj Guest

    Hi,
    Can any one please tell me how to create a clusterd primary key in oracle?
    Also is it possible to add ASC(ascending) clause to each field in the clustered primary key?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I can only assume you have prior experience through SQL Server or Sybase since Oracle doesn't work the same way as those two products.

    Oracle provides an Index-Organized Table (IOT) which requires defining a primary key and organizes the table in a b-tree index structure making the index leaf nodes the actual table data (this is what Sybase and SQL Server do for a clustered index). Look at an example of creating an IOT:

    Code (SQL):
    CREATE TABLE emp_i
    (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),
    CONSTRAINT emp_i_pk PRIMARY KEY(empno, hiredate))
    organization INDEX;
    Notice the 'organization index' directive as well as the inline primary key definition; both are necessary to create an IOT. Looking at the execution plan we see

    Code (SQL):
    SQL> SELECT *
      2  FROM emp_i
      3  WHERE empno = 7900
      4  AND hiredate BETWEEN sysdate AND sysdate + 1;

    no ROWS selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1623964183

    ------------------------------------------------------------------------------
    | Id  | Operation         | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |          |     1 |    87 |     2   (0)| 00:00:01 |
    |*  1 |  FILTER           |          |       |       |            |          |
    |*  2 |   INDEX RANGE SCAN| EMP_I_PK |     1 |    87 |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------

    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------

       1 - FILTER(SYSDATE@!<=SYSDATE@!+1)
       2 - access("EMPNO"=7900 AND "HIREDATE">=SYSDATE@! AND
                  "HIREDATE"<=SYSDATE@!+1)

    Note
    -----
       - dynamic sampling used FOR this statement
    The primary key leaf nodes are the table data thus no table access was listed or required.

    I hope this helps you understand Oracle better and gives you that which you requested.