select文は、
1. from句
2. where句 (結合条件)
3. start with句
4. connect by句
5. where句 (行のフィルタ条件)
6. group by句
7. having句
8. model句
9. select句
10. union、minus、intersectなどの集合演算
11. order by句
の順に評価されます。
(Oracle12c新機能のFetch First句とOffSet句は、order by句の後に評価されます)
分析関数を使う時や、
select文の文法エラーを修正する時や、
select文のチューニングを行う時に、
select文の評価順序を知っていると役に立つことがあります。
select文を読みときは、
select文の評価順序で読むようにし、
select文を書くときは、
脳内では、select文を読むときと同じ順序とし、
エディタで記述するときは、select句から順番、
とすれば効率がいいでしょう。
■■■補足1 RowNumの採番■■■
where句の条件を満たした順に、RowNumが振られる
SQL> select Val,max(rownum)
2 from (select Val
3 from (select 1 as Val from dual
4 union all select 1 from dual
5 union all select 1 from dual
6 union all select 1 from dual
7 union all select 2 from dual
8 union all select 2 from dual
9 union all select 2 from dual
10 union all select 2 from dual
11 union all select 3 from dual
12 union all select 3 from dual
13 union all select 3 from dual)
14 order by dbms_random.random())
15 where Val in (2,3)
16 group by Val;
VAL MAX(ROWNUM)
--- -----------
2 6
3 7
■■■補足2 distinctは分析関数の後■■■
SQL> select distinct Val,count(*) over(order by Val) as c
2 from (select 1 as Val from dual
3 union all select 1 from dual
4 union all select 1 from dual
5 union all select 1 from dual
6 union all select 2 from dual
7 union all select 2 from dual
8 union all select 2 from dual
9 union all select 2 from dual
10 union all select 3 from dual
11 union all select 3 from dual
12 union all select 3 from dual);
VAL C
--- --
1 4
2 8
3 11
■■■補足3 distinctは分析関数の後、group byは分析関数の前■■■
SQL> select distinct ColA,ColB,Row_Number() over(order by 1) as Rn
2 from (select 1 as ColA,1 as ColB from dual
3 union all select 1,1 from dual
4 union all select 1,1 from dual
5 union all select 1,1 from dual
6 union all select 2,2 from dual
7 union all select 2,2 from dual
8 union all select 2,2 from dual)
9 order by 1,2,3;
ColA■ColB■Rn
----■----■--
1■ 1■ 1
1■ 1■ 2
1■ 1■ 3
1■ 1■ 4
2■ 2■ 5
2■ 2■ 6
2■ 2■ 7
7行が選択されました。
SQL> select ColA,ColB,Row_Number() over(order by 1) as Rn
2 from (select 1 as ColA,1 as ColB from dual
3 union all select 1,1 from dual
4 union all select 1,1 from dual
5 union all select 1,1 from dual
6 union all select 2,2 from dual
7 union all select 2,2 from dual
8 union all select 2,2 from dual)
9 group by ColA,ColB
10 order by 1,2,3;
ColA■ColB■Rn
----■----■--
1■ 1■ 1
2■ 2■ 2
■■■補足4 階層問い合わせを使った場合■■■
マニュアル(英語)
マニュアル
Oracleは次のように階層問合せを処理します。
・最初に、結合（指定されている場合）が、FROM句で指定されているか、
またはWHERE句述語で指定されているかが評価されます。
・CONNECT BY条件が評価されます。
・残りのWHERE句述語が評価されます。
階層問い合わせとwhere句は、
結合条件が階層問い合わせ前に評価され、
行のフィルタ条件は階層問い合わせ後に評価されるらしい・・・
where句に、結合条件と、行のフィルタ条件が混ざる場合は、
読みやすいだろうから、Join構文で結合させますが ;-)
with t as(
select 10 as Val from dual union all
select 11 as Val from dual union all
select 30 as Val from dual union all
select 31 as Val from dual union all
select 32 as Val from dual union all
select 33 as Val from dual)
select Val,max(Val)
from t
Start With Val in(10,30)
connect by prior Val = Val-1
group by Val
order by Val;
Val max(Val)
--- --------
10 10
11 11
30 30
31 31
32 32
33 33
with t as(
select 10 as Val from dual union all
select 11 as Val from dual union all
select 30 as Val from dual union all
select 31 as Val from dual union all
select 32 as Val from dual union all
select 33 as Val from dual)
select *
from t
Start With Val in(10,30)
connect by prior Val = Val-1
group by Val
model
dimension by(val as soeji)
measures(max(Val) as mea)
rules()
order by soeji;
soeji mea
----- ---
10 10
11 11
30 30
31 31
32 32
33 33
create table keiro(ID,oya) as
select 1,null from dual union
select 2, 1 from dual union
select 3, 2 from dual union
select 4, 3 from dual;
select ID,Level
from keiro,dual
where ID = 2
Start With oya is null
connect by prior ID = oya;
ID Level
-- -----
2 2
select ID,Level
from keiro join dual on (ID = 2)
Start With oya is null
connect by prior ID = oya;
レコードが選択されませんでした。
マニュアルによると、where句の記述はfrom句とStart With句の間のようです。
with t as(
select 10 as Val from dual union all
select 11 as Val from dual union all
select 30 as Val from dual union all
select 31 as Val from dual union all
select 32 as Val from dual union all
select 33 as Val from dual)
select Val
from t
where connect_by_IsLeaf=1
Start With Val in(10,30)
connect by prior Val = Val-1;
Val
---
11
33
■■■補足5 model句は、having句の後■■■
model句08 having句の次にmodel句が評価される
■■■補足6 PivotとUnPivot■■■
PivotもUnPivotも、from句の一部扱いと考えていいようです。
PivotやUnPivotした結果に表別名を付けれますので
select * from dual
unpivot(vals1 for key1 in(dummy,dummy,dummy,dummy,dummy))
unpivot(vals2 for key2 in(key1,key1))
pivot(max(key2) for vals2 in('DUMMY' as newDummy))
pivot(max(newDummy) for vals1 in('DUMMY' as newDummy2)) a
Join dual b
on a.newDummy2 is null;
NEWDUMMY2 DUMMY
--------- ------
X null
じょっぱりのぐだめぎ: select 文の評価順序
OTN-Japan 解説：GROUP BY だと NOT FOUND
OTN-Japan SELECT文