Multitable Inserts

Multitable Inserts:

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions.

Scripts:

Scneraio1:
drop table org_tab;
drop table dept_tab;
drop table mgr_tab;

create table org_tab ( empid number,dept_name varchar2(10),manager_name varchar2(10));
insert into org_tab values ( 101,'sales','A');
insert into org_tab values ( 102,'sales','A');
insert into org_tab values ( 103,'sales','A');
insert into org_tab values ( 104,'sales','A');
insert into org_tab values ( 105,'sales','A');
insert into org_tab values ( 106,'Marketing','B');
insert into org_tab values ( 107,'Marketing','B');
insert into org_tab values ( 108,'Marketing','B');
insert into org_tab values ( 109,'Marketing','B');
insert into org_tab values ( 110,'Marketing','B');

SQL> select * from org_tab;
EMPID DEPT_NAME MANAGER_NA
---------- ---------- ----------
101 sales A
102 sales A
103 sales A
104 sales A
105 sales A
106 Marketing B
107 Marketing B
108 Marketing B
109 Marketing B
110 Marketing B

10 rows selected.

create table dept_tab (empid number,dept_name varchar2(10));
create table mgr_tab (empid number,manager_name varchar2(10));

SQL> INSERT ALL
2 INTO dept_tab values ( empid,dept_name)
3 INTO mgr_tab values ( empid,manager_name)
4 SELECT empid,dept_name,manager_name from org_tab ;

20 rows created.

SQL> select * from dept_tab;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing

10 rows selected.
SQL> select * from mgr_tab;
EMPID MANAGER_NA
---------- ----------
101 A
102 A
103 A
104 A
105 A
106 B
107 B
108 B
109 B
110 B

10 rows selected.
Scneraio2:

drop table dept_sales;
drop table dept_market;
create table dept_sales (empid number,dept_name varchar2(10));
create table dept_market (empid number,dept_name varchar2(10));


SQL> INSERT ALL
2 WHEN dept_name = 'sales' THEN
3 INTO dept_sales values ( empid,dept_name)
4 WHEN dept_name = 'Marketing' THEN
5 INTO dept_market values ( empid,dept_name)
6 SELECT empid,dept_name,manager_name from org_tab ;

10 rows created.

SQL> select * from dept_sales;
EMPID DEPT_NAME
---------- ----------
101 sales
102 sales
103 sales
104 sales
105 sales

SQL> select * from dept_market;
EMPID DEPT_NAME
---------- ----------
106 Marketing
107 Marketing
108 Marketing
109 Marketing
110 Marketing
The restrictions on multitable INSERTs are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

Read More

Table Space Status Script

Script to identify the table space status:
SELECT Substr(ddf.tablespace_name,1,20) "Tablespace Name",
Round(ddf.bytes/1024/1024,2) "Allocated Bytes(MB)",
Round(de.used_bytes/1024/1024,2) "Used Bytes(MB)",
Round(dfs.free_bytes/1024/1024,2) "Free Bytes(MB)",
Round((de.used_bytes/ddf.bytes)*100,2) "% Used Bytes ",
Round((dfs.free_bytes/ddf.bytes)*100,2) "% Free Bytes"
FROM DBA_DATA_FILES DDF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) DE,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) dfs
WHERE de.file_id = ddf.file_id
AND ddf.file_id = dfs.file_id
ORDER BY ddf.tablespace_name;

Read More

TRUNCATE Vs Index Status

Comments: The Redo size of the current session

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;NAME VALUE
-------------- ----------
redo size 32404
Comments: create the CTAS table with NOLOGGING.

SQL> create table t nologging as select * from all_objects where 1=0;

Table created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 69912

Comments: create the index with NOLOGGING option.

SQL> create index object_name_idx on t(object_name) nologging;

Index created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 87920

Comments: Insert the records into table with the append hint. It will not create any Redo for the table data but it will create a Redo for the index. Check the Redo size and It is high.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 4302432

Comments: Make the index as UNUSABLE state.

SQL> alter index object_name_idx unusable;

Index altered.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 4305028
Comments: Check the status of the index and truncate the table data.

SQL> select index_name, status from user_indexes where table_name = 'T';INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX UNUSABLE

SQL> truncate table t;

Table truncated.
Comments: Insert the records into table with the append hint.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

Comments: Now we check the Redo generation. Still it is high and it has generated the Redo for the index because the TRUNCATE operation has made that index as VALID.


SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 8529448

Comments: check the current status of the index.

SQL> select index_name, status from user_indexes where table_name = 'T';
INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX VALID

Read More

Identify the redo size of the current session

select name,a.value from v$sesstat a, v$sysstat b where b.statistic#=a.statistic# and b.name = 'redo size' and sid = 16;

Read More

Query to identify the SID of current session

select sid from V$mystat where rownum =1;

Read More

NOLOGGING Vs Redo entries.

Title: NOLOGGING Vs Redo entries.
Comments: This document is related to the redo log generation of the scripts in LOGGING mode and the NOLOGGING mode.

Checking the redo generation for table creation:
Ø Checking the record count and the current Redo log size of the session.

SQL> select count(*) from all_objects;



COUNT(*)
----------
27189
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 01 row selected.
Ø Creating the table CTAS (Create Table As Select) table with the NOLOGGING mode. It is not creating the Redo logs for the data.

SQL> create table t NOLOGGING as select * from all_objects;

Table created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 56732
Ø Creating the table CTAS table with the LOGGING mode. It is creating the Redo logs for the data.

SQL> create table tt as select * from all_objects;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 31768281 row selected.
Ø Creating the table CTAS table with the NOLOGGING mode (only table structure) and INSERT the records. It is creating the Redo logs for the data.

SQL> create table ttt nologging as select * from all_objects where 1=2;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 3212880
1 row selected.

SQL> insert into ttt
2 select * from all_objects;

27192 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6257108

1 row selected.
Ø Creating the table CTAS table with the LOGGING mode (only table structure) and INSERT records with /*+ APPEND */ Hint. The Redo logs are not generated for the data.

SQL> create table tttt nologging as select * from all_objects where 1=2;
Table created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6293312
1 row selected.
SQL> INSERT /*+ APPEND */ INTO tttt select * from all_objects;
27193 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6311384
1 row selected.

Redo Log generation table:
The Below table shows when the log generates.




----------- ------------- --------------- ---------------
Checking the redo generation for indexes:
Ø Drop the Redo_check table.

SQL> drop table redo_check;
Table dropped.

Ø Checks the current redo size of the session.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 25016
Ø Create a table and insert the records.

SQL> create table redo_check (
2 num number,
3 data1 varchar2(20)
4 );

Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 32460
SQL> declare
2 i int;
3 begin
4 for i in 1 .. 200000
5 loop
6 insert into redo_check values(i,'Redo check ' i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49555748
Ø Create the index with NOLOGGING mode.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 495558601 row selected.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49633080

1 row selected.
Ø We try to update the index column and check the Redo generation.




SQL> update redo_check set data1 = ' redo check' where num <100001 em="">
Ø Yes. The updates are generating the Redo logs for the table entry and index if we create a table in the NOLOGGING mode also.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536500
1 row selected.SQL> commit;Commit complete.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536592
1 row selected.
Ø We drop the index and try to update the records.

SQL> drop index dat_idx;
Index dropped.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127548252
1 row selected.
SQL> update redo_check set data1 = ' redo check' where num < 100001;Ø Now it has created the redo only for the table entry.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152859364
1 row selected.
Ø After that creating the index with NOLOGGING. This is one of the best ways to increase the performance of the scripts.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152906760
1 row selected.SQL> commit;
Ø Now we check the redo generation with unusable the index and rebuilding the index way.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 0Ø Make the index as an Unusable and update the records in the table.
SQL> alter index dat_idx unusable;
Index altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2224
SQL> update redo_check set data1 ='reupdate' where num < data1 ="'reupdate'">
Ø The update statement is giving the error because the index is in the unusable state. To void this error message alter the current session with below statement.
SQL> alter session set skip_unusable_indexes=true;
Session altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2876
Ø Execute the update statement now and check the redo log. It has created the redo generation only for the table entries.
SQL> update redo_check set data1 ='reupdate' where num < 100001;>SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 24896420
Ø Rebuild the index and if we check the redo generation. It did not create any redo for the index entries.

SQL> alter index data1_idx rebuild;
Index altered.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;

NAME VALUE
------------ ----------
redo size 24934104

Read More

Indexes on two different table space( Associated to different schema)

Question: Can we create indexes on two different table spaces. ( Associated to different schema)

Comments: The answer is “Yes”. We can create a table in one table space and index in another table space.

Now the question is some thinking different. Can we create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo2_TS)? (Where table has created)

Scripts:
sql> conn demo1/demo1 ;
sql> create table table_in_demo1 ( col1 number, col2 number );
sql> insert into table_in_demo1 values (10,11);
sql> insert into table_in_demo1 values (20,21);
sql> insert into table_in_demo1 values (30,31);
sql> select * from table_in_demo1;

COL1 COL2
----- ------

10 11
20 21
30 31

sql> grant all on table_in_demo1 to demo2
sql> conn demo2/demo2
sql> select * from demo1.table_in_demo1
COL1 COL2
------ -----
10 11
20 21
30 31
Comments: Now the Demo2 is having the access to “table_in_demo1”. Can we create a index on table_in_demo1 table from DEMO2?

The table is available in the Demo1_TS table space. Will it allow to create a index in the
Demo2_TS. (The demo2 is associated with the Demo2_TS).

Let’s check in the Demo2 schema

Scripts:
sql> conn demo2/demo2
sql> create index tab_idx on demo1.table_in_demo1(col2);

sql> select owner,index_name,table_owner from all_indexes
where table_name = 'TABLE_IN_DEMO1';

Solution:
It is allowing programmer to create an index in Demo2 schema.

sql> conn demo1/demo1
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

So,

We can create an index on some other user’s table space (Demo2_TS) which is not associated to the current user (Demo1_TS)

Comments:
One more question. What will happen if we revoke the permission from Demo1 schema?

No. Two more question because of the above question.

Question 1: will it allow programmer to revoke the permission?
Question 2: if it allows programmer to revote, will it use the query use that index?

Answer for Question 1:sql> conn demo1/demo1
sql> revoke all on table_in_demo1 from demo2;
Yes. It is allowing programmer to revoke.

Answer for Question 2:
sql> select * from table_in_demo1 where col2 = 21;
Explain plan from Demo1 schema:

Wow! What a surprise! Still it is using Demo2 index.

We try to insert one record.

sql> conn demo1/demo1
sql> insert into table_in_demo1 values (40,41);
sql> select * from table_in_demo1;
COL1 COL2
----- ------
10 11
20 21
30 31
40 41
Yes. It is allowing programmer to insert.

Read More

UNION = OR?

Question: Are UNION and OR doing to same or different?

Comments: We check with some data.

Scripts:

create table test_tab ( num1 number, num2 number );
insert into test_tab values(10,15);
insert into test_tab values(20,23);
insert into test_tab values(17,20);
commit;
select * from test_tab;
NUM1 NUM2
---------- ----------
10 15
20 23
17 20
Result of OR:
select * from test_tab where num1 = 10 or num2 = 20;

NUM1 NUM2
---------- ----------
10 15
17 20
Result of UNION:
select * from test_tab where num1 = 10
union
select * from test_tab where num2 = 20;
NUM1 NUM2
---------- ----------
10 15
17 20
Conclusion:
Wow! The result of Union and OR clause is same.
So
UNION=OR
Do not come to conclusion with above result. Check with some other scenario.
Question: UNION and OR will give same result ?

Comments: check with some other data.

Scripts:

drop table test_tab;create table test_tab
(
num1 number,
num2 number
);
insert into test_tab values ( 1,1);
insert into test_tab values ( 1,1);
Result of OR:

select * from test_tab where num1 = 1 or num2 = 1;
NUM1 NUM2
---------- ----------
1 1
1 1
Result of UNION:

select * from test_tab where num1 = 1
union
select * from test_tab where num2 = 1;
NUM1 NUM2
---------- ----------
1 1
Result of UNION ALL:
select * from test_tab where num1 = 1
union all
select * from test_tab where num2 = 1;

NUM1 NUM2
---------- ----------
1 1
1 1
1 1
1 1
Conclusion:
Yes! The result of Union and OR clause is not same.

So
UNION<>OR

Read More

Index scan on Null able column

Issue: Tune the below SQL script.

Comments: The statement is having the two conditions in the WHERE clause. One it is checking the number and another one is checking the NULL value.
Scripts:
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: It is going for the TABLE ACCESS FULL.
PlanSELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1
Comments: We know that, we have to create index on the “NAME” field.
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME)

Comments: Now we check the explain plan for the script.
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: Still it is going for the TABLE ACCESS FULL because the script is checking the NULL.

Plan
SELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1

Comments: Let we divide the SQL statement as a two part and we check the explain plan.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Good! Now partially it is going for the index scan. The second part of the query is going for the FULL TABLE access. The reason is, because the NAME is null able, the index on only on name and entirely null keys are NOT entered into b*tree indexes.

Plan
SELECT STATEMENT CHOOSE Cost: 158 Bytes: 162 Cardinality: 2
4 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 2 Bytes: 81 Cardinality: 11 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1


Comments: Still it is not going for the index scan. So we drop the index and create it on different way.
drop index NAME_IDX
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME,0)
Comments: Now we check the explain plan for the script.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Wow! Now it is going for the index scan and cost also very less.

Plan
SELECT STATEMENT CHOOSE Cost: 4 Bytes: 162 Cardinality: 2
5 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 3 Bytes: 81 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 2 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 1 Bytes: 81 Cardinality: 1
3 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1

Read More