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
Printable View
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
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.
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?
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.
aps now I understand what is overflow :D
I meant cluster blocks yes sorry for the extent expression ;)
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).