Insert All and Column Naming

SQL> create table ins(i int, j int);

Table created.

SQL> insert into ins(i, j) select 1,0 from dual;

1 row created.

SQL> insert into ins(i, j) select 1,1 from dual;

1 row created.

All fine. How about insert all?

SQL> insert all into ins(i, j) select 2,0 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,1 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,2 from dual;
insert all into ins(i, j) select 2,2 from dual
                *
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL> select * from ins;

         I          J
---------- ----------
         1          0
         1          1
         2          0
         2          1

Got an error about column naming. The simple insert into does not seem to require column naming. As the 1,1 value pair is inserted fine. Insert all requires different column names in the select list. 2,2 without column naming is not working without naming the columns.

SQL> select 2, 2 from dual;

         2          2
---------- ----------
         2          2

SQL> insert all into ins(i, j) select 2 foo, 2 bar from dual;

1 row created.

The column naming does not have an influence here. The placing does.

SQL> insert all into ins(i, j) select 3 j, 4 i from dual;

1 rows created.

SQL> select * from ins where j=3;

no rows selected

SQL> select * from ins where i=3;

         I          J
---------- ----------
         3          4

How about actual multitable insert?

SQL> insert all into ins(i, j) into ins(i, j) select 5 j, 6 i from dual;

2 rows created.

SQL> select * from ins where j=5;

no rows selected

SQL> select * from ins where i=5;

         I          J
---------- ----------
         5          6
         5          6

Similar behavior. Select list column naming does not have effect which column is populated. I would prefer using values with multitable insert to map the select list columns to the placeholders of inserted column names.

SQL> insert all into ins(i, j) into ins(i, j) values (i, j) select 7 j, 8 i from dual;

SQL> select * from ins where j in (7,8);

         I          J
---------- ----------
         7          8
         8          7

SQL Developer 3.2 and something about 12c

SQL Developer 3.2 available for download. Seems to include improvements to database diff and future 12c Database Support.

Could it have something to do with Cloud Connections.

Index Organized Materialized View

Having a materialized view joining two tables. I want to create the materialized view as a index organized table. Materialized view should be possible to be an IOT as documented. Examples of such may be found from support pages "How to create an Index-Organized Materialized View (Snapshot) [ID 114272.1]". But the examples are only for query on top of single table. No join in the materialized view query. There is the ORAGANIZATION INDEX opportunity in create materialized view, but the statement does not have the possibility to state the primary key constraint for the view.

It is possible to create a materialized view ON PREBUILT TABLE. The table may be index organized and attach the materialized view query on top to the existing table. Here is an example of such.

drop materialized view ab;

drop table ab;

drop table b;

drop table a;


create table a(aid int primary key, aname varchar2(20));

create table b(bid int primary key, aid references a, bname varchar2(20));


create table ab(aid int, bid int constraint ab_pk primary key, aname varchar2(20), bname varchar2(20)) organization index;

create materialized view ab (aid, bid, aname, bname) 
on prebuilt table
as
select a.aid, b.bid, a.aname, b.bname
  from a, b
 where a.aid=b.aid
;

insert into a values (1,'A');

insert into b values (1,1,'B');


insert into a values (2,'C');

insert into b values (2,2,'D');


commit;

exec dbms_mview.refresh('AB','C');

select * from ab;

1 1 A B
2 2 C D

select segment_name,segment_type from user_segments where segment_name like 'AB_PK';

AB_PK    INDEX

select table_name,iot_type from user_tables where table_name = 'AB';

AB    IOT

Statistics change

By default gathering statistics change during lifetime of a table in Oracle 11g. Just after creating and populating a table the statistics are not as they will be after awhile the table is used. The usage, how the table columns are queried, effects the way the statics are gathered. Actually it is not necessary to execute a query. Just making the optimizer to parse may change how the statistics are gathered. And this way the execution plans may differ from day to day. In here a table is created and populated with a unbalanced values in a column. Just after the population is done the statistics are gathered.

drop table stat;

create table stat as select level le, 999 mo from dual connect by level < 1000;

insert into stat values (1000,1);

create index stat_mo_idx on stat(mo);

exec dbms_stats.gather_table_stats(user,'STAT');

Just asking a query plan.

explain plan for select * from stat where mo = 999;

select * from table(dbms_xplan.display(format=>'basic rows'));

-----------------------------------------------------------
| Id  | Operation                   | Name        | Rows  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   500 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STAT        |   500 |
|   2 |   INDEX RANGE SCAN          | STAT_MO_IDX |   500 |
-----------------------------------------------------------


select column_name,histogram,num_buckets 
  from user_tab_columns
 where table_name = 'STAT';

COLUMN_NAME    HISTOGRAM    NUM_BUCKETS
LE             NONE         1
MO             NONE         1

Nothing else is done than "explain plan for" for a query that has a column in a where clause predicate. Gathering the statistics again.

exec dbms_stats.gather_table_stats(user,'STAT');

explain plan for select * from stat where mo = 999;

select * from table(dbms_xplan.display(format=>'basic rows'));

------------------------------------------
| Id  | Operation         | Name | Rows  |
------------------------------------------
|   0 | SELECT STATEMENT  |      |   999 |
|   1 |  TABLE ACCESS FULL| STAT |   999 |
------------------------------------------


select column_name,histogram,num_buckets 
  from user_tab_columns 
 where table_name = 'STAT';

COLUMN_NAME    HISTOGRAM    NUM_BUCKETS
LE             NONE         1
MO             FREQUENCY    2

The plan changed as the statistics gathering changed to gather a histogram for the column previously used in a where predicate.

This example shows that before gathering the initial statistics for a newly created and populated table the gathering would like to know how the table will be used. At least when you let Oracle decide how the statistics are gathered. No execution of queries is needed. Just ask the plans of the newly created application before gathering the first statistics. This way your first day of using will be a bit more similar than the future days of using the table. As the stats will change during some future maintenance window gathering after the table is changed enough.

Seems like I copied slides and the idea straight from Tom Kyte "Five things you probably didn’t know about SQL" slides presented at last OUG Harmony, but here I showed that you actually do not even need to execute the query. Only parsing makes the effect.

11.2 version one can use

select dbms_stats.report_col_usage(user,'STAT') from dual;

to get information how the columns are used. In 11.1 the information may be found in sys.col_usage$

Iniling a Common Table Expression

Having a complex common table expression (subquery factoring) SQL clause. It is used several times in the main query. The optimizer desides to do TEMP TABLE TRANSFORMATION. Effectively do what the undocumented MATERIALIZE hint would do. Sometimes that is not the most efficient way to execute. The problem described through an exaple:

drop table cteinline;

create table cteinline as
select level id, mod(level,20) md
  from dual connect by level < 100000;
  
alter table cteinline add constraint cteinline_pk primary key(id);

create index cteinline_md_idx on cteinline(md) compress 1;


with cte as (
select id, (select count(*) from cteinline co where ct.id=co.md) co from cteinline ct
)
select /*+gather_plan_statistics*/ * 
  from cte 
 where id < 10
 union all
select * 
  from cte ct2 
 where id > 99990
;

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |      1 |        |     18 |00:00:00.34 |     867 |    165 |    165 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |      1 |        |     18 |00:00:00.34 |     867 |    165 |    165 |       |       |          |
|   2 |   LOAD AS SELECT           |                             |      1 |        |      1 |00:00:00.32 |     525 |      0 |    165 |   525K|   525K|  525K (0)|
|   3 |    SORT AGGREGATE          |                             |  99999 |      1 |  99999 |00:00:00.17 |     173 |      0 |      0 |       |       |          |
|*  4 |     INDEX RANGE SCAN       | CTEINLINE_MD_IDX            |  99999 |    961 |  95000 |00:00:00.08 |     173 |      0 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL       | CTEINLINE                   |      1 |  96116 |  99999 |00:00:00.01 |     182 |      0 |      0 |       |       |          |
|   6 |   UNION-ALL                |                             |      1 |        |     18 |00:00:00.02 |     339 |    165 |      0 |       |       |          |
|*  7 |    VIEW                    |                             |      1 |  96116 |      9 |00:00:00.02 |     171 |    165 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D9DFC_47AEB497 |      1 |  96116 |  99999 |00:00:00.01 |     171 |    165 |      0 |       |       |          |
|*  9 |    VIEW                    |                             |      1 |  96116 |      9 |00:00:00.01 |     168 |      0 |      0 |       |       |          |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D9DFC_47AEB497 |      1 |  96116 |  99999 |00:00:00.01 |     168 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CO"."MD"=:B1)
   7 - filter("ID"<10)
   9 - filter("ID">99990)

We want to avoid the TEMP TABLE TRANSFORMATION. Do not do the MATERIALIZE for the common table expression. 11.1 introduced a new performance view v$sql_hint. From there one can find yet another not documented hint that does the inverse.

 
select inverse from V$SQL_HINT where name = 'MATERIALIZE'; 

INLINE

The inverse is not NO_MATERIALIZE or NOMATERIALIZE. So many other inverse hints start with word NO. Like MERGE and NO_MERGE. The INLINE hint is not icluded in the 11.2.0.3 documentation but seems to do the job for us. TEMP TABLE TRANSFORMATION is wanished from the plan and less rows are browsed during the execution in this case.

with cte as (
select /*+inline*/ id, (select count(*) from cteinline co where ct.id=co.md) co from cteinline ct
)
select /*+gather_plan_statistics*/ * 
  from cte 
 where id < 10
 union all
select * 
  from cte ct2 
 where id > 99990
;

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |      1 |        |     18 |00:00:00.01 |      92 |
|   1 |  UNION-ALL         |                  |      1 |        |     18 |00:00:00.01 |      92 |
|   2 |   SORT AGGREGATE   |                  |      9 |      1 |      9 |00:00:00.01 |      80 |
|*  3 |    INDEX RANGE SCAN| CTEINLINE_MD_IDX |      9 |    961 |  45000 |00:00:00.01 |      80 |
|*  4 |   INDEX RANGE SCAN | CTEINLINE_PK     |      1 |      9 |      9 |00:00:00.01 |       2 |
|   5 |   SORT AGGREGATE   |                  |      9 |      1 |      9 |00:00:00.01 |       8 |
|*  6 |    INDEX RANGE SCAN| CTEINLINE_MD_IDX |      9 |    961 |      0 |00:00:00.01 |       8 |
|*  7 |   INDEX RANGE SCAN | CTEINLINE_PK     |      1 |      9 |      9 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CO"."MD"=:B1)
   4 - access("ID"<10)
   6 - access("CO"."MD"=:B1)
   7 - access("ID">99990)