Re: [Q] Sorting a column

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 09 Aug 2008 00:14:38 +0200
Message-ID: <489CC54E.3070401@gmail.com>

DA Morgan schrieb:
> Dan Blum wrote:
>

>> Only if they're in a cluster - rows that have the same cluster key have
>> the same ROWID (or so it says in the docs, I haven't tried it). 

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

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 Received on Fri Aug 08 2008 - 17:14:38 CDT