第十二章： 表(1)
1、表的功能：存储、管理数据的基本单元（二维表：有行和列组成）
2、表的类型： 1）普通表：heap table ：数据存储时，无序的，对它的访问采用全表扫描。 2）分区表：（>2G)对大表进行优化 （Range Partitioning，List PartitioningHash Partitioning，Composite Partitioning） ---------按range 建立分区表 10:50:38 SQL> CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) 10:51:11 7 ( 10:51:11 8 lespace user01, PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('04/01/2011','MM/DD/YYYY')) tablespace user01, PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('07/01/2011','MM/DD/YYYY')) tablespace user02, PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('10/01/2011','MM/DD/YYYY')) tablespace user03, PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('01/01/2012','MM/DD/YYYY')) tablespace user04 10:51:11 12 );Table created.
10:51:12 SQL>
10:55:19 SQL> insert into sales_range values ( 1001,'tom',1000,'2011-02-01');
1 row created.
10:55:34 SQL> insert into sales_range values ( 1002,'jerry',1000,'2011-05-01');
1 row created.
10:55:43 SQL> insert into sales_range values ( 1003,'rose',1000,'2011-08-01');
1 row created.
10:55:55 SQL> insert into sales_range values ( 1004,'john',1000,'2011-01-01');
1 row created.
10:56:04 SQL> select * from sales_range partition (sales_jan2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1001 tom 1000 2011-02-01 00:00:00 1004 john 1000 2011-01-01 00:00:0010:56:09 SQL> insert into sales_range values ( 1005,'john',1000,'2011-11-01');
1 row created.
10:56:34 SQL> select * from sales_range partition (sales_jan2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1001 tom 1000 2011-02-01 00:00:00 1004 john 1000 2011-01-01 00:00:0010:56:36 SQL> select * from sales_range partition (sales_feb2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1002 jerry 1000 2011-05-01 00:00:0010:56:40 SQL> select * from sales_range partition (sales_mar2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1003 rose 1000 2011-08-01 00:00:0010:56:47 SQL> select * from sales_range partition (sales_apr2000);
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1005 john 1000 2011-11-01 00:00:0010:56:52 SQL> select * from sales_range ;
SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ ------------------- 1001 tom 1000 2011-02-01 00:00:00 1004 john 1000 2011-01-01 00:00:00 1002 jerry 1000 2011-05-01 00:00:00 1003 rose 1000 2011-08-01 00:00:00 1005 john 1000 2011-11-01 00:00:0010:57:08 SQL>
3）索引组织表（IOT)（如果表经常以主键为索引查询，可建立索引组织表，加快表的访问速度）
建立索引组织表 04:33:59 SQL> create table sales_info( 04:34:11 2 onstraid number(6) primary key, 04:34:55 3 customer_name varchar2(30) ,slaes_amount number(10,2), 04:35:26 4 sales_date date,remark varchar2(2000) 04:35:56 5 ) organization index tablespace users01 04:36:28 6 pctthreshold 20 including remark 04:36:46 7 overflow tablespace users02;Table created.
定义索引表时，主键约束和ORGANIZATION index 选项必须指定。 PCTTHRESHOLD :用于指定数据块中为键列和部分非键列数据所预留空间的百分比；如果数据块剩余空间低于PCTTHRESHOLD 设置，Oracle会将其他数据存放到溢出段。 INCLUDING column ：用于指定数据被存放到溢出段的起始列。 OVERFLOW TABLESPACE：用于指定溢出段所在的表空间。 4）簇：cluster （多表链接查询） 1)建立簇 09:31:25 SQL> create cluster dept_emp_clu (deptno number(3)) 09:33:19 2 pctfree 20 pctused 60 09:33:31 3 size 500 tablespace users01;Cluster created.
2）建立簇表 09:33:56 SQL> create table department( 09:34:21 2 id number(3) primary key, 09:34:40 3 dname varchar(2) ,loc varchar2(13)) 09:35:07 4 cluster dept_emp_clu(id);Table created.
09:35:24 SQL> create table employee(
09:36:00 2 eno number(4) primary key , 09:36:21 3 ename varchar2(10), 09:36:34 4 job varchar2(9), 09:36:50 5 mgr number(4), 09:36:59 6 hiredate date, 09:37:05 7 sal number(7,2), 09:37:14 8 comm number(7,2), 09:37:26 9 dept_id number(3) references department 09:38:09 10 ) cluster dept_emp_clu(dept_id);Table created.
3）建立索引 09:38:31 SQL> create index dept_emp_idx on cluster dept_emp_clu 09:39:12 2 tablespace users02;Index created.
----------------cluster访问和普通表连接查询访问 对比 22:27:03 SQL> select e.eno,e.ename,e.sal,d.id,d.dname,d.loc from employee e,department d 22:27:08 2 where e.dept_id=d.id;ENO ENAME SAL ID DNAME LOC
---------- ---------- ---------- ---------- -------------------- ------------- 7782 CLARK 2450 10 ACCOUNTING NEW YORK 7839 KING 5000 10 ACCOUNTING NEW YORK 7934 MILLER 1300 10 ACCOUNTING NEW YORK 7369 SMITH 800 20 RESEARCH DALLAS 7566 JONES 2975 20 RESEARCH DALLAS 7788 SCOTT 3000 20 RESEARCH DALLAS 7876 ADAMS 1100 20 RESEARCH DALLAS 7902 FORD 3000 20 RESEARCH DALLAS 7499 ALLEN 1600 30 SALES CHICAGO 7521 WARD 1250 30 SALES CHICAGO 7654 MARTIN 1250 30 SALES CHICAGO 7698 BLAKE 2850 30 SALES CHICAGO 7844 TURNER 1500 30 SALES CHICAGO 7900 JAMES 950 30 SALES CHICAGO14 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 1419571889--------------------------------------------
| Id | Operation | Name | -------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | DEPARTMENT | | 3 | TABLE ACCESS CLUSTER| EMPLOYEE | --------------------------------------------Note
----- - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 1042 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) 14 rows processed22:27:09 SQL> select e.empno,e.ename,e.sal,d.dname,d.dname,d.loc from emp e,dept d
22:27:35 2 where e.deptno=d.deptno;EMPNO ENAME SAL DNAME DNAME LOC
---------- ---------- ---------- -------------- -------------- ------------- 7369 SMITH 800 RESEARCH RESEARCH DALLAS 7499 ALLEN 1600 SALES SALES CHICAGO 7521 WARD 1250 SALES SALES CHICAGO 7566 JONES 2975 RESEARCH RESEARCH DALLAS 7654 MARTIN 1250 SALES SALES CHICAGO 7698 BLAKE 2850 SALES SALES CHICAGO 7782 CLARK 2450 ACCOUNTING ACCOUNTING NEW YORK 7788 SCOTT 3000 RESEARCH RESEARCH DALLAS 7839 KING 5000 ACCOUNTING ACCOUNTING NEW YORK 7844 TURNER 1500 SALES SALES CHICAGO 7876 ADAMS 1100 RESEARCH RESEARCH DALLAS 7900 JAMES 950 SALES SALES CHICAGO 7902 FORD 3000 RESEARCH RESEARCH DALLAS 7934 MILLER 1300 ACCOUNTING ACCOUNTING NEW YORK14 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 351108634----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 518 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------4 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics ---------------------------------------------------------- 139 recursive calls 0 db block gets 59 consistent gets 3 physical reads 0 redo size 1257 bytes sent via SQL*Net to client 384 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 14 rows processed22:27:43 SQL>
3、 rowid ：行号（伪列）精确的定义记录的物理位置
extended rowid： object id （6），relative file id（3），block id （6），row id（3）
分析rowid ---------普通表和簇表的rowid ，对比 22:31:27 SQL> select rowid ,ename,sal from emp;ROWID ENAME SAL
------------------ ---------- ---------- AAACYCAAEAAAAAfAAA SMITH 800 AAACYCAAEAAAAAfAAB ALLEN 1600 AAACYCAAEAAAAAfAAC WARD 1250 AAACYCAAEAAAAAfAAD JONES 2975 AAACYCAAEAAAAAfAAE MARTIN 1250 AAACYCAAEAAAAAfAAF BLAKE 2850 AAACYCAAEAAAAAfAAG CLARK 2450 AAACYCAAEAAAAAfAAH SCOTT 3000 AAACYCAAEAAAAAfAAI KING 5000 AAACYCAAEAAAAAfAAJ TURNER 1500 AAACYCAAEAAAAAfAAK ADAMS 1100 AAACYCAAEAAAAAfAAL JAMES 950 AAACYCAAEAAAAAfAAM FORD 3000 AAACYCAAEAAAAAfAAN MILLER 130014 rows selected.
22:31:31 SQL> select rowid,deptno,dname from dept;
ROWID DEPTNO DNAME
------------------ ---------- -------------- AAACYAAAEAAAAAPAAA 10 ACCOUNTING AAACYAAAEAAAAAPAAB 20 RESEARCH AAACYAAAEAAAAAPAAC 30 SALES AAACYAAAEAAAAAPAAD 40 OPERATIONS22:31:42 SQL> select rowid,id,dname from department;
ROWID ID DNAME
------------------ ---------- -------------------- AAACZNAAEAAAABWAAA 10 ACCOUNTING AAACZNAAEAAAABWAAB 20 RESEARCH AAACZNAAEAAAABWAAC 30 SALES AAACZNAAEAAAABWAAD 40 OPERATIONS22:31:58 SQL> select rowid ,eno,ename,sal from employee;
ROWID ENO ENAME SAL
------------------ ---------- ---------- ---------- AAACZNAAEAAAABWAAA 7369 SMITH 800 AAACZNAAEAAAABWAAB 7499 ALLEN 1600 AAACZNAAEAAAABWAAC 7521 WARD 1250 AAACZNAAEAAAABWAAD 7566 JONES 2975 AAACZNAAEAAAABWAAE 7654 MARTIN 1250 AAACZNAAEAAAABWAAF 7698 BLAKE 2850 AAACZNAAEAAAABWAAG 7782 CLARK 2450 AAACZNAAEAAAABWAAH 7788 SCOTT 3000 AAACZNAAEAAAABWAAI 7839 KING 5000 AAACZNAAEAAAABWAAJ 7844 TURNER 1500 AAACZNAAEAAAABWAAK 7876 ADAMS 1100 AAACZNAAEAAAABWAAL 7900 JAMES 950 AAACZNAAEAAAABWAAM 7902 FORD 3000 AAACZNAAEAAAABWAAN 7934 MILLER 130014 rows selected.
DECLARE v_rowid_type NUMBER;
v_OBJECT_NUMBER NUMBER; v_RELATIVE_FNO NUMBER; v_BLOCK_NUMBERE_FNO NUMBER; v_ROW_NUMBER NUMBER; BEGIN DBMS_ROWID.rowid_info(rowid_in=>'&num', rowid_type =>v_rowid_type, object_number =>v_OBJECT_NUMBER, relative_fno =>v_RELATIVE_FNO, block_number =>v_BLOCK_NUMBERE_FNO, ROW_NUMBER =>v_ROW_NUMBER); DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type)); DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER)); DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO)); DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO)); DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER)); END; /Enter value for num: old 7: DBMS_ROWID.rowid_info(rowid_in=>'&num',
new 7: DBMS_ROWID.rowid_info(rowid_in=>'',PL/SQL procedure successfully completed.
22:36:48 SQL> set serverout on
22:36:53 SQL> / Enter value for num: AAACZNAAEAAAABWAAA old 7: DBMS_ROWID.rowid_info(rowid_in=>'&num', new 7: DBMS_ROWID.rowid_info(rowid_in=>'AAACZNAAEAAAABWAAA', ROWID_TYPE:1 OBJECT_NUMBER:9805 RELATIVE_FNO:4 BLOCK_NUMBER:86 ROW_NUMBER:0PL/SQL procedure successfully completed
DECLARE v_rowid_type NUMBER; v_OBJECT_NUMBER NUMBER; v_RELATIVE_FNO NUMBER; v_BLOCK_NUMBERE_FNO NUMBER; DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO)); v_ROW_NUMBER NUMBER; BEGIN DBMS_ROWID.rowid_info(rowid_in=>'&num', rowid_type =>v_rowid_type, object_number =>v_OBJECT_NUMBER, relative_fno =>v_RELATIVE_FNO, block_number =>v_BLOCK_NUMBERE_FNO, ROW_NUMBER =>v_ROW_NUMBER); DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type)); DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER)); DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO)); DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO)); DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER)); END; 22:38:10 19 / Enter value for num: AAACZNAAEAAAABWAAM old 7: DBMS_ROWID.rowid_info(rowid_in=>'&num', new 7: DBMS_ROWID.rowid_info(rowid_in=>'AAACZNAAEAAAABWAAM', ROWID_TYPE:1 OBJECT_NUMBER:9805 RELATIVE_FNO:4 BLOCK_NUMBER:86 ROW_NUMBER:12PL/SQL procedure successfully completed.
-------------在建立cluster的表，通过rowid，可以看到不同的表的记录放在了相同的block 上 22:32:12 SQL> 11:29:43 SQL> 1 DECLARE v_rowid_type NUMBER; 2 v_OBJECT_NUMBER NUMBER; 3 v_RELATIVE_FNO NUMBER; 4 v_BLOCK_NUMBERE_FNO NUMBER; 5 v_ROW_NUMBER NUMBER; 6 BEGIN 7 DBMS_ROWID.rowid_info(rowid_in=>'&num', 8 rowid_type =>v_rowid_type, 9 object_number =>v_OBJECT_NUMBER, 10 relative_fno =>v_RELATIVE_FNO, 11 block_number =>v_BLOCK_NUMBERE_FNO, 12 ROW_NUMBER =>v_ROW_NUMBER); 13 DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type)); 14 DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER)); 15 DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO)); 16 DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO)); 17 DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER)); 18* END; / Enter value for num: AAAMfMAAEAAAAAgAAB old 7: DBMS_ROWID.rowid_info(rowid_in=>'&num', new 7: DBMS_ROWID.rowid_info(rowid_in=>'AAAMfMAAEAAAAAgAAB', ROWID_TYPE:1 OBJECT_NUMBER:51148 RELATIVE_FNO:4 BLOCK_NUMBER:32 ROW_NUMBER:1PL/SQL procedure successfully completed.
11:31:54 SQL> 1 select object_name,object_id,object_type ,status from user_objects 2* where object_name='EMP'OBJECT_NAME OBJECT_ID OBJECT_TYPE STATUS
------------------------------ ---------- ------------------- ---------- EMP 51148 TABLE VALID11:32:56 SQL> COL SEGMENT_NAME FOR A30
11:33:02 SQL> 1 select segment_name,tablespace_name,file_id,block_id from dba_extents 2* where segment_name='EMP'SEGMENT_NAME TABLESPACE_NAME FILE_ID BLOCK_ID
------------------------------ ------------------------------ ---------- ---------- EMP USERS 4 2511:33:02 SQL>
11:33:55 SQL> select segment_name,tablespace_name,file_id,block_id,EXTENT_ID,BYTES/1024 from dba_extents
11:34:25 2 where segment_name='EMP';SEGMENT_NAME TABLESPACE_NAME FILE_ID BLOCK_ID EXTENT_ID BYTES/1024
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- EMP USERS 4 25 0 6411:34:27 SQL>