Oracle SQL

    Category Archives: quiz

    Funny friday Oracle SQL quiz: query running N seconds

    Posted on December 11, 2020 by Sayan Malakshinov Posted in curious, Funny, oracle, quiz, SQL Leave a comment

    Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

    My solution

    with v(start_hsecs, delta, flag) as (
      select
        hsecs as start_hsecs, 
        0 as delta,
        1 as flag
      from v$timer
      union all
      select
        v.start_hsecs,
        (t.hsecs-v.start_hsecs)/100 as delta,
        case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
      from v, v$timer t
      where v.flag>0
        and t.hsecs>=v.start_hsecs
    )
    select delta
    from v
    where flag+rownum<=0;
    

    [collapse]
    SQL> var N number
    SQL> exec :N := 3 /* seconds */;
    
    PL/SQL procedure successfully completed.
    
    SQL> select...
    
         DELTA
    ----------
          3.01
    
    1 row selected.
    
    Elapsed: 00:00:03.01
    
    funny quiz sql

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 5 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Funny friday Oracle SQL quiz: query running N seconds
    • Simple function returning Parallel slave info
    • Android Oracle Client 2.0
    • PL/SQL functions and statement level consistency
    • Just short note for myself: OJPPD limitations

    Email Subscription

    Recent Comments

    • Oracle SYSTPH* Type – oraerr.com on A funny fact about collect
    • Is the use of SELECT COUNT(*) before SELECT INTO slower than using Exceptions? – oraerr.com on About the performance of exception handling
    • Oracle SQL | Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions on PRECOMPUTE_SUBQUERY hint
    • Oracle SQL | Deterministic function vs scalar subquery caching. Part 3 on Deterministic function vs scalar subquery caching. Part 2
    • Index Bouncy Scan 2 | Oracle Scratchpad on Amazing optimization of getting distinct values from the index, and TopN for each of them
    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL