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

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

Beautiful Fractals with SQL https://t.co/7jXv1G2hjE pic.twitter.com/EqLrPFS6aG

— SQL Performance Tips (@SQLPerfTips) 2015, 12月 25

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

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

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

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 |       |          |                 |
============================================================================================================================================================

追記！

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
;