Re: [Q] Sorting a column
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.orgReceived on Fri Aug 08 2008 - 14:47:40 CDT