EXPLAIN EXTENDED

How to create fast database queries

Happy New Year!

with 5 comments

Oracle New Year tree

?
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
WITH    tree AS
(
SELECT  /*+ MATERIALIZE */
'  ,,,"''' AS needles, 'Oo%$&' AS decorations
FROM    dual
),
branches AS
(
SELECT  level AS id, level - TRUNC((level - 2) / 6) * 2 AS s
FROM    dual
CONNECT BY
level <= 24
        )
SELECT  RPAD(' ', 18, ' ') || '*' AS tree
FROM    dual
UNION ALL
SELECT  RPAD(' ', 18 - s, ' ') || '/' ||
        (
        SELECT  REPLACE(
                SYS_CONNECT_BY_PATH
                (
                CASE
                WHEN DBMS_RANDOM.value < 0.1 THEN
                        SUBSTR(decorations, TRUNC(DBMS_RANDOM.value * LENGTH(decorations)) + 1, 1)
                ELSE
                        SUBSTR(needles, TRUNC(DBMS_RANDOM.value * LENGTH(needles)) + 1, 1)
                END , '/'
                ),
                '/', ''
                )
        FROM    tree
        WHERE   CONNECT_BY_ISLEAF = 1
        CONNECT BY
                level <= s * 2 - 1
        )
        || '\'
FROM    branches
TREE
*
/,\
/,, \
/$," ,\
/, ',, \
/ ,'o'' ,\
/,, ,'",,,, \
/,',,$, '' ,,\
/,, ,'",,,, \
/",, ,,"' , ",\
/ $' ,, " ,' '\
/, , '"O,,'o"," "\
/,,,," , ',,',,',',\
/,',,"' ' ' ,,,,&"o,\
/,' ,,," ,',%" ,,,\
/,',,"' ' ' ,,,,&"o,\
/,$,,," ,,$'%",",,%,,''"\
/ ' ,, ," ,,& , ,, ,, \
/,",O' ,',, , ,$, "',",,,',\
/,," ",, ,,, ,',', " ,""',\
/ ,O ',,,,," o , '','', ,&,\
/,," ",, ,,, ,',', " ,""',\
/,"'',,""$"," ,$,%,' "" ,,,',",\
/, '" "O,,o' , ,'% $',,, ,""" , \
/, O' ,,, &,',,' , ',$, ,' ,,, ,,'\

SQL Server bottle of champagne

?
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
WITH    lines AS
(
SELECT  1 AS n
UNION ALL
SELECT  n + 1
FROM    lines
WHERE   n < 24
        ),
        bottle_parts (part, lft, body, rgt, w) AS
        (
        SELECT  'mouth', '.', '_', '.', 2
        UNION ALL
        SELECT  'neck', '|', ' ', '|', 2
        UNION ALL
        SELECT  'bell', '/', ' ', '\', NULL
        UNION ALL
        SELECT  'border', '/', '=', '\', NULL
        UNION ALL
        SELECT  'body', '|', ' ', '|', 7
        UNION ALL
        SELECT  'bottom', '(', '_', ')', 7
        ),
        bottle_lines AS
        (
        SELECT  CASE
                WHEN n = 1 THEN 'mouth'
                WHEN n BETWEEN 2 AND 8 THEN 'neck'
                WHEN n = 11 THEN 'border'
                WHEN n BETWEEN 9 AND 13 THEN 'bell'
                WHEN n BETWEEN 13 AND 23 THEN 'body'
                WHEN n = 24 THEN 'bottom'
                END AS part,
                n
        FROM    lines
        ),
        bottle_width AS
        (
        SELECT  bp.*, n, COALESCE(w, (n - 6)) AS width
        FROM    bottle_lines bl
        JOIN
                bottle_parts bp
        ON      bp.part = bl.part
        ),
        bottle AS
        (
        SELECT  n, width, REPLICATE(' ', 18 - width) + lft + REPLICATE(body, width * 2) + rgt AS line
        FROM    bottle_width
        ),
        bubbles (pos, m, bubble) AS
        (
        SELECT  CAST(ROUND(RAND() * 4, 0, 1) AS INTEGER), 1, ':' AS bubble
        UNION ALL
        SELECT  pos + ASCII(SUBSTRING(CAST(NEWID() AS VARCHAR(MAX)), 1, 1)) % 3 - 1, m + 1, SUBSTRING(':.oO', m / 3 + 1, 1)
        FROM    bubbles
        WHERE   m < 12
        ),
        limits AS
        (
        SELECT  *,
                18 +
                CASE
                WHEN pos < 1 - width THEN 1 - width
                WHEN pos > width - 1 THEN width - 1
ELSE pos
END AS rpos
FROM    bottle bo
LEFT HASH JOIN
bubbles bu
ON      bu.m = 24 - bo.n
)
SELECT  SUBSTRING(line, 1, COALESCE(rpos, 1) - 1) + COALESCE(bubble, ' ') + SUBSTRING(line, COALESCE(rpos, 1) + 1, 200) AS bottle
FROM    limits
ORDER BY
n
bottle
.____.
| |
| |
| |
| |
| |
| |
| |
/ \
/ \
/==========\
/ O \
/ O \
| O |
| o |
| o |
| o |
| . |
| . |
| . |
| : |
| : |
| : |
(______________)

PostgreSQL fireworks

?
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
41
42
43
44
WITH    centers AS
(
SELECT  angle,
len,
ROUND(len * SIN(2 * PI() * angle)) AS x,
ROUND(len * COS(2 * PI() * angle)) AS y,
ROUND(len * 0.3)::INTEGER + 1 AS trace
FROM    (
SELECT  RANDOM() AS angle,
8 * (1 - POWER(RANDOM(), 3)) AS len
FROM    generate_series (1, 50) s
) q
),
traces AS
(
SELECT  *,
generate_series(1, trace) AS part
FROM    centers
),
parts AS
(
SELECT  CASE
WHEN trace = part THEN
LEAST(len * 0.2, 2)::INTEGER
ELSE
TRUNC(angle * 8 - 0.5)::INTEGER % 4 + 3
END AS symbol,
TRUNC(x + part * SIN(2 * PI() * angle)) AS x,
TRUNC(y + part * COS(2 * PI() * angle)) AS y
FROM    traces
)
SELECT  ARRAY_TO_STRING(
ARRAY(
SELECT  COALESCE(
(
SELECT  SUBSTR(E'.xX\\-/|', MIN(symbol) + 1, 1)
FROM    parts
WHERE   x = col - 14
AND y = row - 12
), ' ')
FROM    generate_series(1, 25) col
), ''
) AS FIREWORKS
FROM    generate_series(1, 24) row
fireworks
X
| X
x| | X
x | | /
X\ \x | /x
X\ \ \ /
X \ \ x / /xx
\\ x |. ///
X-- \ |
xxx-- .
Xx-- ..
xxx-/ --x
. \\ --X
. .
/ \
X/ | \ \x
/ |\ \\
// |x\ \ \x
x/ |\x \ X
X x\x X
X

MySQL postcard

?
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
SELECT  CONCAT(COALESCE(border, '|'), RPAD(COALESCE(postcard, ''), 61, COALESCE(filler, ' ')), COALESCE(border, '|')) AS postcard
FROM    (
SELECT  id2 * 5 + id1 AS line
FROM    (
SELECT 1 AS id1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) q1
CROSS JOIN
(
SELECT 0 AS id2 UNION ALL SELECT 1
) q2
) dummy
LEFT JOIN
(
SELECT  3 AS line, CONCAT(' Dear ', USER(), ',') AS postcard
UNION ALL
SELECT  4, ' I wish you good luck in the New Year.'
UNION ALL
SELECT  5, ' May all your plans be optimal, all your queries be answered'
UNION ALL
SELECT  6, ' and all your estimations be correct.'
UNION ALL
SELECT  8, '               Yours sincerely,'
UNION ALL
SELECT  9, '                                                    Quassnoi'
) ln
ON      ln.line = dummy.line
LEFT JOIN
(
SELECT 1 AS line, '.' AS border, '_' AS filler
UNION ALL
SELECT 10 AS line, '.' AS border, '_' AS filler
) borders
ON      borders.line = dummy.line
ORDER BY
dummy.line
postcard
._____________________________________________________________.
| |
| Dear dbuser@localhost, |
| I wish you good luck in the New Year. |
| May all your plans be optimal, all your queries be answered |
| and all your estimations be correct. |
| |
| Yours sincerely, |
| Quassnoi |
._____________________________________________________________.
Happy New Year!

Written by Quassnoi

December 31st, 2009 at 11:00 pm

Posted in Miscellaneous

«
»

5 Responses to 'Happy New Year!'

Subscribe to comments with RSS

  1. Happy new year!

    Gonçalo

    2 Jan 10 at 19:32

  2. Hi Quassnoi,
    I enjoy your blog – a question about the PostgreSQL fireworks display.

    I tried running the query, but got:

    ERROR: syntax error at or near “WITH centers”
    LINE 1: WITH centers AS

    I’m using Postgres 8.3.9.

    Eugene

    Eugene

    5 Jan 10 at 22:26

  3. @Eugene: WITH was introduced in PostgreSQL 8.4, so it’s not that strange you’re seeing an error.

    http://www.postgresql.org/docs/8.4/static/queries-with.html

    Richard

    6 Jan 10 at 13:05

  4. @Eugene: @Richard is right, this only works in 8.4. Without recursion, it’s hard to make anything more complex than a postcard :)

    Quassnoi

    9 Jan 10 at 19:58

  5. Excellent work:) now i know what i am going to send to my co-developers on the new years eve on 2012.

    Giwrgos

    2 Oct 11 at 23:32

Leave a Reply