Help us understand the problem. What is going on with this article?

Hard Parseに時間が掛かるお手軽なSQLを作ってみる。(Oracle Database)

More than 1 year has passed since last update.

「Hard Parseに時間が掛かる手軽なSQLを作りたい。彡(゚)(゚)」と呟いた
(※下記twitterモーメント参照)ところ、色々とアイデアを頂いたので、
全部ではないのですがお試し&まとめてみましたやで彡(゚)(゚)

Hard Parseに時間が掛かるSQL ※twitterモーメント
https://twitter.com/i/moments/1001431132234072064

Case1. IN句リテラルを仕様上限(999個)まで

こちらの結果は下記の通り、ぼちぼち彡(゚)(゚)

SELECT A.ITEM_NAME
     , COUNT(*)
  FROM ITEM_TBL A
 WHERE ITEM_NO IN (
   1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
:
   975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999)
 GROUP BY ITEM_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.08       0.09          0          2          0          10

Case2. 結合表8個以上

こちらの結果は下記の通り、Case1. よりは増えました彡(゚)(゚)

SELECT A.ITEM_NAME
     , COUNT(*)
  FROM ITEM_TBL A
     , ITEM_TBL B
     , ITEM_TBL C
     , ITEM_TBL D
     , ITEM_TBL E
     , ITEM_TBL F
     , ITEM_TBL G
     , ITEM_TBL H
     , ITEM_TBL I
     , ITEM_TBL J
 WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD')
                         AND TO_DATE('2012/08/02', 'YYYY/MM/DD')
   AND A.ITEM_NO = B.ITEM_NO
   AND B.ITEM_NO = C.ITEM_NO
   AND C.ITEM_NO = D.ITEM_NO
   AND D.ITEM_NO = E.ITEM_NO
   AND E.ITEM_NO = F.ITEM_NO
   AND F.ITEM_NO = G.ITEM_NO
   AND G.ITEM_NO = H.ITEM_NO
   AND H.ITEM_NO = I.ITEM_NO
   AND I.ITEM_NO = J.ITEM_NO
 GROUP BY A.ITEM_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.48       0.48          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         10          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.48       0.48          0         10          0          10

Case3. 結合表8個以上 + permutation増強

こちらの結果は下記の通り、Case2. より更に増加彡(゚)(゚)

ALTER SESSION SET "_optimizer_max_permutations"=80000;
SELECT A.ITEM_NAME
     , COUNT(*)
  FROM ITEM_TBL A
     , ITEM_TBL B
     , ITEM_TBL C
     , ITEM_TBL D
     , ITEM_TBL E
     , ITEM_TBL F
     , ITEM_TBL G
     , ITEM_TBL H
     , ITEM_TBL I
     , ITEM_TBL J
 WHERE J.REGIST_DATE BETWEEN TO_DATE('2012/08/01', 'YYYY/MM/DD')
                         AND TO_DATE('2012/08/02', 'YYYY/MM/DD')
   AND A.ITEM_NO = B.ITEM_NO
   AND B.ITEM_NO = C.ITEM_NO
   AND C.ITEM_NO = D.ITEM_NO
   AND D.ITEM_NO = E.ITEM_NO
   AND E.ITEM_NO = F.ITEM_NO
   AND F.ITEM_NO = G.ITEM_NO
   AND G.ITEM_NO = H.ITEM_NO
   AND H.ITEM_NO = I.ITEM_NO
   AND I.ITEM_NO = J.ITEM_NO
 GROUP BY A.ITEM_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.66       0.68          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         10          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.67       0.69          0         10          0          10

Case4. UNION500行

こちらの結果は下記の通り、エエ感じやで!彡(^)(^)

SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100 UNION
:
SELECT ITEM_NO FROM ITEM_TBL WHERE ITEM_NO BETWEEN 1 AND 100

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.04       1.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0        500          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.06       1.06          0        500          0          10

Case5. DUAL表を200個結合

Kazumasa Obaraさんネタ。元ネタの500個だと
終わらなかったので200個で計測。ぶっちぎりで草wwwww

SELECT A.* FROM DUAL A,
DUAL,
:
DUAL,
DUAL,
DUAL,
DUAL,
DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     48.86      49.96          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     48.86      49.96          0          3          0           1

やっぱり結合順序の探索には時間が掛かるんやなぁ……彡(゚)(゚)
アイデアを頂いた皆さん、ありがとうございます!(`・ω・)ゞ

ora_gonsuke777
やきうのお兄ちゃんなITエンジニア、主にOracle Database廻りを担当。日本オラクル株式会社 柴田 歩 ※本ブログに記述された見解は私個人の見解であり、所属する会社&組織の見解を必ずしも反映したものではありません。ご了承ください。
http://d.hatena.ne.jp/gonsuke777/
oracle
Oracle Cloudは、最先端の機能をSoftware as a Service、Platform as a ServiceおよびInfrastructure as a ServiceおよびData as a Serviceとして提供します。
https://cloud.oracle.com/ja_JP/home
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away