Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 22, 2016 - 5:32 am UTC

Last updated: May 27, 2016 - 12:27 am UTC

Version: 11g

Viewed 1000+ times

You Asked


Ref. :- https://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm
See Figure :- 2.6

Suppose We create cluster on emp and dept. and cluster is clus_emp_dept and common column is dept_no.
Now question is

1. suppose cluster allocate continuous memory or not?
2. suppose cluster created and i inserted data then, I also reinsert data on emp table then what happen below situation
2.1 :- Insert row in emp table and emp_no is 20.
Here I assume that row insert.
2.2 :- Now I insert a emp with emp_no is 30 then how to insert?
Here I assume that emp_30 insert after block of emp_no 20 or i wrong?
2.3 :- Once again I insert emp_no 20 then what happen?
Here I assume that To allocate block after Block of emp_no 20 and before emp_no 30 or I'm wrong?

Can you explain me above situation , how cluster table work?

and Connor said...

Some good info here

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6369500346363001

But the concept is relatively straihtforward

If you give me a a department row (deptno=100), and some employee records which all belong to deptno=100, then we are going to *try* to store these records together.

The emphasis here is *try*...if you have 50,000 employees all in deptno=100, then obviously we are not going to be able to store all 50,000 on the same block as the department row for deptno=100. We will have to use multiple blocks, and link them together.

But if its just a small number, then there's a good chance, they'll all sit on the same block as the department row, hence making joins more efficient.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 23, 2016 - 3:50 am UTC

How to create cluster on existing table?
Connor McDonald
May 23, 2016 - 6:12 am UTC

You can't directly. You could use DBMS_REDEFINITION

SQL> create cluster t_clust (a integer, b integer);

Cluster created.

SQL> create table t_new ( a int primary key, b int, z int )
  2  cluster t_clust(a,b);

Table created.

SQL>
SQL> create index t_clust_ix on cluster t_clust;

Index created.

SQL>
SQL> create table t ( a int primary key, b int, z int );

Table created.

SQL>
SQL> insert into t
  2  select rownum , rownum, rownum
  3  from dual
  4  connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> exec dbms_redefinition.can_redef_table(user, 't');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.start_redef_table(user, 't', 't_new');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(user, 't', 't_new');

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, cluster_name
  2  from user_tables
  3  where table_name = 'T';

TABLE_NAME                     CLUSTER_NAME
------------------------------ ------------------------------
T                              T_CLUST

1 row selected.

SQL>
SQL>


A reader, May 25, 2016 - 9:39 am UTC

exec dbms_redefinition.start_redef_table(user, 't', 't_new');

but table or view does not exists
Connor McDonald
May 26, 2016 - 3:54 am UTC

Paste the output of the entire script, just like I do.

A reader, May 26, 2016 - 11:58 am UTC 

step 1
------

create table dept
(
  dno number(6) primary key,
  dname varchar2(20),
  location varchar2(20)
)

step 2
------

create table emp
(
  eno number(6) primary key,
  dno number(6) references dept (dno),
  ename varchar2(20),
  job varchar2(20),
  mobileno number(10)  
)

step 3
------

begin
  for i 1 in 100000
  loop
      insert into dept values(i, dbms_random.string('U',20),dbms_random.string('U',20))
  end loop;
end;
/ 

begin
  for i in 1 .. 400000
  loop
      insert into emp values(i,dbms_random.values(1,100000), dbms_random.string('A',20),dbms_random.string('U',20),dbms_random.values(9009009009,9999999999));
      commit;
  end loop;
end;
/  


step 4
------

create cluster cl_emp_dept (dno number(4));
 
create index cl_ind_emp_dept on cluster cl_emp_dept;

step 5
------

begin
  dbms_redefinition.can_redef_table('user1', 'dept');
end; 

step 6
------

begin
  dbms_redefinition.start_redef_table('user1', 'dept','dept_new');
end; 



=========================================================ror :- step 6
============================================================
Error starting at line 1 in command:
begin
  dbms_redefinition.start_redef_table('user1', 'dept','dept_new');
end; 
Error report:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
==========================================================================

Connor McDonald
May 27, 2016 - 12:27 am UTC

Not "user1" in quotes, but just 'user' the *function* which returns who you are currently connected as:

So instead of:

dbms_redefinition.start_redef_table('user1', 'dept','dept_new');

use

dbms_redefinition.start_redef_table(user, 'dept','dept_new');

Or, if the dept table is in another schema (say SCOTT) you would do:

dbms_redefinition.start_redef_table('SCOTT', 'dept','dept_new');
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.