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

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_bytesFROM dba_extentsGROUP by file_id) DE,(SELECT Max(bytes) free_bytes,file_idFROM dba_free_spaceGROUP BY file_id)...

Read More

TRUNCATE Vs Index Status

Comments: The Redo size of the current session SQL> select name,a.valuefrom v$sesstat a, v$sysstat bwhere 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.valuefrom v$sesstat a, v$sysstat bwhere b.statistic#=a.statistic#and b.name = 'redo size' and sid = 16;NAME VALUE -------------- ----------redo size 69912 Comments: create...

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

Read More

Query to identify the SID of current session

select sid from V$mystat where rownum ...

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(*)----------27189SQL> select name,a.value2 from v$sesstat a, v$sysstat b3 where b.statistic#=a.statistic#4 and b.name like 'redo...

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

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 1520 2317 20 Result of OR: select * from test_tab where num1 = 10 or num2 = 20;NUM1 NUM2---------- ----------10 1517 20 Result of UNION: select * from test_tab where num1 = 10unionselect * from test_tab where num2 = 20; NUM1 NUM2----------...

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: 11 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1Comments: We know that, we have to create index on the “NAME” field.create index NAME_IDX...

Read More