全局临时表自动处理数据：
drop table ljb_tmp_session; create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1=2; select table_name,temporary,duration from user_tables where table_name='LJB_TMP_SESSION'; drop table ljb_tmp_transaction; create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2; select table_name, temporary, DURATION from user_tables where table_name='LJB_TMP_TRANSACTION'; insert all into ljb_tmp_transaction into ljb_tmp_session select * from dba_objects; select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session), (select count(*) transaction_cnt from ljb_tmp_transaction); commit; select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session), (select count(*) transaction_cnt from ljb_tmp_transaction); select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session), (select count(*) transaction_cnt from ljb_tmp_transaction);
不同session数据独立：
drop table ljb_tmp_session;
create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1=2;
select table_name,temporary,duration from user_tables where table_name='LJB_TMP_SESSION';
drop table ljb_tmp_transaction;
create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2;
select table_name, temporary, DURATION from user_tables where table_name='LJB_TMP_TRANSACTION';
--不同session的例子试验基于sesssion 的临时表即可了，不用试验另外一个了。
---连上session 1
insert into ljb_tmp_session select * from dba_objects where rownum<=10;
--可以体会提交，基于session 的提交并清理数据
commit;
select count(*) from ljb_tmp_session;
COUNT(*)
----------
10
---不退出session 1，继续登录session 2
insert into ljb_tmp_session select * from dba_objects where rownum<=20;
commit;
select count(*) from ljb_tmp_session;
COUNT(*)
----------
20
产生日志较少：
全局临时表是否在同样delete ,insert 和update 的情况下，产生的redo比普通表要少的多。
sqlplus "/ as sysdba"
grant all on v_$mystat to ljb;
grant all on v_$statname to ljb;
connect ljb/ljb
drop table t purge;
create table t as select * from dba_objects ;
--以下创建视图，方便后续直接用select * from v_redo_size进行查询
create or replace view v_redo_size as
select a.name,b.value
from v$statname a,v$mystat b
where a.statistic#=b.statistic#
and a.name='redo size';
select * from v_redo_size;
--中间输入你的删除插入更新的动作
select * from v_redo_size;
两次相减就是产生的redo量
索引组织表：
--必须有主键！
---分别建索引组织表和普通表进行试验
set autotrace off
drop table heap_addresses purge;
drop table iot_addresses purge;
create table heap_addresses
(empno number(10),
addr_type varchar2(10),
street varchar2(10),
city varchar2(10),
state varchar2(2),
zip number,
primary key (empno)
)
/
create table iot_addresses
(empno number(10),
addr_type varchar2(10),
street varchar2(10),
city varchar2(10),
state varchar2(2),
zip number,
primary key (empno)
)
organization index
/
insert into heap_addresses
select object_id,'WORK','123street','washington','DC',20123
from all_objects;
insert into iot_addresses
select object_id,'WORK','123street','washington','DC',20123
from all_objects;
commit;
---分别比较索引组织表和普通表的查询性能
set linesize 1000
set autotrace traceonly
select * from heap_addresses where empno=22;
SQL> select * from heap_addresses where empno=22;
执行计划
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 1 | 50 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0013751 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
659 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select * from iot_addresses where empno=22;
执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_104441 | 1 | 50 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
751 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
簇表：
--构造环境
--注意这个删除的先后顺序，否则会报ORA-00951: 簇非空
set autotrace off
drop table cust_orders;
drop cluster shc;
CREATE CLUSTER shc
(
cust_id NUMBER,
order_dt timestamp SORT
)
HASHKEYS 10000
HASH IS cust_id
SIZE 8192
/
CREATE TABLE cust_orders
( cust_id number,
order_dt timestamp SORT,
order_number number,
username varchar2(30),
ship_addr number,
bill_addr number,
invoice_num number
)
CLUSTER shc ( cust_id, order_dt )
/
---开始执行分析
set autotrace traceonly
variable x number
--以下是利用有序散列聚族表的方法，发现排序被避免
select cust_id, order_dt, order_number
from cust_orders
where cust_id = :x
order by order_dt;
执行计划
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)|
|* 1 | TABLE ACCESS HASH| CUST_ORDERS | 1 | 39 | |
----------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
--以下是普通方法，排序不可避免
select job, hiredate, empno
from scott.emp
where job = 'CLERK'
order by hiredate;
执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 60 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
外键索引性能：
drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;
---外键未建索引前的表连接性能分析
set autotrace traceonly
set linesize 1000
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;
执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 4260 | 137 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | 71 | 4260 | 137 (1)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_C | 71 | 2130 | 137 (1)| 00:00:02 |
------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
452 consistent gets
0 physical reads
0 redo size
3605 bytes sent via SQL*Net to client
459 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72 rows processed
--外键建索引后的表连接性能分析
CREATE INDEX IND_T_C_FID ON T_C (FID);
SELECT A.ID, A.NAME, B.NAME FROM T_P A, T_C B WHERE A.ID = B.FID AND A.ID = 880;
执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72 | 4320 | 69 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 72 | 4320 | 69 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_C | 72 | 2160 | 69 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T_C_FID | 72 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
83 consistent gets
0 physical reads
0 redo size
3605 bytes sent via SQL*Net to client
459 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72 rows processed
外键与锁：
--外键索引性能研究之准备 drop table t_p cascade constraints purge; drop table t_c cascade constraints purge; CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30)); ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID); CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30)); ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID); INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES; INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS; COMMIT; create index idx_IND_T_C_FID on T_C(FID); 外键有索引，没有死锁情况产生 --首先开启会话1 select sid from v$mystat where rownum=1; DELETE T_C WHERE FID = 2; --接下来开启会话2，也就是开启一个新的连接 select sid from v$mystat where rownum=1; DELETE T_P WHERE ID = 2000; 外键索引先删除 drop index idx_IND_T_C_FID; 外键索引删除后，立即有锁相关问题 --首先开启会话1 select sid from v$mystat where rownum=1; DELETE T_C WHERE ID = 2; --接下来开启会话2，也就是开启一个新的连接 select sid from v$mystat where rownum=1; --然后执行如下进行观察 DELETE T_P WHERE ID = 2000; --居然发现卡住半天不动了！
普通索引改主键：
drop table t_p cascade constraints purge; CREATE TABLE t_p (order_id NUMBER(3), item_id NUMBER(2), comments varchar2(400)); CREATE INDEX ord_itm_idx ON t_p(order_id,item_id); alter table t_p add constraint ord_itm_id_pk primary key (order_id,item_id) using index ord_itm_idx ; ---不需要如此操作(差别只是索引名不一样而已） drop index ord_itm_idx; alter table t_p add constraint ord_itm_id_pk primary key (order_id,item_id);
主键DISABLE再ENABLE后，外键需要重新生效
drop table t_p cascade constraints purge;
create table t_p (dept_id number,emp_name varchar2(20),emp_id number);
alter table t_p add constraints pk_dept_id PRIMARY KEY (dept_id);
ALTER TABLE t_p ADD CONSTRAINT FK_T_F FOREIGN KEY (emp_id) REFERENCES T_P (dept_id) ;
select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');
ALTER TABLE t_p DISABLE CONSTRAINT pk_dept_id CASCADE;
select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');
ALTER TABLE t_p ENABLE CONSTRAINT pk_dept_id ;
select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');
alter table t_p enable constraint FK_T_F;
select CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints t where t.constraint_name in ('PK_DEPT_ID','FK_T_F');
主键约束有延迟约束到提交才验证的功能
drop table test cascade constraints; create table test as select rownum object_id,object_name from dba_objects where rownum<=20; ALTER TABLE test ADD CONSTRAINT pk_object_id PRIMARY KEY(object_id) DEFERRABLE INITIALLY DEFERRED; INSERT INTO test VALUES (21,'ABC'); INSERT INTO test VALUES (21,'EFG'); COMMIT; SET CONSTRAINT pk_object_id IMMEDIATE; INSERT INTO test VALUES (1,'LATA'); INSERT INTO test VALUES (2,'KING'); COMMIT;
主外键设计其他约束条件：
drop table test cascade constraints; create table test as select rownum object_id,object_name from dba_objects where rownum<=20; ALTER TABLE test ADD CONSTRAINT pk_object_id PRIMARY KEY(object_id) DEFERRABLE INITIALLY DEFERRED; INSERT INTO test VALUES (21,'ABC'); INSERT INTO test VALUES (21,'EFG'); COMMIT; SET CONSTRAINT pk_object_id IMMEDIATE; INSERT INTO test VALUES (1,'LATA'); INSERT INTO test VALUES (2,'KING'); COMMIT;
表压缩：
---压缩表可减少数据量，从而减少IO
DROP TABLE t purge;
CREATE TABLE t NOCOMPRESS AS
SELECT rownum AS n, rpad(' ',500,mod(rownum,15)) AS pad
FROM dual
CONNECT BY level <= 200000;
execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');
--未压缩的表当前情况
SELECT table_name, blocks,compression FROM user_tables WHERE table_name = 'T';
TABLE_NAME BLOCKS COMPRESS
------------------------------ ---------- --------
T 14449 DISABLED
set autotrace traceonly
select count(*) from t;
执行计划
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3922 (1)| 00:00:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 200K| 3922 (1)| 00:00:48 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
14297 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--开始压缩表
set autotrace off
ALTER TABLE t MOVE COMPRESS;
execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t');
SELECT table_name, blocks,compression FROM user_tables WHERE table_name = 'T';
TABLE_NAME BLOCKS COMPRESS
------------------------------ ---------- --------
T 2639 ENABLED
set autotrace traceonly
select count(*) from t;
执行计划
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 718 (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 200K| 718 (1)| 00:00:09 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2595 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意：压缩后的表更新的开销会更大，查询耗费的CPU也更多。所以压缩表一般适合在更新比较少，且CPU消耗不大，IO消耗很大系统中试用。
索引压缩：
---压缩索引(联合索引的压缩层度会高一些）
DROP TABLE t1 purge;
CREATE TABLE t1 AS select * from dba_objects;
alter table T1 modify owner not null;
alter table T1 modify object_name not null;
alter table T1 modify object_type not null;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;
create index idx1_object_union on t1(owner,object_type,object_name);
execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx1_object_union');
--未压缩索引的当前情况
SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel FROM user_indexes t WHERE index_name = 'IDX1_OBJECT_UNION';
INDEX_NAME COMPRESS LEAF_BLOCKS BLEVEL
------------------------------ -------- ----------- ----------
IDX1_OBJECT_UNION DISABLED 2043 2
--开始压缩索引
drop table t2 purge;
create table t2 as select * from t1;
alter table T2 modify owner not null;
alter table T2 modify object_name not null;
alter table T2 modify object_type not null;
create index idx2_object_union on t2(owner,object_type,object_name);
ALTER index idx2_object_union rebuild COMPRESS;
execute dbms_stats.gather_index_stats(ownname=>user, indname=>'idx2_object_union');
SELECT t.index_name,t.compression,t.leaf_blocks,t.blevel FROM user_indexes t WHERE index_name = 'IDX2_OBJECT_UNION';
INDEX_NAME COMPRESS LEAF_BLOCKS BLEVEL
------------------------------ -------- ----------- ----------
IDX2_OBJECT_UNION ENABLED 907 2
set linesize 1000
set autotrace traceonly
select count(*) from t1 ;
执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 572 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_UNION | 251K| 572 (1)| 00:00:07 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2067 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select count(*) from t2 ;
执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 258 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX2_OBJECT_UNION | 282K| 258 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
922 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
列值如何存放：
--什么类型就放什么值，否则会发生类型转换，导致系能问题！
--（是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）
--举例说明：
drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonly
select * from t_col_type where id=6;
执行计划
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_COL_TYPE | 1 | 36 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("ID")=6)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
540 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。
select * from t_col_type where id='6';
执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_COL_TYPE | 1 | 36 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
2 - access("ID"='6')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
过时的字段：
CHAR和LONG基本要被VARCHAR2和CLOB等替换，属于已经过时的字段类型。 其中CHAR不利扩展，且有可能浪费空间。 而LONG的更新查询操作极为麻烦！