Tag: 12c

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,…

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…

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….

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…

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…

Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error: C:\oracle\stage\22581007>opatch apply Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : C:\oracle\product\1210~1.2 Central Inventory : C:\Program Files\Oracle\Inventory from :…

TOP-N histograms on 12c

I had an interesting question on AskTom today, with a nicely provided test case !!!,  so I thought I’d share it here with some additional commentary. The basic premise of the TOP-N histogram in 12c is that when the number of distinct values in…