表设计主要强调什么场合该选择什么技术，没有最高级的技术，只有最适合的技术。
1.表的特性
普通堆表的不足之处
1.查看产生多少日志
-
[oracle
@oracle
-db
-19c
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Wed Jan
4
14:
27:
13
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
SQL
>
alter session
set container
=PDB1;
-
-
Session altered.
-
-
SQL
>
select a.name,b.value
-
2
from v$statname a,v$mystat b
-
3
where a.statistic#
=b.statistic#
-
4
and a.name
=
'redo size';
-
-
NAME
-
--------------------------------------------------------------------------------
-
VALUE
-
----------
-
redo size
-
0
-
-
-
SQL
>
-
[oracle
@MaxwellDBA
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Wed Jan
4
15:
25:
31
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
sys
@cdb$root:orclcdb
>
alter session
set container
=ORCLPDB1;
-
-
Session altered.
-
-
sys
@cdb$root:orclcdb
>
select a.name,b.value
-
2
from v$statname a,v$mystat b
-
3
where a.statistic#
=b.statistic#
-
4
and a.name
=
'redo size';
-
-
NAME
VALUE
-
---------------------------------------------------------------- ----------
-
redo size
0
-
-
1
row selected.
-
-
sys
@cdb$root:orclcdb
>
实验准备工作，创建观察redo的视图
-
[oracle
@MaxwellDBA
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Wed Jan
4
15:
25:
31
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
sys
@cdb$root:orclcdb
>
alter session
set container
=ORCLPDB1;
-
-
Session altered.
-
-
sys
@cdb$root:orclcdb
>
select a.name,b.value
-
2
from v$statname a,v$mystat b
-
3
where a.statistic#
=b.statistic#
-
4
and a.name
=
'redo size';
-
-
NAME
VALUE
-
---------------------------------------------------------------- ----------
-
redo size
0
-
-
1
row selected.
-
-
sys
@cdb$root:orclcdb
>
观察删除记录产生了多少redo
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
VALUE
-
----------
-
redo size
-
11871068
-
-
-
SQL
>
delete
from t;
-
-
73262
rows deleted.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
VALUE
-
----------
-
redo size
-
22920220
-
-
-
SQL
>
select
22920220
-
11871068
from dual;
-
-
22920220
-11871068
-
-----------------
-
11049152
-
-
SQL
>
删除语句产生了差不多11M的日志量
观察插入记录产生了多少redo
-
SQL
>
-
SQL
>
insert
into t
select
*
from dba_objects;
-
-
73264
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
VALUE
-
----------
-
redo size
-
34265784
-
-
-
SQL
>
select
34265784
-
22920220
from dual;
-
-
34265784
-22920220
-
-----------------
-
11345564
-
-
SQL
>
观察更新记录产生了多少redo
-
SQL
>
-
SQL
>
update t
set object_id
=rownum;
-
-
73264
rows updated.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
-
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
VALUE
-
----------
-
redo size
-
47178732
-
-
-
SQL
>
select
47178732
-
34265784
from dual;
-
-
47178732
-34265784
-
-----------------
-
12912948
-
-
SQL
>
更新语句产生了差不多12M的redo
三个试验说明了对表的更新操作，无论是删除、插入还是修改，都会产生日志.
虽说安全第一，不过在某些特定的场合，某些表的记录只是作为中间结果临时运算而根本无须永久保留，这些表无须写日志，那就既高效又安全了！
delete无法释放空间
实际上工作中不少性能问题都和delete操作有关。
原因是：delete是最耗性能的操作，产生的undo最多，而且因为undo需要redo来保护的缘故，delete产生的redo量也最大。所以不少性能问题都和delete操作有关。
观察未删除表时产生的逻辑读
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
drop
table t purge;
-
-
Table dropped.
-
-
SQL
>
-
SQL
>
create
table t
as
select
*
from dba_objects;
-
-
Table created.
-
-
SQL
>
set autotrace
on
-
SQL
>
select
count(
*)
from t;
-
-
COUNT(
*)
-
----------
-
73263
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
2966233522
-
-
-------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Cost (
%CPU)
|
Time
|
-
-------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
1
|
397 (
1)
|
00:
00:
01
|
-
|
1
| SORT AGGREGATE
|
|
1
|
|
|
-
|
2
|
TABLE ACCESS
FULL
| T
|
73263
|
397 (
1)
|
00:
00:
01
|
-
-------------------------------------------------------------------
-
-
-
Statistics
-
----------------------------------------------------------
-
75
recursive calls
-
0 db block gets
-
1546 consistent gets
-
1422 physical
reads
-
0 redo size
-
552 bytes sent via
SQL
*Net
to client
-
384 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
20 sorts (memory)
-
0 sorts (disk)
-
1
rows processed
-
-
SQL
>
用delete命令删除t所有记录后，逻辑读发生了微小的变化
-
SQL
>
-
SQL
>
set autotrace off
-
SQL
>
delete
from t;
-
-
73263
rows deleted.
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
set autotrace
on
-
SQL
>
select
count(
*)
from t;
-
-
COUNT(
*)
-
----------
-
0
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
2966233522
-
-
-------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Cost (
%CPU)
|
Time
|
-
-------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
1
|
397 (
1)
|
00:
00:
01
|
-
|
1
| SORT AGGREGATE
|
|
1
|
|
|
-
|
2
|
TABLE ACCESS
FULL
| T
|
73263
|
397 (
1)
|
00:
00:
01
|
-
-------------------------------------------------------------------
-
-
-
Statistics
-
----------------------------------------------------------
-
0
recursive calls
-
0 db block gets
-
1426 consistent gets
-
0 physical
reads
-
0 redo size
-
549 bytes sent via
SQL
*Net
to client
-
384 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
0 sorts (memory)
-
0 sorts (disk)
-
1
rows processed
-
-
SQL
>
使用truncate命令清空表后，逻辑读终于大幅度下降了。
-
SQL
>
-
SQL
>
set autotrace off
-
SQL
>
truncate
table t;
-
-
Table truncated.
-
-
SQL
>
set autotrace
on
-
SQL
>
select
count(
*)
from t;
-
-
COUNT(
*)
-
----------
-
0
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
2966233522
-
-
-------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Cost (
%CPU)
|
Time
|
-
-------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
1
|
397 (
1)
|
00:
00:
01
|
-
|
1
| SORT AGGREGATE
|
|
1
|
|
|
-
|
2
|
TABLE ACCESS
FULL
| T
|
73263
|
397 (
1)
|
00:
00:
01
|
-
-------------------------------------------------------------------
-
-
-
Statistics
-
----------------------------------------------------------
-
1
recursive calls
-
1 db block gets
-
3 consistent gets
-
0 physical
reads
-
104 redo size
-
549 bytes sent via
SQL
*Net
to client
-
384 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
0 sorts (memory)
-
0 sorts (disk)
-
1
rows processed
-
-
SQL
>
delete 删除并不能释放空间，虽然delete将很多块的记录删除了，但是空块依然保留，Oracle在查询时依然会去查询这些空块。而truncate是一种释放高水平位的动作，这些空块被回收，空间也被释放了。
不过truncate显然不能替代delete，因为truncate是一种DDL操作而非DML操作，truncate后面是不能带条件的，即truncate table t where…是不允许的。但是如果表中这些where条件能形成有效的分区，Oracle是支持在分区表中做truncate分区的，命令大致为 alter table t truncate partition ＇分区名＇，如果where 条件就是分区条件，那等同于换个角度实现了 truncate table t where…的功能。
这就是分区表最实用的功能之一了，高效地清理数据，释放空间，老师将在后续章节中详细描述分区表的特性。
表记录太多检索较慢
有没有什么好方法能提升检索的速度呢？主要思路就是缩短访问路径来完成同样的更新查询操作。简单地说，完成同样的需求，访问块的个数越少越好。Oracle 为了尽可能减少访问路径提供了两种主要技术，一种是索引技术，另一种则是分区技术。
索引本身也是一把双刃剑，既能给数据库开发应用带来极大的帮助，也会给数据库带来不小的灾难。
分区表，除了之前描述的具有高效清理数据的功能外，还有减少访问路径的神奇本领。
索引回表读开销很大
观察TABLE ACCESS BY INDEX ROWID 产生的开销
-
SQL
>
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
drop
table t purge;
-
-
Table dropped.
-
-
SQL
>
create
table t
as
select
*
from dba_objects
where rownum
<=
200;
-
-
Table created.
-
-
SQL
>
create index idx_obj_id
on t(object_id);
-
-
Index created.
-
-
SQL
>
set linesize
1000
-
SQL
>
set autotrace traceonly
-
SQL
>
select
*
from t
where object_id
<=
10;
-
-
9
rows selected.
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
3784017797
-
-
--------------------------------------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Bytes
| Cost (
%CPU)
|
Time
|
-
--------------------------------------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
9
|
927
|
2 (
0)
|
00:
00:
01
|
-
|
1
|
TABLE ACCESS
BY INDEX ROWID BATCHED
| T
|
9
|
927
|
2 (
0)
|
00:
00:
01
|
-
|
*
2
| INDEX
RANGE SCAN
| IDX_OBJ_ID
|
9
|
|
1 (
0)
|
00:
00:
01
|
-
--------------------------------------------------------------------------------------------------
-
-
Predicate Information (identified
by operation id):
-
---------------------------------------------------
-
-
2
- access("OBJECT_ID"
<=
10)
-
-
-
Statistics
-
----------------------------------------------------------
-
51
recursive calls
-
126 db block gets
-
54 consistent gets
-
3 physical
reads
-
25504 redo size
-
3584 bytes sent via
SQL
*Net
to client
-
397 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
0 sorts (memory)
-
0 sorts (disk)
-
9
rows processed
-
-
SQL
>
有序插入却难有序读出
测试表记录顺序插入却难保证顺序读出
-
[oracle
@oracle
-db
-19c
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Thu Jan
5
10:
11:
06
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
SQL
>
connect maxwellpan
/maxwellpan
@PDB1;
-
Connected.
-
SQL
>
-
SQL
>
show
user
-
USER
is "MAXWELLPAN"
-
SQL
>
-
SQL
>
drop
table t purge;
-
-
Table dropped.
-
-
SQL
>
create
table t
-
2 (a
int,
-
3 b varchar2(
4000)
default rpad(
'*',
4000,
'*'),
-
4 c varchar2(
4000)
default rpad(
'*',
3000,
'*'));
-
-
Table created.
-
-
SQL
>
insert
into t(a)
values(
1);
-
-
insert
into t(a)
values(
2);
-
-
-
-
1
row created.
-
-
SQL
>
SQL
>
-
1
row created.
-
-
SQL
>
SQL
>
SQL
>
insert
into t(a)
values(
3);
-
-
1
row created.
-
-
SQL
>
select A
from t;
-
-
A
-
----------
-
1
-
2
-
3
-
-
SQL
>
delete
from t
where a
=
2;
-
-
1
row deleted.
-
-
SQL
>
insert
into t(a)
values(
4);
-
-
1
row created.
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
select A
from t;
-
-
A
-
----------
-
1
-
4
-
3
-
-
SQL
>
比较有无order by 语句在执行计划、开销上的差异
可以观察到，有排序的操作的统计信息模块有一个1 sorts（memory），表示发生了排序，执行计划中也有SORT ORDER BY关键字，不过最重要的是，没排序的操作代价为3，有排序的操作代价为4，性能上是有差异的，这在数量大的时候将会非常明显。
关于order by 避免排序的方法有两种思路。第一种思路是在order by 的排序列建索引，为什么可以消除排序呢？这就当成一个悬念，在后续介绍索引的章节中给大家揭秘。第二种方法就是，将普通表改造为有序散列聚簇表，这样可以保证顺序插入，order by 展现时无须再有排序动作
-
SQL
>
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
set linesize
1000
-
SQL
>
set autotrace traceonly
-
SQL
>
select A
from t;
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
1601196873
-
-
--------------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Bytes
| Cost (
%CPU)
|
Time
|
-
--------------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
3
|
39
|
3 (
0)
|
00:
00:
01
|
-
|
1
|
TABLE ACCESS
FULL
| T
|
3
|
39
|
3 (
0)
|
00:
00:
01
|
-
--------------------------------------------------------------------------
-
-
Note
-
-----
-
-
dynamic statistics used:
dynamic sampling (level
=
2)
-
-
-
Statistics
-
----------------------------------------------------------
-
0
recursive calls
-
0 db block gets
-
7 consistent gets
-
0 physical
reads
-
0 redo size
-
605 bytes sent via
SQL
*Net
to client
-
377 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
0 sorts (memory)
-
0 sorts (disk)
-
3
rows processed
-
-
SQL
>
-
SQL
>
select A
from t
order
by A;
-
-
-
Execution Plan
-
----------------------------------------------------------
-
Plan hash
value:
961378228
-
-
---------------------------------------------------------------------------
-
| Id
| Operation
| Name
|
Rows
| Bytes
| Cost (
%CPU)
|
Time
|
-
---------------------------------------------------------------------------
-
|
0
|
SELECT STATEMENT
|
|
3
|
39
|
4 (
25)
|
00:
00:
01
|
-
|
1
| SORT
ORDER
BY
|
|
3
|
39
|
4 (
25)
|
00:
00:
01
|
-
|
2
|
TABLE ACCESS
FULL
| T
|
3
|
39
|
3 (
0)
|
00:
00:
01
|
-
---------------------------------------------------------------------------
-
-
Note
-
-----
-
-
dynamic statistics used:
dynamic sampling (level
=
2)
-
-
-
Statistics
-
----------------------------------------------------------
-
26
recursive calls
-
0 db block gets
-
10 consistent gets
-
0 physical
reads
-
0 redo size
-
605 bytes sent via
SQL
*Net
to client
-
609 bytes received via
SQL
*Net
from client
-
2
SQL
*Net roundtrips
to
/
from client
-
1 sorts (memory)
-
0 sorts (disk)
-
3
rows processed
-
-
SQL
>
3.奇特的全局临时表
1.分析全局临时表的类型
全局临时表分为两种类型，一种是基于会话的全局临时表（on commit preserve rows），一种是基于事务的全局临时表（on commit delete rows）
创建基于事务和会话的全局临时表
-
SQL
>
-
SQL
>
show
user
-
USER
is "MAXWELLPAN"
-
SQL
>
-
SQL
>
drop
table t_tmp_session purge;
-
drop
table t_tmp_session purge
-
*
-
ERROR
at line
1:
-
ORA
-00942:
table
or
view does
not exist
-
-
-
SQL
>
drop
table t_tmp_transaction purge;
-
drop
table t_tmp_transaction purge
-
*
-
ERROR
at line
1:
-
ORA
-00942:
table
or
view does
not exist
-
-
-
SQL
>
create
global temporary
table t_tmp_session
on
commit preserve
rows
as
select
*
from dba_objects
where
1
=
2;
-
-
Table created.
-
-
SQL
>
-
SQL
> col table_name format a50
-
SQL
> col tem format a50
-
SQL
> col duration format a50
-
SQL
>
select table_name,temporary,duration
from user_tables
where table_name
=
'T_TMP_SESSION';
-
-
TABLE_NAME TEM DURATION
-
-------------------------------------------------- --- --------------------------------------------------
-
T_TMP_SESSION Y SYS$SESSION
-
-
SQL
>
create
global temporary
table t_tmp_transaction
on
commit
delete
rows
as
select
*
from dba_objects
where
1
=
2;
-
-
Table created.
-
-
SQL
>
select table_name,temporary,duration
from user_tables
where table_name
=
'T_TMP_TRANSACTION';
-
-
TABLE_NAME TEM DURATION
-
-------------------------------------------------- --- --------------------------------------------------
-
T_TMP_TRANSACTION Y SYS$TRANSACTION
-
-
SQL
>
上述命令完成了基于会话的全局临时表 T_TMP_SESSION 和基于事务的全局临时表T_TMP_TRANSACTION。接下来大家肯定想知道DML操作针对全局临时表产生的日志和针对普通表有什么不同.
2.观察各类DML的redo量
分别观察两种全局临时表针对各类DML语句产生的redo量
-
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';
-
SQL
>
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
240504
-
-
SQL
>
insert
into t_tmp_transaction
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
774580
-
-
SQL
>
select
774580
-240504
from dual;
-
-
774580
-240504
-
-------------
-
534076
-
-
SQL
>
insert
into t_tmp_session
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
1308488
-
-
SQL
>
select
1308488
-
774580
from dual;
-
-
1308488
-774580
-
--------------
-
533908
-
-
SQL
>
update t_tmp_transaction
set object_id
=rownum;
-
-
73266
rows updated.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
7788968
-
-
SQL
>
select
7788968
-
1308488
from dual;
-
-
7788968
-1308488
-
---------------
-
6480480
-
-
SQL
>
update t_tmp_session
set object_id
=rownum;
-
-
73266
rows updated.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
12430324
-
-
SQL
>
select
12430324
-
7788968
from dual;
-
-
12430324
-7788968
-
----------------
-
4641356
-
-
SQL
>
delete
from t_tmp_session;
-
-
73266
rows deleted.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
23330180
-
-
SQL
>
select
23330180
-
12430324
from dual;
-
-
23330180
-12430324
-
-----------------
-
10899856
-
-
SQL
>
delete
from t_tmp_transaction;
-
-
73266
rows deleted.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
34230036
-
-
SQL
>
select
34230036
-
23330180
from dual;
-
-
34230036
-23330180
-
-----------------
-
10899856
-
-
SQL
>
全局临时表和普通表产生日志情况的比较
-
SQL
>
-
SQL
>
drop
table t purge;
-
-
Table dropped.
-
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
create
table t
as
select
*
from dba_objects
where
1
=
2;
-
-
Table created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
34301144
-
-
SQL
>
insert
into t
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
45974392
-
-
SQL
>
select
45974392
-
34301144
from dual;
-
-
45974392
-34301144
-
-----------------
-
11673248
-
-
SQL
>
update t
set object_id
=rownum;
-
-
73266
rows updated.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
57139632
-
-
SQL
>
select
57139632
-
45974392
from dual;
-
-
57139632
-45974392
-
-----------------
-
11165240
-
-
SQL
>
delete
from t;
-
-
73266
rows deleted.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
68186560
-
-
SQL
>
select
68186560
-
57139632
from dual;
-
-
68186560
-57139632
-
-----------------
-
11046928
-
-
SQL
>
通过简单的比较我们即可得出结论：无论插入、更新还是删除，操作普通表产生的日志都比全局临时表要多。
DML操作针对全局临时表来说，只是产生的日志要少得多，而不是不会产生。
3.全局临时表的两大重要特性
1.高效删除记录
全局临时表有两个重要的特点。一是高效删除记录，基于事务的全局临时表 COMMIT 或者 SESSION 连接退出后，临时表记录自动删除；基于会话的全局临时表则是SESSION连接退出后，临时表记录自动删除，都无须我们手动去操作。二是针对不同会话数据独立，不同的SESSION访问全局临时表，看到的结果不同。
基于事务的全局临时表的高效删除
-
SQL
>
-
SQL
>
select
count(
*)
from t_tmp_transaction;
-
-
COUNT(
*)
-
----------
-
0
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
68186560
-
-
SQL
>
insert
into t_tmp_transaction
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
68720580
-
-
SQL
>
select
68720580
-
68186560
from dual;
-
-
68720580
-68186560
-
-----------------
-
534020
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
68720756
-
-
SQL
>
select
count(
*)
from t_tmp_transaction;
-
-
COUNT(
*)
-
----------
-
0
-
-
SQL
>
-
SQL
>
select
68720756
-
68720580
from dual;
-
-
68720756
-68720580
-
-----------------
-
176
-
-
SQL
>
---commit 方式删除全局临时表记录所产生的日志量是176B
-
SQL
>
基于会话的全局临时表COMMIT并不清空记录
-
SQL
>
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
68720756
-
-
SQL
>
insert
into t_tmp_session
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
69268792
-
-
SQL
>
select
69268792
-
68720756
from dual;
-
-
69268792
-68720756
-
-----------------
-
548036
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
select
count(
*)
from t_tmp_session;
-
-
COUNT(
*)
-
----------
-
73266
-
-
SQL
>
select
*
from v_redo_size;
-
-
NAME
VALUE
-
-------------------- ----------
-
redo size
69268940
-
-
SQL
>
select
69268940
-
69268792
from dual;
-
-
69268940
-69268792
-
-----------------
-
148
-
-
SQL
>
退出基于事务的全局临时表后再登入，观察记录情况
-
SQL
> exit
-
Disconnected
from Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
[oracle
@oracle
-db
-19c
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Thu Jan
5
11:
22:
30
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
SQL
>
connect maxwellpan
/maxwellpan
@PDB1;
-
Connected.
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
select
count(
*)
from t_tmp_session;
-
-
COUNT(
*)
-
----------
-
0
-
-
SQL
>
如果全局临时表在程序的一次调用执行过程中需要多次清空记录再插入记录，就要考虑用基于事务的，这时COMMIT可以把结果快速清理了，否则用delete效率低下。如果不存在这种情况，就用基于会话的，更简单，连COMMIT的动作都省了。
一般来说，基于会话的全局临时表的应用会更多一些，少数比较复杂的应用，涉及一次调用中需要清空记录再插入等复杂动作时，才考虑用基于事务的全局临时表。
2.不同会话独立
基于全局临时表的会话独立性的观察——第1个会话
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
select
*
from v$mystat
where rownum
=
1;
-
-
SID STATISTIC#
VALUE CON_ID
-
---------- ---------- ---------- ----------
-
18
0
0
3
-
-
SQL
>
select
*
from t_tmp_session;
-
-
no
rows selected
-
-
SQL
>
insert
into t_tmp_session
select
*
from dba_objects;
-
-
73266
rows created.
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
select
count(
*)
from t_tmp_session;
-
-
COUNT(
*)
-
----------
-
73266
-
-
SQL
>
基于全局临时表的会话独立性的观察——第2个会话
-
[root
@oracle
-db
-19c
~]# su
- oracle
-
[oracle
@oracle
-db
-19c
~]$
-
[oracle
@oracle
-db
-19c
~]$ sqlplus
/
as sysdba
-
-
SQL
*Plus:
Release
19.0
.0
.0
.0
- Production
on Thu Jan
5
11:
30:
28
2023
-
Version
19.3
.0
.0
.0
-
-
Copyright (c)
1982,
2019, Oracle.
All rights reserved.
-
-
-
Connected
to:
-
Oracle Database
19c Enterprise Edition
Release
19.0
.0
.0
.0
- Production
-
Version
19.3
.0
.0
.0
-
-
SQL
>
connect maxwellpan
/maxwellpan
@PDB1;
-
Connected.
-
SQL
>
show
user;
-
USER
is "MAXWELLPAN"
-
SQL
>
select
*
from v$mystat
where rownum
=
1;
-
-
SID STATISTIC#
VALUE CON_ID
-
---------- ---------- ---------- ----------
-
143
0
0
3
-
-
SQL
>
select
count(
*)
from t_tmp_session;
-
-
COUNT(
*)
-
----------
-
0
-
-
SQL
>
insert
into t_tmp_session
select
*
from dba_objects
where rownum
=
1;
-
-
1
row created.
-
-
SQL
>
commit;
-
-
Commit complete.
-
-
SQL
>
select
count(
*)
from t_tmp_session;
-
-
COUNT(
*)
-
----------
-
1
-
-
SQL
>
这是一个神奇的特性，结合高效删除，灵活应用这两个特性，将会给相关工作带来巨大的帮助。大家将会在后续的课程中感受到。
转载：https://blog.csdn.net/u011868279/article/details/128547006
查看评论