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

「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個で計測。ぶっちぎりで草ｗｗｗｗｗ

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