サブクエリ – 定義:1つのSQL文内で括弧付きの別途のクエリブロック(Query Block) – 分類:1. インラインビュー: from 節に現れるサブクエリ2. 重複したサブクエリ(Nested subquery):結果のセットを限定するためにWhere節に使用されたサブクエリ3. スカラーサブクエリ:1レコードあたり正確に1つのカラム値のみをリターンするのが特徴で、主にselect – リストで使用されますが、いくつかの例外がある場合はカラムがある場合があるほとんどの場所で使用可能なサブクエリを解く2つか。 クエリ変換 1.サブクエリ Unnesing : 重畳されたサブクエリと関連 2. ビューマージング(View Merging) : インラインビューと関連サブクエリ Unessingの意味 nest : ボックスなどをきちんと重ねる (重畳)unnest : 重畳された状態を解く サブクエリ Unnesting : 重畳されたサブクエリを解くこと
empawhereexists(1/*+rule*/fromdeptcwherec.deptno=a.deptno)とsal>(empbwhereexists(sal)から/*+rule*/avg(sal)を選択)から1を選択します(sから1を選択します)d.losalとd.hisalの間のb.salとd.grade=4（））行のソース操作———————————————————————————————0ステートメント4フィルタ（cr=52pr=0pw=0time=0us）14TABLEACCEFULLEMP（cr=4pr=0pw=0time=13us）3TABLEACCESSFULLDEPT（cr=9pr=0pw=0time=0us）1SORTAggregate（cr=39pr=0pw=0time=0timeus）5FILTER（cr=39pr=0pw=0time=16us）14TABLEACCEFUSSFULLSALGRADE（cr
重複したサブクエリメインクエリ、子という従属的で階層的な関係が存在するため、IN、EXITSTSを問わずフィルター方式、すなわちメインクエリで読まれるレコードごとにサブクエリを繰り返し遂行しながら条件に合わないデートを選び出すが、サブクエリを処理するにあたってフィルター方式が常に最適な遂行速度を保障するわけではないため、オプティマイザーは以下のいずれかを選択1。 同じ結果を保障する調印文に変換してから最適化 – サブクエリUnnestingせずに元通りに置いた状態で最適化されたサブクエリUnnestingする。 Another subqueries, generating optimization technique used in the wheel condition sub queries is not at the main query and a correlation between the 1. 2. the following form of a single low return of the sub a time to appear on the system first query handling queries : SCALAR Subselect the second : Two column to return in a query not sub queries.
Subqueries in the above form are performed first at the point of Excute, not Fetch, and the resulting values are provided as constants to the main query. 4. Basic example of subqueries UNNESTING
Subqueries in the above form are performed first at the point of Excute, not Fetch, and the resulting values are provided as constants to the main query. 4. Basic example of subqueries UNNESTING
Oracle automatically unwrites Unnest in the form of NL signingOracle automatically unwrites Unnest in the form of NL signingempでdeptnoを選択する(deptnoから/*+unnest*/deptnoを選択する)行行ソースの操作——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————=1)(オブジェクトID74586)5. Unnestingされたクエリの調印順序調整 Unnestingによって調印文に変換された後は、どちらでもドライビング集合で選択可能but、オプイマイザーが選択し、統計情報に基づいて調印箱順変更方法 1.メインクエリ集合を先にドライビングする方法5. Unnestingされたクエリの調印順序調整 Unnestingによって調印文に変換された後は、どちらでもドライビング集合で選択可能but、オプイマイザーが選択し、統計情報に基づいて調印箱順変更方法 1.メインクエリ集合を先にドライビングする方法METHOD FOR DRIVING first sub queries (2) was the main query from subqueries, side table with a reference, but he could not use the 10 possible!!METHOD FOR DRIVING first sub queries (2) was the main query from subqueries, side table with a reference, but he could not use the 10 possible!!empaから/*+preading(b)use_nl(a)*/*を選択します。a.deptnoin(deptbから/*+unnest*/b.deptnofromdeptb)RowsSourceOperation———————————————————0ステートメント14ネストループ(cr=17pr=0pw=0time=0usscostカード=4サイズ=14)4INDEXFULLSCANCEPT0=PRP uscost=1size=52card=4)(オブジェクトID74586)14TABLEACCESSFULLEMP(cr=14pr=0pw=0time=0uscost=1size=348card=4)select*fromdeptawherea.deptnoin(selectb.deptnofromempb)RowsRowSourceOperation—————————————————————–0ステートメント3HASHJOINSEMI(cr=7pr=0pw=0time=0uscost=5size=69card=3)4TABLACCESSFULLDEPT(cr=3pw=0pw=0time=0time cost=2size=80card=4)14TABLEACCESSFULLEMP(cr=4pr=0pw=0time=0uscost=2size=42card=14)1. Perform the sort Unique operation1. Perform the sort Unique operation実際、DEPTテーブルはUniqueな集合であるが、オプティマイザーは確信できず、sort Uniqueオペレーションを遂行セミゾイン本来の目的:分散質疑を効果的に行うために導入、遠隔サーバー上で二つのテーブル間で調印が必要な場合、すべてのデータ伝送ではなく、調印に必要なカラムだけを伝送した後、調印に成功したレコードセットの再送信特徴:1.サブクエリにIN、EXISTS使用する際に現れる方式2.サブクエリはメインクエリに従属的であるため、メインクエリの結果を変えることはできない!!!In fact, the DEPT table is a unique set, but the optimizer is not sure, and the original purpose of the sort unique operation is to effectively perform distributed queries. If a remote server needs to sign between two tables, it is not all data transmission.1. NL type semi-join 1.1 sub-query is provider form – (sub-query runs first) – sub-query has no main query connection condition1. NL type semi-join 1.1 sub-query is provider form – (sub-query runs first) – sub-query has no main query connection condition1.2 Subqueries are identified – (Main query runs first) – Subqueries have main query merge conditions1.2 Subqueries are identified – (Main query runs first) – Subqueries have main query merge conditions2.ソート – マージ型セミジョイン(Sort-Merge Semi Join) 1.サブクエリの結果ソートして結果setを作る 2.メインクエリの整列された結果と併合しながら調印する形態 3.Group byなどを使用してインデックスを使用することができず、メインクエリからもデータ数件抽出される場合、正常な結果 (しかし11gではCBO基盤filterで解決)2.ソート – マージ型セミジョイン(Sort-Merge Semi Join) 1.サブクエリの結果ソートして結果setを作る 2.メインクエリの整列された結果と併合しながら調印する形態 3.Group byなどを使用してインデックスを使用することができず、メインクエリからもデータ数件抽出される場合、正常な結果 (しかし11gではCBO基盤filterで解決)As a result, I solved it with Marjoin.As a result, I solved it with Marjoin.行の行のソース操作————————————————————————————————————-0ステートメント4マージJOIN（cr=6pr=0pw=0time=0us）14ソートJOIN（cr=3pr=0pw=0time=13us）14TABLEACCEFULLEMP1（cr=3pw=0pw=0pw=0time=0=3pr_1）0=0w=0time=2us)3ソートグループby(cr=3pr=0pw=0time=0us)14TABLEACCESSFULLEMP1(cr=3pr=0pw=0time=13us)3. フィルター型セミジョイン(Filter Semi Join) – EXISTSを使用するサブクエリとたまにINでも現れる形態 – サブクエリは確認者の役割のみ!!! – メインクエリより後で実行されるため、調印量が多くなると性能が落ちるセミジョイタイプ – サブクエリをUnnesingしないとクエリを最適化する上で不利な場合が多く、メインクエリを実行しながら簡単にサブクエリを繰り返し実行するフィルターオペレーションを使用、大量の集合Randomアクセス方式でサブクエリ集合をフィルタリングすると速い速度が得られない!!!but、2章6節のスカラーサブクエリのキャッシング効果のようにサブクエリ遂行結果を捨てずに内部キャッシュに保存し、同じ値が入力されれば保存された値を出力する方式を使えば速い速度を得ることができ(Filter optimization technique – caching)3. フィルター型セミジョイン(Filter Semi Join) – EXISTSを使用するサブクエリとたまにINでも現れる形態 – サブクエリは確認者の役割のみ!!! – メインクエリより後で実行されるため、調印量が多くなると性能が落ちるセミジョイタイプ – サブクエリをUnnesingしないとクエリを最適化する上で不利な場合が多く、メインクエリを実行しながら簡単にサブクエリを繰り返し実行するフィルターオペレーションを使用、大量の集合Randomアクセス方式でサブクエリ集合をフィルタリングすると速い速度が得られない!!!but、2章6節のスカラーサブクエリのキャッシング効果のようにサブクエリ遂行結果を捨てずに内部キャッシュに保存し、同じ値が入力されれば保存された値を出力する方式を使えば速い速度を得ることができ(Filter optimization technique – caching)━━━━━━━━━━━━=>8i,9i:256━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━t’);存在するt_twhereからcount()を選択します(deptno=t.deptnoでlocがヌルではないdeptから/*+no_unnest*/’x’を選択します);コールカウントCPU時間経過時間ディスククエリーの現在の行————————————————————————————————————————————————————————————10.0をパーサー0100000.0000.00000000000000000010000000001————————————————————————————————————————合計40.0000.002018 01解析中のライブラリキャッシュの欠落:1Optimizer目標:ALL_ROWSParsingユーザ:SCOTTO（ID=54）行のソース操作————————————————————————-0ステートメント1SORTAggregate（cr=18pr=0pw=0time=1114us）1400FILTER（cr=18pr=0pw=0time=11292us）1400timeus）1400TABLEACCESSFULLT_EMP（cr=12pr=0pw=0time=2847us）3INDEXROWIDDEPT（cr=6pr=0pw=0time=62us）3INDEX固有スキャンPK_DEPT（cr=3pr=0pw=0time=29us）（オブジェクトID51250）selectcountquot=decは存在せず、nullではありません。コール数CPU時間経過時間ディスククエリーの現在の行————————————————————————————————————————————————————————————————————————————パーセ10.0000000000000000000Fetch20.016を実行する 0.00101701———————————————————————————————————————————————————————————————————解析中のライブラリキャッシュの欠落:1オプティマイザ目標:ALL_ROWS解析ユーザー:SCOT（ID=54）行ソース操作—————————————————————————— 0ステートメント1ソート集約（cr=17pr=0pw=0time=921us）1400ネストループセミ（cr=17pr=0pw=0time=19493us）1400タブLEACCESSFULLT_EMP（cr=12pr=0pw=0time=2848us）3INDEXROWIDDEPT（cr=5pr=0pw=0time=65us）3INDEX固有スキャンPK_DEPT（cr=2pr=0pw=0time=30us）（オブジェクトID51250）=필세i9g부)lenl(과가효해션이에경일인싱음조가서음과터10효미━━━━━━━━━━━━=>8i,9i:256━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━t’);存在するt_twhereからcount()を選択します(deptno=t.deptnoでlocがヌルではないdeptから/*+no_unnest*/’x’を選択します);コールカウントCPU時間経過時間ディスククエリーの現在の行————————————————————————————————————————————————————————————10.0をパーサー0100000.0000.00000000000000000010000000001————————————————————————————————————————合計40.0000.002018 01解析中のライブラリキャッシュの欠落:1Optimizer目標:ALL_ROWSParsingユーザ:SCOTTO（ID=54）行のソース操作————————————————————————-0ステートメント1SORTAggregate（cr=18pr=0pw=0time=1114us）1400FILTER（cr=18pr=0pw=0time=11292us）1400timeus）1400TABLEACCESSFULLT_EMP（cr=12pr=0pw=0time=2847us）3INDEXROWIDDEPT（cr=6pr=0pw=0time=62us）3INDEX固有スキャンPK_DEPT（cr=3pr=0pw=0time=29us）（オブジェクトID51250）selectcountquot=decは存在せず、nullではありません。コール数CPU時間経過時間ディスククエリーの現在の行————————————————————————————————————————————————————————————————————————————パーセ10.0000000000000000000Fetch20.016を実行する 0.00101701———————————————————————————————————————————————————————————————————解析中のライブラリキャッシュの欠落:1オプティマイザ目標:ALL_ROWS解析ユーザー:SCOT（ID=54）行ソース操作—————————————————————————— 0ステートメント1ソート集約（cr=17pr=0pw=0time=921us）1400ネストループセミ（cr=17pr=0pw=0time=19493us）1400タブLEACCESSFULLT_EMP（cr=12pr=0pw=0time=2848us）3INDEXROWIDDEPT（cr=5pr=0pw=0time=65us）3INDEX固有スキャンPK_DEPT（cr=2pr=0pw=0time=30us）（オブジェクトID51250）=필세i9g부)lenl(과가효해션이에경일인싱음조가서음과터10효미4. HASH SEMI JOIN – Best performance – However, due to hash characteristics, it must be connected from subquery to equal sign.4. HASH SEMI JOIN – Best performance – However, due to hash characteristics, it must be connected from subquery to equal sign.5. 否定型セミジョイン(Anti Semi Join)- メインクエリとサブクエリの連結に否定型演算が存在する場合 – ex)NOTIN、NOT EXISTS- メインクエリサブクエリ連結セミジョイン、判定する方式のみ逆に- フィルター型処理形態で現れ、NOTINを使用する瞬間確認者としての役割だけが可能であるため、大量のデータの場合は遅い- したがってHASHANTI JOIN OR MERGE ANTI JOINで解かなければならない。SELECT/*+rule*/e1.ename,e1.sal,e1.deptnoFromemp1e1.expnonotin(SELECTempnoFromemp2e2);行のソース操作———————————————0ステートメント0フィルタ(cr=45pr=0pw=0time=0us)14TABLEACCEFULLEMP1（cr=3pr=0pw=0time=13us）14TABLEACCESSFULLEMP2（cr=42pr=0pw=0time=0us）(8) Anti-join not exists, not in subqueries are also processed by a filter method as follows:(8) Anti-join not exists, not in subqueries are also processed by a filter method as follows:Select * from a deptd that does not exist (select /*+no_unnest*/’x’ from emp).deptno=d.deptno); Execution Plan ——————————————————————————————————————————————————————————————————————————————————————————–Select * from a deptd that does not exist (select /*+no_unnest*/’x’ from emp).deptno=d.deptno); Execution Plan ——————————————————————————————————————————————————————————————————————————————————————————–(9 ) A total removal subqueries, including totaling function to a query SUB and the Connect Marketing and analyzing them, and function of query conversion of 10 g to be replaced with, was introduced.= > tried again the query conversion in line, so let her stay, or View in the second quarter of 2012 and maidenhair tree can be optimized.(9 ) A total removal subqueries, including totaling function to a query SUB and the Connect Marketing and analyzing them, and function of query conversion of 10 g to be replaced with, was introduced.= > tried again the query conversion in line, so let her stay, or View in the second quarter of 2012 and maidenhair tree can be optimized.(1 0 ) the Push any sub queries = > a plan of action To force = sub queries at the earliest possible stage filtering processing on the > for ” octopus ” supplement used for Compaq a hint.= > the Push Under sub queries is Ann exactly what it says only subqueries, not operated.= > Therefore, the push-to-talk substantially restructured and (q) Benihana, and especially DebugRunner’sdocstring (the hint can not be to remain with him and use.* To preferentially in the last hint study questions, especially at a test * = > not comply with testing how you can solve a plan of action in cases where it’s not in, or ” Runner to sub queries and push-to-talk _ sp) Type q to quit when it described the company “(1 0 ) the Push any sub queries = > a plan of action To force = sub queries at the earliest possible stage filtering processing on the > for ” octopus ” supplement used for Compaq a hint.= > the Push Under sub queries is Ann exactly what it says only subqueries, not operated.= > Therefore, the push-to-talk substantially restructured and (q) Benihana, and especially DebugRunner’sdocstring (the hint can not be to remain with him and use.* To preferentially in the last hint study questions, especially at a test * = > not comply with testing how you can solve a plan of action in cases where it’s not in, or ” Runner to sub queries and push-to-talk _ sp) Type q to quit when it described the company “from depth that does not * ‘ x ‘ is selected from Google (selection.deptno=d.deptno;実行計画—————————————–0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)notexistsexistsのdeptdから/*+push_subq*/*を選択します(deptno=d.deptnoのeSelect the name ‘ x ‘ from).実行計画———————————————0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)存在しないdeptdから/*+push_subq*/*を選択します(/*+unnest*/’x’fromempwheredeptno=d.deptno);実行計画———————————————0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)存在しないdeptdから/*+push_subq*/*を選択します(/*+no_unnest*/’x’fromempwheredeptno=dAction Plan ;. depth) —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- —- – MAXTRIPID FROM trip (structured query optimizer = ALL ROW (= 7 Card = sedimentation rate = 6 ) 10 filter 21 table access, a P-SiO FE ‘ FreeBSD-STABLE (EPT) (= 8 = 3 Card = 4 byte access table 3 (FULL) ‘ (FULL ) (FreeBSD-STABLE ) (Card = 2 and Copy then Repeat Double Word is = 2 = 6 )Select * from a deptd that does not exist (select ‘x’ from emp).deptno=d.deptno;実行計画—————————————–0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)notexistsexistsのdeptdから/*+push_subq*/*を選択します(deptno=d.deptnoのeUS>Select ‘x’ from mp).実行計画———————————————0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)存在しないdeptdから/*+push_subq*/*を選択します(/*+unnest*/’x’fromempwheredeptno=d.deptno);実行計画———————————————0SELECT文オプティマイザ=ALL_ROWS(Cost=6Card=1Bytes=23)10MERGEJOIN(ANTI)(Cost=6Card=1Bytes=23)’DEPT'(TABLE)の21テーブルアクセス(INDEX=4Bytes=80)’PK_DEPT'(INDEX(UNIQU)(Cost=1Card=4)SORT=4UNIQUID=4(Cost=4)の22Byt=4カード) (TABLE)(Cost=3Card=14Bytes=42)存在しないdeptdから/*+push_subq*/*を選択します(/*+no_unnest*/’x’fromempwheredeptno=d.deptno); Execution Plan ——————————————————————————————————————————————————————————————————————————————————————————————