Dan Blum wrote:

Not even then.

SQL> CREATE CLUSTER hcl_srvr_id (
2 si_clustercol NUMBER(10))
3 PCTFREE 0
4 TABLESPACE uwdata
5 HASHKEYS 141
6 ROWDEPENDENCIES;

Cluster created.

SQL> CREATE TABLE cservers (
2 srvr_id NUMBER(10),
3 network_id NUMBER(10),
4 status VARCHAR2(1),
5 latitude FLOAT(20),
6 longitude FLOAT(20),
7 netaddress VARCHAR2(15))
8 CLUSTER hcl_srvr_id (srvr_id);

Table created.

SQL> CREATE TABLE cserv_inst (
2 siid NUMBER(10),
3 si_status VARCHAR2(15),
4 type VARCHAR2(5),
5 installstatus VARCHAR2(1),
6 location_code NUMBER(10),
7 custacct_id VARCHAR2(10),
8 srvr_id NUMBER(10),
9 ws_id NUMBER(10))
10 CLUSTER hcl_srvr_id (srvr_id);

Table created.

SQL> insert into cservers
2 select * from servers;

141 rows created.

SQL> insert into cserv_inst
2 select * from serv_inst;

999 rows created.

SQL> select rowid, count(*)
2 from cservers
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> select rowid, count(*)
2 from cserv_inst
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> CREATE CLUSTER sc_srvr_id (
2 srvr_id NUMBER(10))
3 SIZE 1024;

Cluster created.

SQL> CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;

Index created.

SQL> drop table cservers purge;

Table dropped.

SQL> drop table cserv_inst purge;

Table dropped.

SQL> CREATE TABLE cservers (
2 srvr_id NUMBER(10),
3 network_id NUMBER(10),
4 status VARCHAR2(1),
5 latitude FLOAT(20),
6 longitude FLOAT(20),
7 netaddress VARCHAR2(15))
8 CLUSTER sc_srvr_id (srvr_id);

Table created.

SQL> CREATE TABLE cserv_inst (
2 siid NUMBER(10),
3 si_status VARCHAR2(15),
4 type VARCHAR2(5),
5 installstatus VARCHAR2(1),
6 location_code NUMBER(10),
7 custacct_id VARCHAR2(10),
8 srvr_id NUMBER(10),
9 ws_id NUMBER(10))
10 CLUSTER sc_srvr_id (srvr_id);

Table created.

SQL> insert into cservers
2 select * from servers;

141 rows created.

SQL> insert into cserv_inst
2 select * from serv_inst;

999 rows created.

SQL> select rowid, count(*)
2 from servers
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL> select rowid, count(*)
2 from serv_inst
3 group by rowid
4 having count(*) > 1;

no rows selected

SQL>

You should have tried it before making an assumption and
claiming it was true.

The datafile will be identical.
The block will be identical.
But the row itself? Not likely.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
happen, but they don't provide details.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
happen, but they don't provide details.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
happen, but they don't provide details.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

DA Morgan <damorgan (AT) psoug (DOT) org> wrote:

Your argument is with the Oracle docs, not me. The 10.2 docs claim it can
happen, but they don't provide details.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

DA Morgan schrieb:
All rows from the same table within the same cluster key will still have
different slot numbers, hence different rowid's. But it is of course
possible for different tables within same cluster key to have same slot
numbers, so i think, Dan meant something like this

SQL> create cluster emp_dept(
2 deptno number(2)
3 )
4 ;

Cluster created.

SQL> create index emp_dept_idx on cluster emp_dept
2 ;

Index created.

SQL> create table emp_c
2 cluster emp_dept(deptno)
3 as select * from emp
4 ;

Table created.

SQL> create table dept_c
2 cluster emp_dept(deptno)
3 as select * from dept
4 ;

Table created.

SQL> select count(*) from (
2 select rowid from emp_c
3 intersect
4 select rowid from dept_c
5 )
6 ;

COUNT(*)
----------
3

SQL> select e.ename,e.deptno,d.dname
2 from emp_c e,dept_c d
3 where e.rowid=d.rowid
4 ;

ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
CLARK 10 ACCOUNTING


Best regards

Maxim

DA Morgan schrieb:
All rows from the same table within the same cluster key will still have
different slot numbers, hence different rowid's. But it is of course
possible for different tables within same cluster key to have same slot
numbers, so i think, Dan meant something like this

SQL> create cluster emp_dept(
2 deptno number(2)
3 )
4 ;

Cluster created.

SQL> create index emp_dept_idx on cluster emp_dept
2 ;

Index created.

SQL> create table emp_c
2 cluster emp_dept(deptno)
3 as select * from emp
4 ;

Table created.

SQL> create table dept_c
2 cluster emp_dept(deptno)
3 as select * from dept
4 ;

Table created.

SQL> select count(*) from (
2 select rowid from emp_c
3 intersect
4 select rowid from dept_c
5 )
6 ;

COUNT(*)
----------
3

SQL> select e.ename,e.deptno,d.dname
2 from emp_c e,dept_c d
3 where e.rowid=d.rowid
4 ;

ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
CLARK 10 ACCOUNTING


Best regards

Maxim

DA Morgan schrieb:
All rows from the same table within the same cluster key will still have
different slot numbers, hence different rowid's. But it is of course
possible for different tables within same cluster key to have same slot
numbers, so i think, Dan meant something like this

SQL> create cluster emp_dept(
2 deptno number(2)
3 )
4 ;

Cluster created.

SQL> create index emp_dept_idx on cluster emp_dept
2 ;

Index created.

SQL> create table emp_c
2 cluster emp_dept(deptno)
3 as select * from emp
4 ;

Table created.

SQL> create table dept_c
2 cluster emp_dept(deptno)
3 as select * from dept
4 ;

Table created.

SQL> select count(*) from (
2 select rowid from emp_c
3 intersect
4 select rowid from dept_c
5 )
6 ;

COUNT(*)
----------
3

SQL> select e.ename,e.deptno,d.dname
2 from emp_c e,dept_c d
3 where e.rowid=d.rowid
4 ;

ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
CLARK 10 ACCOUNTING


Best regards

Maxim

DA Morgan schrieb:
All rows from the same table within the same cluster key will still have
different slot numbers, hence different rowid's. But it is of course
possible for different tables within same cluster key to have same slot
numbers, so i think, Dan meant something like this

SQL> create cluster emp_dept(
2 deptno number(2)
3 )
4 ;

Cluster created.

SQL> create index emp_dept_idx on cluster emp_dept
2 ;

Index created.

SQL> create table emp_c
2 cluster emp_dept(deptno)
3 as select * from emp
4 ;

Table created.

SQL> create table dept_c
2 cluster emp_dept(deptno)
3 as select * from dept
4 ;

Table created.

SQL> select count(*) from (
2 select rowid from emp_c
3 intersect
4 select rowid from dept_c
5 )
6 ;

COUNT(*)
----------
3

SQL> select e.ename,e.deptno,d.dname
2 from emp_c e,dept_c d
3 where e.rowid=d.rowid
4 ;

ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
CLARK 10 ACCOUNTING


Best regards

Maxim

Dan Blum wrote:
Maxim demonstrates how.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org