Re: [Q] Sorting a column

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Aug 2008 12:47:40 -0700
Message-ID: <1218224852.828830@bubbleator.drizzle.com>


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.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Aug 08 2008 - 14:47:40 CDT

Original text of this message