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