EXPLAIN EXTENDED

How to create fast database queries

Happy New Year!

with 5 comments

Oracle New Year tree

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

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

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

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