Query Optimierung mit ORDER BY?

Franck Pachot liefert in seinem Blog ein interessantes Beispiel, in dem eine simple Query ohne jede Einschränkung, die eine kleine Tabelle via Full Table Scan einliest, eine deutliche Reduzierung der Consistent Gets und auch der Laufzeit erfährt, wenn man ein ORDER BY ergänzt:
  • select lpad(x,2000,x) from DEMO;
  • 683 consistent gets
  • 20163693 bytes sent via SQL*Net to client
  • 668 SQL*Net roundtrips to/from client
  • select lpad(x,2000,x) from DEMO order by x;
    • 35 consistent gets
    • 118324 bytes sent (more...)

    Better Data Modeling: An Easter Present

    In celebration of Easter this year, I have a present for everyone. A book sale! I am putting my first Kindle ebook, A Check List for Doing Data Model Design Reviews, on Sale starting on Easter Sunday, April 5, 2015,  for five days! The sooner you act, the better deal you will get: Easter Sunday […]

    LAMP PHP-GD Libraries

    Everything seemed complete after configuring my standalone MySQL instance to a LAMP installation, but last night I started playing with the image files. It turns out that I failed to install the php-gd library.

    There’s very little feedback when you try to troubleshoot why you can’t read an image. In fact, the error message for reading the BLOB from MySQL was only available on the local Firefox browser:

    The image "http://localhost/ConvertMySQLBlobToImage.php" cannot be  (more...)

    2015 ODTUG Kscope Editor’s Choice Award – Calling All Minds, Calling All Media

    Originally posted on TH TECHNOLOGY:
    It’s that time of year again – for the ODTUG Editor’s Choice Awards. This year we welcome submissions in all media – even online annotated demos. Shows us your stuff – wow us with your brilliance and clever use of Oracle development tools! Hello, ODTUG! Spring is here – finally…

    Rumors about Oracle Reports 12c

    For nearly six weeks, it is rumored that Oracle Reports will not appear in the version 12c.

    Since then, I tried to get information on this topic. After a few emails I got the info I needed.

    The rumors are definitely wrong and no one at Oracle wants to cancel Reports 12c.

    I hope that this cools down the rumor mill a little bit.


    New blog to handle the PJC/Bean articles

    Here is the link to another place that stores the PJCs/Beans article without adds.




    SQL puzzles: Which tree am I?

    The following SQL statements are all clues to the names of trees:

    select covering
    from   mammals;
    update wood
    set    state = 'burnt';
    select admiration
    from   people
    select name
    from   pronouns
    where  type = 'second person';
    select target
    from   diseases
    where  nationality = 'Dutch';
    update oslo
    set    appearance = 'smarter';
    select *
    from   elements e
    join   punishments p
    where  e.atomic_number = 47
    and    p. (more...)

    MySQL bind-address

    While I try to keep things simple, sometimes eliminating options and explanations comes back to haunt me. After posting how to open a Fedora firewall port for a LAMP stack, somebody got trapped by my instructions for installing MySQL on Fedora. They got stuck because they had the following setting in their my.cnf file:


    I’d suggested using that bind-address value for a DHCP VMware Fedora installation in Step #7. I was trying (more...)

    Open Fedora Port 80

    After installing the LAMP stack on Fedora, you need to open port 80 in the Firewall to access the PHP programs on the Fedora instance from external servers. You can open a firewall port by launching the firewall-config application as the root user with the following syntax:


    The firewall-config utility opens the following dialog message:


    Click on the Ports tab, and you’ll see the following:


    Click on Add button to add a port (more...)

    Fedora Install LAMP

    My students wanted an extra credit assignment, so I thought a LAMP configuration and test would be appropriate. The only problem was I hadn’t added it to their course VMware instance. So, here are the instructions to install Apache2, PHP, and MySQLi for a complete LAMP stack.

    The post builds on my Fedora Install of MySQL and MySQL Workbench on Fedora posts from last year. It also presumes that you’ve installed a studentdb database but (more...)

    MV-Refresh mit out_of_place Parameter in 12c

    Und wieder verweise ich auf Jonathan Lewis, der diesmal eine interessante neue (12c) Refresh-Option für Materialized Views vorstellt, die durch den Parameter out_of_place aufgerufen wird. Die Idee dabei ist sehr einfach: im Rahmen des Refreshs wird eine zusätzliche Hilfstabelle erzeugt und gefüllt, die dann am Ende der Operation gegen das bisher zur MV gehörende Segment ausgetauscht wird (über eine interne Folge von Rename-Operationen). Das Verfahren entspricht grundsätzlich einem Workaround, den der Herr Lewis in (more...)

    Frequenz des ASH/AWR Samplings

    Noch einmal Jonathan Lewis, diesmal mit einer Erläuterung des ASH- und AWR-Samplings von Session-Informationen:
    • einmal in jeder Sekunde werden die Informationen zu aktiven Sessions (state = 'ACTIVE') aus v$session nach v$active_session_history kopiert.
    • diese Snapshots werden als Sample bezeichnet.
    • ein Zehntel dieser Datensätze wird in der entsprechenden AWR-Tabelle dba_hist_active_sess_history persistiert.
    • Es handelt sich aber nicht um jeden zehnten Datensatz, sondern um die Datensätze der zehnten Sekunde. Im Ergebnis sieht die enthält die AWR-Tabelle also einen (more...)

    ANSI Join Syntax und das 1000-Spalten-Limit

    Mein Titel klingt mal wieder wie der Name einer eher lieblos fortgeschriebenen Krimi-Serie, aber wahrscheinlich gelingt es mir eher, den Eintrag über eine solche Überschrift wiederzufinden, als anhand des Titels ANSI expansion, den Jonathan Lewis seinem Artikel gegeben hat, den ich hier abkürzend nacherzähle. Worum es geht ist Folgendes: im OTN-Forum wurde ein gut beschriebener Testfall vorgelegt, in dem ein ANSI-Join (mit recht breiten Views) in 12c (aber nicht in 11g) einen Fehler "ORA-01792: (more...)

    Why I can’t do webinars

    This is why I can't do webinars from my current home.

    Ookla Speed Test Result

    The upload speed is no doubt way too slow to cater for voice and screencast. Unfortunately I also seem to be in quite the dead spot for 4G wireless connection.

    Download speed might be enough to support the streaming services recently launched in Australia, but I know ABC's iView does stutter occasionally so I think I'll still have to wait (more...)

    Visualizing Statspack “Average Active Sessions” in SQL Developer

    This is the second post in my mini-series on leveraging SQL Developer Reports for DBA tasks, today with visualizing Average Active Sessions (AAS). In this article I’ll cover What AAS is and how to interpret it How to build a basic line graph in SQL Developer How to extend the graph with detailed child reports (Time […]

    Migrating To Oracle Using Custom Object Names

    Sybase , SQL  Server and other databases allow for long identifier names.
    Oracle allows for a maximum of 30 Bytes when naming objects like  users, tables, ...
    When it comes to migrating objects to Oracle,  SQL Developer will truncate the object names and resolve clashes with unique names.

    Say for example you have two tables called

    • Application_Name_SubArea_Name_SpecificAreaName_Table_Table1
    • Application_Name_SubArea_Name_SpecificAreaName_Table_Table2

    Oracle SQL Developer will convert these to

    • Application_Name_SubArea_Name_S
    • Application_Name_SubArea_Name_1
    This default new name may not be to your (more...)

    Lowercase Table Names

    A student posed the question about why table names are case sensitive. That’s because case insensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:

             WHEN @@lower_case_table_names = 1 THEN
               'Case insensitive tables'
               'Case sensitive tables.'
             END AS "Table Name Status";

    The default value returned is:

    | Table Name Status      |
    | Case sensitive tables. |

    What’s this ‘WHERE 1=1′?

    Since some time I have been adding WHERE 1=1 to all my queries.
    I get queries like this:

    SELECT *
      FROM emp e
     WHERE 1=1
       AND e.ename LIKE 'A%'
       AND e.deptno = 20

    Lots of people ask me what’s the use of this WHERE 1=1.

    You know I like to type as little as possible but here I am typing a lot of extra characters. And yet, it makes my development life a (more...)

    Join Tables on Date Ranges

    A recent question on the OTN SQL forum asked how best to join two tables related by ID and date range, in order to insert one row per date into a data warehouse. One solution was to expand the data from each table, creating one row per date, then join on date. I think it's more efficient to join on date range, then expand.

    An Irish Blessing (for my friends at #OUG_IRE)

    Maybe a day or so late (for St Patrick’s Day) but a bunch of my Oracle pals (#ACEs and #ACEDs) are all over in Dublin at an Oracle User Group event (#oug_ire on Twitter) so this seemed appropriate. Looks like they are having a good event (based on the tweets), with a fun evening to […]