[oracle@rac1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 22 11:12:11 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn scott/tiger
Connected.
SQL> desc chained_rows
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER_NAME                                         VARCHAR2(30)
TABLE_NAME                                         VARCHAR2(30)
CLUSTER_NAME                                       VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
SUBPARTITION_NAME                                  VARCHAR2(30)
HEAD_ROWID                                         ROWID
ANALYZE_TIMESTAMP                                  DATE

SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table t1 list chained rows into chained_rows;

Table analyzed.

SQL> select count(1) from chained_rows;

COUNT(1)
----------
8284

SQL> create table t1_chained as select * from t1 where rowid in (select head_rowid from chained_rows where table_name='T1');

Table created.

SQL> select count(2) from t1_chained;

COUNT(2)
----------
8284

SQL> delete from t1 where rowid in (select head_rowid from chained_rows where table_name='T1');

8284 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1_chained;

8284 rows created.

SQL> commit;

Commit complete.

SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table t1 list chained rows into chained_rows;

Table analyzed.

SQL> select count(1) from chained_rows;

COUNT(1)
----------
0

SQL> drop table t1_chained purge;

Table dropped.

SQL>

ITPub博客

消除行连接

最新文章