对比shrink和move-db_name,instance_name,service_names,db_...-Oracle SQL多表查询

当前位置: 数据库>oracle

本页文章导读:

▪对比shrink和move 最近整理了一下系统内所有表的空间占用情况，发现其中一些表其实只有及万行，却占用了非常大的空间，那么显而易见，这个表的“碎片”实在是太多了。 &nb.........

▪db_name,instance_name,service_names,db_domain,dbid,oracle_sid等区别与联系最近整理了一篇文章：oracle listener 有网友对数据库是否显式设置了instance_name和service_names提出疑问。由此引发出db_name,instance_name,oracle_sid等等这些常见的参数都代表什么意思，怎么取值的，.........

▪Oracle SQL多表查询曾经一段时间我对oracle的多表查询搞的云里雾里，究其原因：oracle自己的语法和SQL国际标准语法混用。此文章仅适合oracle 菜鸟，老鸟直接飞过… 多表连接类型（SQL 1999标准） • Cross joins .........

[1]对比shrink和move

来源: 发布时间: 2013-10-29

最近整理了一下系统内所有表的空间占用情况，发现其中一些表其实只有及万行，却占用了非常大的空间，那么显而易见，这个表的“碎片”实在是太多了。

最开始接触oracle的时候我认为删除数据就是delete，但是后来的学习中，发现事情并不是我想象的那么简单，delete之后，系统也只是将这部分数据块置为可写状态，而实际上还是将这部分空间交由表来占用。而我发现我们系统中为了提高插入的效率，大量的使用了append方式，这样就更加积重难返了。

可以设计下面的实验：

有两个表test1和test2。

create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
delete from test1;
commit;
delete from test2;
commit;
insert /*+append*/ into test1 select * from dba_objects;
commit;
insert into test2 select * from dba_objects;
commit;

　　这样子，test1里就会有“碎片”了，而TEST2则是一个比较健康的表。占用空间为test1:17M，TEST2:9M。

现在就可以对比对比执行计划了：

analyze table test1 compute statistics;
analyze table test2 compute statistics;

一样大的两张表，执行计划却有比较大的差异，在这种小数据量的情况下尚能看出差异，那么在生产系统中动辄上百万千万的数据量，这个效率差异会更加明显。

这些表在我们的系统中会每天都被delete一次，delete的效率也严重的被“碎片”影响着：

delete * from test1;

delete * from test2;

其实这个和上面的select是一样的，都是全表扫描，索引COST基本上和刚才的select语句一样。

这里写到的东西大部分在http://www.cnblogs.com/wingsless/archive/2013/03/13/2957554.html这里都写过了，这里就想说明一下对delete也有影响，也顺便在后面说几句shrink。

以前单位的总部曾经来过一个专家给我们指导过工作，告诉我们，有些表应该shrink一下了。之前已经说了，shrink的好处就是不会改变rowid，所以索引不会失效。还是刚才的test1，加上索引，进行shrink操作：

alter table test1 enable row movement;
alter table test1 shrink space;

这样操作之后test1占用的空间就只有8.25M，再看看索引的状态：VALID。这是个很不错的消除表“碎片”的办法，值得在以后的工作中使用之。

本文链接

[2]db_name,instance_name,service_names,db_domain,dbid,oracle_sid等区别与联系

来源: 发布时间: 2013-10-29

最近整理了一篇文章：oracle listener 有网友对数据库是否显式设置了instance_name和service_names提出疑问。

由此引发出db_name,instance_name,oracle_sid等等这些常见的参数都代表什么意思，怎么取值的，有什么区别？

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert             string
db_name                               string       ORCL
db_unique_name                    string       ORCL
global_names                         boolean    FALSE
instance_name                       string       ORCL
lock_name_space                   string
log_file_name_convert              string
processor_group_name            string
service_names                        string       ORCL

看到这么多参数，但是服务器参数(spfile)中仅仅设置了db_name那么其它的name比如db_unique_name,instance_name,service_names的值是怎么出来的？

官方是这样说的：When an Oracle instance starts, it reads initialization parameters from an initialization parameter file. This file must at a minimum specify the DB_NAME parameter. All otherparameters have default values.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> create pfile='/u01/pfile.ora' from spfile;

File created.

[oracle@resoft u01]$ vi pfile.ora

ORCL.__db_cache_size=243269632
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=293601280
ORCL.__sga_target=553648128
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=289406976
ORCL.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=847249408
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

各种name或者id的解释：--括弧内中文名称是我们一致认为比较合理的翻译，但是如果想准确的表达，请直接说英文名称，不要去翻译，以免造成误解。

db_name(数据库名)

Property                                                Description
Parameter type                                      String
Syntax                                                  DB_NAME = database_name
Default value                                          There is no default value.
Modifiable                                              No
Basic                                                    Yes
Oracle RAC                                           You must set this parameter for every instance. Multiple instances must have the same value, or the same value must be

specified in the STARTUP OPEN SQL*Plus command or the ALTER DATABASE MOUNT SQL statement.

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database name in the control file are different, the database does not start.

db_name必须是一个不超过8个字符的文本串。在数据库创建过程中，db_name被记录在数据文件，日志文件和控制文件中。如果数据库实例启动过程中参数文件中的db_name和控制文件中的db_name名称不一样，则数据库不能启动。db_name是最具有稳定意义的参数，官网说不能修改，修改后会导致数据库不能启动。

DB_NAME 也就是数据库的名字标示。这里，数据库里可能有多个实例，比如RAC里的多节点，这多个节点是不同的实例，但是却有相同的名字，他们的 DB_NAME是相同的但是Instance_name是不同的。DB_NAME会保持在数据文件头里，所以更改DB_NAME不能仅仅修改parameter，还需要用nid 来进行更改，并且更改后还需要手工做些工作，是其生效。

db_unique_name(数据库唯一名)

Property                                                Description
Parameter type                                      String
Syntax        &

[3]Oracle SQL多表查询

来源: 发布时间: 2013-10-29

曾经一段时间我对oracle的多表查询搞的云里雾里，究其原因：oracle自己的语法和SQL国际标准语法混用。此文章仅适合oracle 菜鸟，老鸟直接飞过…

多表连接类型（SQL 1999标准）

• Cross joins

• Natural joins

• USING clause

• Full (or two-sided) outer joins

• Arbitrary join conditions for outer joins

SQL1999语法：

SELECT table1.column, table2.column

FROM table1

[CROSS JOIN table2] |

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON (table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)]|

[CROSS JOIN table2];

语法解释：

table1.column --指明从中检索数据的表和列

CROSS JOIN --返回两个表的笛卡尔集

NATURAL JOIN --根据相同的列名连接两个表

JOIN table

USING column_name --根据列名执行等值连接

JOIN table ON

table1.column_name --根据ON 子句中的条件执行等值连接

= table2.column_name

LEFT/RIGHT/FULL OUTER

一般来说，从数据显示方式来讲，分为内连接和外连接

内连接：只返回满足连接条件的数据。

外连接：除了返回满足连接条的行以外，还返回左（右）表中，不满足条件的行，

称为左（右）连接

演示，主要以SQL标准为主，oracle 写法作对比。示例用户为scott、HR

解锁这两个用户语句：

alter user scott identified by tiger account unlock;

alter user hr identified by hr account unlock;

内连接

--Oracle的写法

select empno,ename,sal,dname,loc from emp,dept

where emp.deptno=dept.deptno;

-- SQL 99标准的写法

select empno,ename,job,sal,dept.deptno,dname,loc

from emp join dept on emp.deptno=dept.deptno;

或把join改为inner join

外连接

左外连接

Oracle 外连接语法：

SELECT table1.column, table2.column --右外连接

FROM table1, table2

WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column --左外连接

FROM table1, table2

WHERE table1.column = table2.column(+);

SQL 1999标准外连接语法见上面SQL1999语法

--Oracle的写法

外连接的符号是（＋），（＋）要放在字段名后。（＋）对面的那个表，会全部显示。

左外连接时，加号在等号的右边

SQL> select d.dname,e.ename,e.deptno

from dept d,emp e

where d.deptno = e.deptno(+)

order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected

-- SQL 99标准的写法接

SQL> select d.dname,e.ename,e.deptno

from dept d

left join emp e

on d.deptno = e.deptno

order by d.deptno;

DNAME ENAME DEPTNO

-------------- ---------- ------

ACCOUNTING CLARK 10

ACCOUNTING KING 10

ACCOUNTING MILLER 10

RESEARCH JONES 20

RESEARCH FORD 20

RESEARCH ADAMS 20

RESEARCH SMITH 20

RESEARCH SCOTT 20

SALES WARD 30

SALES TURNER 30

SALES ALLEN 30

SALES JAMES 30

SALES BLAKE 30

SALES MARTIN 30

OPERATIONS

15 rows selected

右外连接

--oracle的写法

SQL> select empno, ename, job, sal, dept.deptno, dname, loc

2 from emp, dept

3 where emp.deptno(+) = dept.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

40 OPERATIONS BOSTON

15 rows selected

--SQL1999标准写法

SQL> select empno, ename, job, sal, dept.deptno, dname, loc

2 from emp

3 right join dept

4 on emp.deptno = dept.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

40 OPERATIONS BOSTON

15 rows selected

全连接

--SQL1999标准写法

SQL> select empno, ename, job, sal, d.deptno, dname, loc

2 from emp e

3 full join dept d

4 on e.deptno = d.deptno;

EMPNO ENAME JOB SAL DEPTNO DNAME LOC

----- ---------- --------- --------- ------ -------------- -------------

7369 SMITH CLERK 800.00 20 RESEARCH DALLAS

7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO

7521 WARD SALESMAN 1250.00 30 SALES CHICAGO

7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS

7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO

7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO

7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK

7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS

7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK

7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO

7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS

7900 JAMES CLERK 950.00 30 SALES CHICAGO

7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS

7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK

40 OPERATIONS BOSTON

15 rows selected

自连接

把表自身的镜像当成另外一个表

--oracle 的写法

SQL> select e.ename || ' works for ' || m.ename

2 from emp e, emp m

3 where e.empno = m.mgr;

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

JONES works for FORD

JONES works for SCOTT

BLAKE works for TURNER

BLAKE works for ALLEN

BLAKE works for WARD

BLAKE works for JAMES

BLAKE works for MARTIN

CLARK works for MILLER

SCOTT works for ADAMS

KING works for BLAKE

KING works for JONES

KING works for CLARK

FORD works for SMITH

13 rows selected

--SQL1999标准的写法

SQL> select e.ename || ' works for ' || m.ename

2 from emp e

3 join emp m

4 on e.empno = m.mgr;

E.ENAME||'WORKSFOR'||M.ENAME

-------------------------------

JONES works for FORD

JONES works for SCOTT

BLAKE works for TURNER

BLAKE works for ALLEN

BLAKE works for WARD

BLAKE works for JAMES

BLAKE works for MARTIN

CLARK works for MILLER

SCOTT works for ADAMS

KING works for BLAKE

KING works for JONES

KING works for CLARK

FORD works for SMITH

13 rows selected

自然连接

以两个表具有相同的字段的所有列为基础，可采用自然连接（natural join)

它将选择两个表中那些在所有匹配的列中值相等的行。

如果列具有相同的名称，但数据类型能够不同，则会报错。

--SQL1999标准写法

SQL> select empno, ename