Learning is not a spectator sport

Complete listing of posts

  • LOGGING and temporary space

    We had an interesting question on AskTom this week.  The poster had been told by their DBA that the reason their large INSERT-AS_SELECT statement was consuming lots of temporary segment space, was because the database had been recently altered to enable FORCE LOGGING, presumably to ensure easier consistency in a physical standby node. So …… Read more

  • Parsing … no big deal eh ?

    Most of us have probably seen the standard demo when it comes to emphasizing the need for sharable SQL, aka, using bind variables where appropriate.  The demo traditionally compares two similar scripts, where one of them generates a lot of SQL statements with literals, and the other recasts the same script with bind variables for… Read more

  • 12c FETCH PERCENT

    A nice little feature in 12c is the FETCH FIRST n ROWS syntax, which is a simple shorthand to avoid using inline views and the like to get a subset of the rows from what would normally be a larger resultset. Here’s a simple example showing the syntax SQL> select * 2 from t 3… Read more

  • SQL statements using literals

    16 years ago, someone “Ask-ed Tom” how to find those SQL statements that were not using bind variables.   You can see the question here (because we don’t delete stuff ever ) but I’ll paraphrase the answer below: Tom took the following approach take a copy of SQL statements in the library cache create a routine… Read more

  • MERGE vs UPDATE/INSERT revisited

    I wrote a few years back that for single row operations, MERGE might in fact have a large overhead than the do-it-yourself approach (ie, attempt an update, if it fails, then do an insert). Just to show that it’s always good to revisit things as versions change, here’s the same demo (scaled up now because… Read more

  • Datatypes for DATES

    Richard Foote has written a post about not using the DATE datatype for storing dates. So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype. Here’s a demo SQL> create table t ( x raw(7) ); Table created. SQL> SQL> create or replace 2 procedure store_date(p_yyyymmddhh24miss varchar2)… Read more

  • Let me START WITH sequences

    It’s always cool that you can learn stuff every single day, even on the most simple of topics.  This one came from an AskTom question.  We define a sequence to start with 100, and then alter it to modify the INCREMENT BY. SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE… Read more

  • A cool thing with EXCHANGE PARTITION (part 2)

    In the previous post, I showed that even though a partition was “removed” (ie, exchanged out) from a table, a query running against the table could still successfully run the completion. However, of course, if once that partition is exchanged out, it is now a table in it’s own right…and is subject to the whims… Read more

  • Those pesky dates as strings

    You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”. But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the good data from the bad data. For example, if your table… Read more

  • OTN Yathra– scenes from Bangalore and Hyderbad

    It’s sad that I could not capture in pictures the amazing sights and sounds from both inside the conference and outside in the streets of these amazing places. I was too busy just absorbing it myself and neglected to take enough pictures But again, a truly wonderful couple of days. Read more

  • OTN Yathra Chennai

    Rather than try to convey in words the first couple of days in India, I thought I’d share some of the images from the first days here in India, covering the Chennai conference. Enjoy Read more

  • Subtle variations in optimizer stats

    Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care. Let’s look at the following example SQL> create table T ( 2 x varchar2(20) , y varchar2(100)); Table created. SQL> insert into T 2 select ‘x’ , rpad(‘z’,100) from… Read more