Posts Oracle数据库学习-表管理
Post
Cancel

Oracle数据库学习-表管理

Oracle中的表有很多类型,本文章涉及Heap-Organized的Table,External Table和临时表,以及Index-Organized Table。

Heap-Organized Table

最常见的表就是Heap-Organized Table,在创建之初,可以指定ORGANIZATION HEAP,或者直接忽略此参数。Heap-Organized表中数据无序存储,由rowid物理地址唯一地标识每一行记录。

Heap-Organized表的创建可以指定表空间,存储参数,比如下面例子。

1
2
3
4
5
SQL> drop tablespace ZIGZAG_TBS0
  2  including contents and datafiles;

表空间已删除。
已用时间:  00: 00: 04.29

先创建表空间,Uniform的管理方式。

1
2
3
4
5
6
7
8
9
10
11
SQL> CREATE TABLESPACE ZIGZAG_TBS0 DATAFILE
  2    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ZIGZAG\ZDF_01.DBF'
  3    SIZE 10M AUTOEXTEND OFF
  4    LOGGING
  5    PERMANENT
  6    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  7    BLOCKSIZE 8K
  8    SEGMENT SPACE MANAGEMENT MANUAL;

表空间已创建。
已用时间:  00: 00: 01.07

验证

1
2
3
4
5
6
7
8
9
SQL> select tablespace_name,extent_management,allocation_type from dba_tablespaces 
	 where tablespace_name='ZIGZAG_TBS0';

TABLESPACE_NAME  EXTENT_MANAGEMENT  ALLOCATION_TYPE
---------------- -------------------- ------------------
ZIGZAG_TBS0      LOCAL              UNIFORM

已选择 1 行。
已用时间:  00: 00: 00.06

创建表,指定存储参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> create table staff (
  2    staffno number(5),
  3    job varchar2(200),
  4    salary number(7, 2)
  5  )
  6  tablespace ZIGZAG_TBS0
  7  storage(
  8    INITIAL 2K      --第一个extent分配100K
  9    NEXT 2K         --下一个extent大小
 10    MINEXTENTS 2    --初始时分配2个extent
 11    MAXEXTENTS 100  --最大
 12    PCTINCREASE 60  --下一次分配时较已分配的的百分比
 13  );

表已创建。
已用时间:  00: 00: 00.06

此处的Next,在第一次时为storage中指定,第三次及以后的大小计算方式为

1
Next=前次已经分配的大小*(1+PCTINCREASE/100) 

可以通过user_tables来查看extent的分配情况。

1
2
3
4
5
6
7
8
SQL> select 'init='|| initial_extent,'next='||next_extent,
	'min='||min_extents,'max='||max_extents,'pct='||pct_increase
	from user_tables where table_name='STAFF';
--------------------------------------------------------
init=24576  next=1048576  min=1  max=2147483645  pct=60

已选择 1 行。
已用时间:  00: 00: 00.00

External Table

和普通的表很相像,但是oracle只保存了表的metadata,数据部分为外部操作系统的文件,因此只读只能select,不能做其他的DML。External Table一般用于数据的初始化加载工作。

通过下面的例子来演示如何通过External Table的创建来读取外部文件,并加载到数据库中的普通表中。

1,首先在操作系统中创建两个数据文本文件

staff1.txt

1
2
3
4
5
6
7
8
100, 'CEO', 1000000.99
101, 'CFO', 200000.99
102, 'CIO', 100000.99
103, 'CTO', 90000.99
104, 'Director', 80000.99
105, 'Manager', 70000.99
106, 'Leader', 60000.99
100, 'Member', 50000.99

staff2.txt

1
2
3
4
5
6
7
8
200, 'Jerry', 99991000000.99
201, 'Jim', 200000.99
202, 'Tom', 100000.99
203, 'Tommy', 90000.99
204, 'Kate', 80000.99
205, 'Joan', 70000.99
206, 'Jack', 60000.99
200, 'Apple', 50000.99

2,以sysdba身份创建文件目录并授权读写权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL> conn / as sysdba;  
已连接。

SQL> create or replace directory staff_data_dir
  2    as 'D:\oracle\zigzag\staffdata';

目录已创建。
已用时间:  00: 00: 00.09

SQL> create or replace directory staff_log_dir
  2    as 'D:\oracle\zigzag\staffdata\log';

目录已创建。
已用时间:  00: 00: 00.00

SQL> create or replace directory staff_baddata_dir
  2    as 'D:\oracle\zigzag\staffdata\baddata';

目录已创建。
已用时间:  00: 00: 00.00

SQL> grant read on directory staff_data_dir to myuser;

授权成功。
已用时间:  00: 00: 00.03

SQL> grant write on directory staff_log_dir to myuser;

授权成功。
已用时间:  00: 00: 00.00

SQL> grant write on directory staff_baddata_dir to myuser;

授权成功。
已用时间:  00: 00: 00.00

3,切换到schema用户,创建External Table,并通过ORACLE_LOADER读取外部文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> connect myuser/oracle
已连接。

SQL> create table staff_external_tb
  2  (
  3    staffno number(5),
  4    job varchar2(200),
  5    salary number(7, 2)
  6  )
  7  organization external
  8  (
  9    type ORACLE_LOADER
 10    default directory staff_data_dir
 11    access parameters
 12    (
 13      records delimited by newline
 14      badfile staff_baddata_dir:'bad%a_%p.txt'
 15      logfile staff_log_dir:'log%a_%p.txt'
 16      fields terminated by ','
 17      missing field values are null
 18      (staffno, job, salary)
 19    )
 20    location ('staff1.txt', 'staff2.txt')
 21  )
 22  parallel
 23  reject limit unlimited;

表已创建。
已用时间:  00: 00: 00.01

注意到这个EXTERNAL TABLE的数据加载规则有一项是salary number(7, 2),因此上述文件中的salary数字长度超过7的将被忽略并报错。

4,插入数据到实际表

切换当前session以并行方式工作

1
2
3
4
SQL> alter session enable parallel DML;

会话已更改。
已用时间:  00: 00: 00.00

导入表

1
2
3
SQL> insert into staff select * from staff_external_tb;

已创建10行。

两个数据文件总共16条记录,导入数据库的有效记录为10个。

参看产生的baddata文件如下:

1
2
3
4
5
6
100, 'CEO', 1000000.99
101, 'CFO', 200000.99
102, 'CIO', 100000.99
200, 'Jerry', 99991000000.99
201, 'Jim', 200000.99
202, 'Tom', 100000.99

错误日志如下:

1
2
3
4
5
6
7
8
9
10
11
12
error processing column SALARY in row 1 for datafile D:\oracle\zigzag\staffdata\staff1.txt
ORA-01438: ?????????????
error processing column SALARY in row 2 for datafile D:\oracle\zigzag\staffdata\staff1.txt
ORA-01438: ?????????????
error processing column SALARY in row 3 for datafile D:\oracle\zigzag\staffdata\staff1.txt
ORA-01438: ?????????????
error processing column SALARY in row 1 for datafile D:\oracle\zigzag\staffdata\staff2.txt
ORA-01438: ?????????????
error processing column SALARY in row 2 for datafile D:\oracle\zigzag\staffdata\staff2.txt
ORA-01438: ?????????????
error processing column SALARY in row 3 for datafile D:\oracle\zigzag\staffdata\staff2.txt
ORA-01438: ?????????????

staff_external_tb并不真正持有数据内容,select * from staff_external_tb取得的数据是通过ORACLE_LOADER驱动读取外部文件而来。 除ORACLE_LOADER意外,Oracle还支持ORACLE_DATAPUMP,读取由expdp导出的dump二进制文件

Temporary Table

临时表只存在于某个session或者transaction中,一旦session或者transaction结束,当前session或者transactoin表中的数据会自动清空。何时清空由ON COMMIT参数指定。session或者transaction之间的临时表是互相独立的。truncate一个临时表不会影响另外session中的临时表。表中的数据存储在当前用户的临时表空间,不驻数据文件。

  • ON COMMIT DELETE ROWS:transaction一旦commit,oracle会truncate这个临时表
  • ON COMMIT PRESERVE ROWS:session一旦结束,truncate临时表

举例:创建一个基于session的临时表

1
2
3
4
5
6
7
8
9
SQL> create global temporary table staff_temp(
  2    staffno number(5) primary key,
  3    job varchar2(200),
  4    salary number(7, 2)
  5  )
  6  on commit preserve rows;

表已创建。
已用时间:  00: 00: 00.17

临时表插入10行数据

1
2
3
4
SQL> insert into staff_temp select * from staff_external_tb;

已创建10行。
已用时间:  00: 00: 00.45

注意到在当前session中,临时表记录有10笔。接下来断开session

断开session并重新连接

1
2
3
4
5
6
7
8
9
10
11
12
SQL> conn
请输入用户名:  myuser
已连接。
SQL> select count(*) from staff_temp;

  COUNT(*)
----------
		 0

已选择 1 行。

已用时间:  00: 00: 00.03

可见staff_temp已被truncate

Index-Organized Table

有别于Heap-Organized,Index-Organized将数据和索引都保存在B-tree的叶子节点中,因此选择操作十分快,同时由于索引和数据存储在同一个B-Tree表中,磁盘大幅节省,而insert/delete/update将十分缓慢。比如insert操作,根据B-Tree组织,需要找到合适的位置插入数据,当无法占位时,需要在末端的叶子节点处创建一个额外的溢出块来存储,因此I/O比较慢。适合简单的通过主键来进行select的只读表。

创建Index-Organized Table,只需要通过参数 ORGANIZATION INDEX指明。

This post is licensed under CC BY 4.0 by the author.