SELECT
语句
一个查询语句在逻辑上的处理方式可能会与实际物理处理过程不同。Oracle 基于查询成本的优化器用来产生实际的
执行计划。
查询语句的逻辑处理顺序
5 | Select <column list> |
1 | from |
1.1 | from join on |
2 | where |
3 | group by |
4 | having |
6 | order by |
可以把处理过程中的每一个步骤想象为生成一个临时的数据集。随着每个处理步骤的进行,这个数据集被不断的操作。
FROM
注意在处理联接语句时按下面的顺序进行:
交叉联接
内联接
外联接
一般,排序是查询过程中开销相当大的一个处理,尤其是当数据量很大时。
INSERT
INSERT
INTO
HR.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES
(
'IT_PM'
,
'Project Manager'
,
5000
,
11000
)
INSERT
INTO
SCOTT.BONUS
(ENAME, JOB, SAL)
SELECT
NAME
, JOB, SAL *
0.1
FROM
SCOTT.EMP;
INSERT
ALL
WHEN
SUM_ORDERS <
10000
THEN
INTO
SMALL_CUSTOMERS
WHEN
SUM_ORDERS >=
10000
AND
SUM_ORDERS <
100000
THEN
INTO
MEDIUM_CUSTOMERS
ELSE
INTO
LARGE_CUSTOMERS
SELECT
CUSTOMER_ID,
SUM
( ORDER_TOTAL) SUM_ORDERS
FROM
OE.ORDERS
GROUP
BY
CUSTOMER_ID;
UPDATE
UPDATE
EMPLOYEES
SET
SALARY = SALARY *
1.1
WHERE
DEPARTMENT_ID =
90
;
UPDATE
EMPLOYEES
SET
SALARY =
(
SELECT
EMPLOYEES2.SALARY
FROM
EMPLOYEES2
WHERE
EMPLOYEES2.EMPLID =
1
)
MERGE
MERGE
INTO
DEPT60_BONUSES B
USING
(
SELECT
EMPLID , SALARY, DEPTID
FROM
EMPLOYEES
WHERE
DEPTID =
60
) E
ON
(B.EMPLID = E.EMPLID)
WHEN
MATCHED
THEN
UPDATE
SET
B.BONUS_AMT = E.SALARY *
0.2
WHERE
B.BONUS_AMT =
0
DELETE
WHERE
( E.SALARY >
7500
)
WHEN
NOT
MATCHED
THEN
INSERT
(B.EMPLID, B.BONUS_AMT)
VALUES
(E.EMPLID, E.SALARY *
0.1
)
WHERE
(E.SALARY <
7500
);