Learning is not a spectator sport

Complete listing of posts

  • Can a query on the standby update the primary ?

    You would think that (with the exception of the V$ tables which are predominantly memory structures reflecting the state of various parts of the database instance) a query on a read-only standby database would have absolutely no interaction with the primary.  After all, the standby database needs to be able to run independently of the Read more

  • LAG / LEAD quick tip

    As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result. Here’s a trivial example SQL> create table t as 2 select rownum x 3 from dual 4 connect by level <= Read more

  • Direct mode operations on IOT’s

    An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c.  We can see the difference by running a simple script in both v11 and v12 SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production PL/SQL Release 11.2.0.4.0 Read more

  • dbms_output and the scheduler

    One of the nifty things in 12c is the ability to pick up DBMS_OUTPUT output from your scheduler jobs. So if you haven’t built an extensive instrumentation or logging facility, you’ll still have some details you can pick up from the scheduler dictionary views. Let’s look at an example SQL> create or replace 2 procedure Read more

  • Granular detail from a summary

    We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is perhaps a little dubious to “invent” detailed data out of summarised Read more

  • 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