DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Clusters

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    Are anyone expert using Clusters in Oracle? I know how it works (groupping tables in same segment), But I have some question regarding DML operations on clusters. Anyone know how Clusters handle DML operations ?

    Cheers

  2. #2
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    From the user perspective, clustered tables handle DML the same way unclustered tables do. The syntax is the same; the fact that the tables are clustered is an internal issue.

    However, the performance may differ quite a bit, depending on what kind of cluster (index or hash) you use and whether or not your WHERE clause references the cluster key column in the table.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I mean DML operations from DBA´s view, like how it allocates extents. For example

    (Using index cluster)

    DEPT EMP in a cluster called dept_emp, according to Cluster definition the rows of emp and dept are kept in same blocks as long as the cluster key is same (deptno in this case), I am wondering, if I insert 1000 deptno values in dept.deptno and leave dept.dname and dept.loc empty and all emp columns empty Oracle will allocate extents for these "deptnos", if later, I then insert 1000 rows in emp.empno and emp.deptno (deptno same value as those inserted in dept table) leaving the other columns empty will all these new values be allocated in new blocks or same blocks as those I inserted previously in dept.deptno?

  4. #4
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    When you say "...Oracle will allocate extents for these "deptnos"...", I assume you mean cluster blocks.

    In answer to your question, Oracle will place the column data into the already-allocated cluster block as long as there is room for it. If there is insufficient room, Oracle will store the data in "overflow" blocks, which creates the same performance problems you encounter with row chaining/row migration in a non-clustered table.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    aps now I understand what is overflow :D
    I meant cluster blocks yes sorry for the extent expression ;)

  6. #6
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    An overflow block is a cluster block that is not associated with any specific cluster key value. Instead, data that will not fit into the original cluster block will be stored in overflow block(s).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width