« Edgeも出たし、JetStreamも出たので久々にブラウザのベンチマーク | トップページ | Pipelined table function で ascii art »

2015年12月26日 (土)

クリスマスのお遊び - SQL de Fractals :)

Twitterを見ていたら以下のtweetが目に入った! お! 面白そう。

元のサイトを覗いてみたら :) w
EXPLAIN EXTENDED -
http://explainextended.com/2013/12/31/happy-new-year-5/

元ネタはPostgreSQLだったので、Oracle Database 12c R1で書き換えて遊んでみた。 :)
遊びながら覚えるSQLってのも面白いよね。久々にLISTAGGなんて使ったよw
20151226_23132


元々あるOracleの方言、階層問合せと、11gR2から実装された階層再帰問合せを組み合わせてます。
PostgreSQLでは、generate_series()関数が利用されていますが、Oracleでは階層問合せかCUBEとrownumを組み合わせて生成するしかないのでちょいとカッコ悪いかもw
ただ、ARRAY_TO_STRING(ARRAY_AGG(s ORDER BY r), '') の箇所は、Oracleなら LISTAGG WITHIN...と1つの関数で代替できたので判りやすいかも。

WITH
q (r, i, rx, ix, g) AS
(
SELECT
CAST(r.r AS DOUBLE PRECISION) * 0.02 AS r
, CAST(i.i AS DOUBLE PRECISION) * 0.02 AS i
, CAST(.0 AS DOUBLE PRECISION) AS rx
, CAST(.0 AS DOUBLE PRECISION) AS ix
, 0 AS g
FROM
(
SELECT
LEVEL - 61 AS r
FROM
DUAL
CONNECT BY
LEVEL <= 80
) r,
(
SELECT
LEVEL - 51 AS i
FROM
DUAL
CONNECT BY
LEVEL <= 100
) i
UNION ALL
SELECT
r
, i
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN
rx * rx - ix * ix
END + r AS rx
, CASE
WHEN ABS(rx * rx + ix * ix) <= 2
THEN
2 * rx * ix
END + i AS ix
, g + 1 AS g
FROM
q
WHERE
rx IS NOT NULL
AND g < 99
)
SELECT
LISTAGG(s,'') WITHIN GROUP ( ORDER BY r ) AS Mandelbrot
FROM
(
SELECT
i
, r
, SUBSTR(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
FROM
q
GROUP BY
i
, r
) q
GROUP BY
i
ORDER BY
i
;


実行計画も載せておきますね
こんな実行計画になるのか〜〜〜面白〜い :) :) :)

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 35 | 35 | 0.05 | 8 | 22M |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1659179395)
============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +35 | 1 | 100 | | | |
| 1 | SORT GROUP BY | | 2 | 8 | 1 | +35 | 1 | 100 | 499K | | |
| 2 | VIEW | | 2 | 8 | 1 | +35 | 1 | 8000 | | | |
| 3 | HASH GROUP BY | | 2 | 8 | 34 | +2 | 1 | 8000 | 1M | | |
| 4 | VIEW | | 2 | 8 | 34 | +2 | 1 | 424K | | | |
| 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST | | | | 35 | +1 | 1 | 424K | 16M | 100.00 | Cpu (35) |
| 6 | MERGE JOIN CARTESIAN | | 1 | 4 | 1 | +2 | 1 | 8000 | | | |
| 7 | VIEW | | 1 | 2 | 1 | +2 | 1 | 80 | | | |
| 8 | CONNECT BY WITHOUT FILTERING | | | | 1 | +2 | 1 | 80 | 2048 | | |
| 9 | FAST DUAL | | 1 | 2 | 1 | +2 | 1 | 1 | | | |
| 10 | BUFFER SORT | | 1 | 4 | 1 | +2 | 80 | 8000 | 4096 | | |
| 11 | VIEW | | 1 | 2 | 1 | +2 | 1 | 100 | | | |
| 12 | CONNECT BY WITHOUT FILTERING | | | | 1 | +2 | 1 | 100 | 2048 | | |
| 13 | FAST DUAL | | 1 | 2 | 1 | +2 | 1 | 1 | | | |
| 14 | RECURSIVE WITH PUMP | | | | 34 | +2 | 100 | 416K | | | |
============================================================================================================================================================


追記!
Sql_de_fractals_frost_pattern


WITH
q (r, i, rx, ix, g) AS
(
SELECT
CAST(r.r AS DOUBLE PRECISION) * 0.0002 AS r
, CAST(i.i AS DOUBLE PRECISION) * 0.0002 AS i
, CAST(r.r AS DOUBLE PRECISION) * 0.0002 AS rx
, CAST(i.i AS DOUBLE PRECISION) * 0.0002 AS ix
, 0 AS g
FROM
(
SELECT
LEVEL - 201 AS r
FROM
DUAL
CONNECT BY
LEVEL <= 120
) r,
(
SELECT
LEVEL - 1 AS i
FROM
DUAL
CONNECT BY
LEVEL <= 100
) i
UNION ALL
SELECT
r
, i
, CASE
WHEN ABS(rx * rx + ix * ix) < 1E+8
THEN
rx * rx - ix * ix
END - 0.70176 AS rx
, CASE
WHEN ABS(rx * rx + ix * ix) < 1E+8
THEN
2 * rx * ix
END + 0.3842 AS ix
, g + 1 AS g
FROM
q
WHERE
rx IS NOT NULL
AND g < 99
)
SELECT
LISTAGG(s,'') WITHIN GROUP ( ORDER BY r ) AS frost_patterns
FROM
(
SELECT
i
, r
, SUBSTR(' .:-=+*#%@', MAX(g) / 10 + 1, 1) s
FROM
q
GROUP BY
i
, r
) q
GROUP BY
i
ORDER BY
i
;


|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/62939407

この記事へのトラックバック一覧です: クリスマスのお遊び - SQL de Fractals :):

コメント

コメントを書く