Little enhancements are always nice

Before LATERAL and CROSS APPLY were added (exposed to us) in 12c, a common technique to do correlated joins was using the TABLE/MULTISET technique.

For example, we might have had a couple of tables:

SQL> create table t as
  2  select object_type, min(created) min_dte, max(created) max_dte
  3  from dba_objects
  4  where owner = 'SCOTT'
  5  group by object_type;

Table created.

SQL> select * from t;

------------------- --------- ---------
INDEX               09-OCT-13 09-OCT-13
TABLE                (more...)

OpenWorld 2016 is not far away

OpenWorld is just around the corner, and the Ask Tom team will be involved in a number of panels where you can chat to us, ask questions, debate topics and basically have a relaxed 45mins during all the frenzied activity that is OpenWorld.  So if you’ve got any questions you would like answered “face to face”, rather than via Ask Tom, either drop them as a comment here, or feel free to post them (more...)

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 primary should that primary database be down, or destroyed.

But there’s an exception to the rule.  Consider the (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 <= 10;

Table created.

SQL> select x, lag(x) over ( order by x ) as lag_Test
  2  from t;

         X    (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;

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for  (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 do_stuff is
  3  begin
  4    dbms_output.put_line('Commenced');
  5    dbms_lock.sleep(30);
  6    dbms_output.put_line('Working');
  7    dbms_lock.sleep(30);
  8    dbms_output.put_line('Done');
  9   (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 data, but we can come up with a reasonable algorithm for doing so.

We could simply divide each (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 … here’s a simple test case to demonstrate that this assertion is wrong.

First we build up table, and then (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 dramatic improvement.

Here’s a simple version I’ve whipped up:

SQL> create table t ( x int primary key) organization  (more...)


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  order by 1
  4  fetch first 8 rows only;


8  (more...)