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 ?
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.