Oracle左连接和右连接实现方式

 

在Oracle PL-SQL中，左连接和右连接以如下方式来实现

查看如下语句：

hr@ORCL> select last_name, department_name

  2  from employees, departments

  3  where employees.department_id(+)=departments.department_id;

 

LAST_NAME                 DEPARTMENT_NAME

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

Whalen                    Administration

Hartstein                 Marketing

Fay                       Marketing

Raphaely                  Purchasing

Khoo                      Purchasing

Baida                     Purchasing

Tobias                    Purchasing

Himuro                    Purchasing

Colmenares                Purchasing

Mavris                    Human Resources

Weiss                     Shipping

Fripp                     Shipping

Kaufling                  Shipping

Vollman                   Shipping

Mourgos                   Shipping

Nayer                     Shipping

Mikkilineni               Shipping

Landry                    Shipping

Markle                    Shipping

Bissot                    Shipping

Atkinson                  Shipping

Marlow                    Shipping

Olson                     Shipping

Mallin                    Shipping

Rogers                    Shipping

Gee                       Shipping

Philtanker                Shipping

Ladwig                    Shipping

Stiles                    Shipping

Seo                       Shipping

Patel                     Shipping

Rajs                      Shipping

Davies                    Shipping

Matos                     Shipping

Vargas                    Shipping

Taylor                    Shipping

Fleaur                    Shipping

Sullivan                  Shipping

Geoni                     Shipping

Sarchand                  Shipping

Bull                      Shipping

Dellinger                 Shipping

Cabrio                    Shipping

Chung                     Shipping

Dilly                     Shipping

Gates                     Shipping

Perkins                   Shipping

Bell                      Shipping

Everett                   Shipping

McCain                    Shipping

Jones                     Shipping

Walsh                     Shipping

Feeney                    Shipping

OConnell                  Shipping

Grant                     Shipping

Hunold                    IT

Ernst                     IT

Austin                    IT

Pataballa                 IT

Lorentz                   IT

Baer                      Public Relations

Russell                   Sales

Partners                  Sales

Errazuriz                 Sales

Cambrault                 Sales

Zlotkey                   Sales

Tucker                    Sales

Bernstein                 Sales

Hall                      Sales

Olsen                     Sales

Cambrault                 Sales

Tuvault                   Sales

King                      Sales

Sully                     Sales

McEwen                    Sales

Smith                     Sales

Doran                     Sales

Sewall                    Sales

Vishney                   Sales

Greene                    Sales

Marvins                   Sales

Lee                       Sales

Ande                      Sales

Banda                     Sales

Ozer                      Sales

Bloom                     Sales

Fox                       Sales

Smith                     Sales

Bates                     Sales

Kumar                     Sales

Abel                      Sales

Hutton                    Sales

Taylor                    Sales

Livingston                Sales

Johnson                   Sales

King                      Executive

Kochhar                   Executive

De Haan                   Executive

Greenberg                 Finance

Faviet                    Finance

Chen                      Finance

Sciarra                   Finance

Urman                     Finance

Popp                      Finance

Higgins                   Accounting

Gietz                     Accounting

                          Treasury

                          Corporate Tax

                          Control And Credit

                          Shareholder Services

                          Benefits

                          Manufacturing

                          Construction

                          Contracting

                          Operations

                          IT Support

                          NOC

                          IT Helpdesk

                          Government Sales

                          Retail Sales

                          Recruiting

                          Payroll

 

122 rows selected.

此SQL使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，也就是说上例中无论会不会出现某个部门没有一个员工的情况，这个部门的名字都会在查询结果中出现。

反之：
hr@ORCL> select last_name, department_name

  2  from employees, departments

  3  where employees.department_id=departments.department_id(+);

 

LAST_NAME                 DEPARTMENT_NAME

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

King                      Executive

Kochhar                   Executive

De Haan                   Executive

Hunold                    IT

Ernst                     IT

Austin                    IT

Pataballa                 IT

Lorentz                   IT

Greenberg                 Finance

Faviet                    Finance

Chen                      Finance

Sciarra                   Finance

Urman                     Finance

Popp                      Finance

Raphaely                  Purchasing

Khoo                      Purchasing

Baida                     Purchasing

Tobias                    Purchasing

Himuro                    Purchasing

Colmenares                Purchasing

Weiss                     Shipping

Fripp                     Shipping

Kaufling                  Shipping

Vollman                   Shipping

Mourgos                   Shipping

Nayer                     Shipping

Mikkilineni               Shipping

Landry                    Shipping

Markle                    Shipping

Bissot                    Shipping

Atkinson                  Shipping

Marlow                    Shipping

Olson                     Shipping

Mallin                    Shipping

Rogers                    Shipping

Gee                       Shipping

Philtanker                Shipping

Ladwig                    Shipping

Stiles                    Shipping

Seo                       Shipping

Patel                     Shipping

Rajs                      Shipping

Davies                    Shipping

Matos                     Shipping

Vargas                    Shipping

Russell                   Sales

Partners                  Sales

Errazuriz                 Sales

Cambrault                 Sales

Zlotkey                   Sales

Tucker                    Sales

Bernstein                 Sales

Hall                      Sales

Olsen                     Sales

Cambrault                 Sales

Tuvault                   Sales

King                      Sales

Sully                     Sales

McEwen                    Sales

Smith                     Sales

Doran                     Sales

Sewall                    Sales

Vishney                   Sales

Greene                    Sales

Marvins                   Sales

Lee                       Sales

Ande                      Sales

Banda                     Sales

Ozer                      Sales

Bloom                     Sales

Fox                       Sales

Smith                     Sales

Bates                     Sales

Kumar                     Sales

Abel                      Sales

Hutton                    Sales

Taylor                    Sales

Livingston                Sales

Grant

Johnson                   Sales

Taylor                    Shipping

Fleaur                    Shipping

Sullivan                  Shipping

Geoni                     Shipping

Sarchand                  Shipping

Bull                      Shipping

Dellinger                 Shipping

Cabrio                    Shipping

Chung                     Shipping

Dilly                     Shipping

Gates                     Shipping

Perkins                   Shipping

Bell                      Shipping

Everett                   Shipping

McCain                    Shipping

Jones                     Shipping

Walsh                     Shipping

Feeney                    Shipping

OConnell                  Shipping

Grant                     Shipping

Whalen                    Administration

Hartstein                 Marketing

Fay                       Marketing

Mavris                    Human Resources

Baer                      Public Relations

Higgins                   Accounting

Gietz                     Accounting

 

107 rows selected.

则是左连接，无论这个员工有没有一个能在departments表中得到匹配的部门号，这个员工的记录都会被显示

有两个表T1和T2，两个表除了主键索引外均无其他索引，这两个表由T1.F1(主键)，T2.F2(主键)进行左连接，

SQL语句有两种写法：
1. SELECT * FROM T1,T2 WHERE T1.F1=T2.F2(+)
2. SELECT * FROM T1 LEFT JOIN T2 ON T1.F1=T2.F2

当查看1的执行计划时发现T1为全表扫描，T2为索引扫描。
当查看2的执行计划时发现两个表均为全表扫描。
在oracle9.2自带的SQL Referrence.pdf的第511页上有这样一句：
Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator.
前者选择的优化器是RULE，而后者选择的优化器是CBO的ALL ROWS。

 

接下来看两个面试题：

题目：表、视图结构转化
现有一个商品销售表sale，表结构为：
month　　　 char(6)　　　　　 –月份
sell　　　　number(10,2)　　　–月销售金额

现有数据为：
200001　　1000
200002　　1100
200003　　1200
200004　　1300
200005　　1400
200006　　1500
200007　　1600
200101　　1100
200202　　1200
200301　　1300

想要转化为以下结构的数据：
year　　　char(4)　　　　　 –年份
month1　　number(10,2)　　　–1月销售金额
month2　　number(10,2)　　　–2月销售金额
month3　　number(10,2)　　　–3月销售金额
month4　　number(10,2)　　　–4月销售金额
month5　　number(10,2)　　　–5月销售金额
month6　　number(10,2)　　　–6月销售金额
month7　　number(10,2)　　　–7月销售金额
month8　　number(10,2)　　　–8月销售金额
month9　　number(10,2)　　　–9月销售金额
month10　　number(10,2)　　　–10月销售金额
month11　　number(10,2)　　　–11月销售金额
month12　　number(10,2)　　　–12月销售金额

结构转化的SQL语句为：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
　　　　select
　　　　substrb(month,1,4),
　　　　sum(decode(substrb(month,5,2),’01’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’02’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’03’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’04’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’05’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’06’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’07’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’08’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’09’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’10’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’11’,sell,0)),
　　　　sum(decode(substrb(month,5,2),’12’,sell,0))
　　　　from sale
　　　　group by substrb(month,1,4);

以下是我的测试数据：

hr@ORCL> create table sale

  2  (month char(6), --month

  3   sell number(10,2) --month sales 

  4  );

 

Table created.

 

hr@ORCL> insert into sale values(200001,1000);

 

1 row created.

 

hr@ORCL> insert into sale values(200002,1100);

 

1 row created.

 

hr@ORCL> insert into sale values(200003,1200);

 

1 row created.

 

hr@ORCL> insert into sale values(200004,1300);

 

1 row created.

 

hr@ORCL> insert into sale values(200005,1400);

 

1 row created.

 

hr@ORCL> insert into sale values(200006,1500);

 

1 row created.

 

hr@ORCL> insert into sale values(200007,1600);

 

1 row created.

 

hr@ORCL> insert into sale values(200101,1100);

 

1 row created.

 

hr@ORCL> insert into sale values(200202,1200);

 

1 row created.

 

hr@ORCL> insert into sale values(200301,1300);

 

1 row created.

 

hr@ORCL> commit;

 

Commit complete.

 

hr@ORCL> select * from sale;  

 

MONTH        SELL

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

200001       1000

200002       1100

200003       1200

200004       1300

200005       1400

200006       1500

200007       1600

200101       1100

200202       1200

200301       1300

 

10 rows selected.

 

hr@ORCL> create or replace view

  2  v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

  3  as

  4  select

  5  substrb(month,1,4),

  6  sum(decode(substrb(month,5,2),'01',sell,0)),

  7  sum(decode(substrb(month,5,2),'02',sell,0)),

  8  sum(decode(substrb(month,5,2),'03',sell,0)),

  9  sum(decode(substrb(month,5,2),'04',sell,0)),

 10  sum(decode(substrb(month,5,2),'05',sell,0)),

 11  sum(decode(substrb(month,5,2),'06',sell,0)),

 12  sum(decode(substrb(month,5,2),'07',sell,0)),

 13  sum(decode(substrb(month,5,2),'08',sell,0)),

 14  sum(decode(substrb(month,5,2),'09',sell,0)),

 15  sum(decode(substrb(month,5,2),'09',sell,0)),

 16  sum(decode(substrb(month,5,2),'

 

hr@ORCL> create or replace view

  2  v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

  3  as

  4  select

  5  substrb(month,1,4),

  6  sum(decode(substrb(month,5,2),'01',sell,0)),

  7  sum(decode(substrb(month,5,2),'02',sell,0)),

  8  sum(decode(substrb(month,5,2),'03',sell,0)),

  9  sum(decode(substrb(month,5,2),'04',sell,0)),

 10  sum(decode(substrb(month,5,2),'05',sell,0)),

 11  sum(decode(substrb(month,5,2),'06',sell,0)),

 12  sum(decode(substrb(month,5,2),'07',sell,0)),

 13  sum(decode(substrb(month,5,2),'08',sell,0)),

 14  sum(decode(substrb(month,5,2),'09',sell,0)),

 15  sum(decode(substrb(month,5,2),'10',sell,0)),

 16  sum(decode(substrb(month,5,2),'11',sell,0)),

 17  sum(decode(substrb(month,5,2),'12',sell,0))

 18  from sale

 19  group by substrb(month,1,4);

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)

*

ERROR at line 2:

ORA-01031: insufficient privileges

 

新开一个session，赋予hr用户create view权限

sys@ORCL> grant create any view to hr;

 

Grant succeeded.

 

回到原来的session

hr@ORCL> /       

 

View created

 

hr@ORCL> set linesize 181

hr@ORCL> select * from v_sale;

 

YEAR     MONTH1     MONTH2     MONTH3     MONTH4     MONTH5     MONTH6     MONTH7     MONTH8     MONTH9    MONTH10    MONTH11    MONTH12

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

2000       1000       1100       1200       1300       1400       1500       1600          0          0          0          0          0

2001       1100          0          0          0          0          0          0          0          0          0          0          0

2003       1300          0          0          0          0          0          0          0          0          0          0          0

2002          0       1200          0          0          0          0          0          0          0          0          0          0

 

hr@ORCL>

 

题目：优化的策略一般包括:
• 内存优化
• 操作系统优化
• 数据存储的优化
• 网络优化等方法
具体到不同的数据库涉及到要调整不同的数据库配置文件、不同的操作系统参数、网络参数等等, 不同的数据库不同.