Back to basics Part-1

Folks,
Hope you all doing well! After listening to so many basic questions from beginners, I decide to post some database basic things that helps beginners to work with database.

First thing, that you need is location(url) from where you can get database software to install? You can get database software from oracle's website itself, its free for development but, If you want to use it commercially, you have to first purchase license for that.

After downloading it, just follow some steps as instructed by Oracle installer software and you have database ready in your machine.
There are 3- different types of installation are possible 1. Enterprise edition 2.Standard edition 3.Personal Edition, Select Enterprise edition. Please note down password that you specify during installation. Later on, by using this password you can login into database as SYS user.

Here is good step by step explanation for oracle installation on windows

Once installation is complete follow below steps:

To login into database and to create test table just follow steps mention below(Windows OS):

start>cmd
c:>sqlplus sys/password1 as sysdba
sql>create table test(col1 number,col2 varchar2(100),col3 char(10));

Please note that it is not recommended to create table under sys account. In Part-2 we will learn how to create other user/schema in database.

Delete large amount of data....

If you are at home and want to delete large data from tables then just go ahead and delete it. No problem at all.


But ! But, What to do when you have a task to delete some unused/bad data (Usually these number is large) from very critical large table.


Before executing well known delete sql statement you need to think twice(or may be thrice ...counting....). first you should ask yourself whether delete is require, if yes then you need to calcualte amount of data that you want to delete, and then comapre that amount with actual number of records of table. If amount of unused rows exceed 65%(Approx., Dont expect rule of thumb here) of total size of table then transfer good data into some other identical table,drop old one, create require index in new table....and its done.(Yes! In most cases its done!!)


You need to take lots of other thing in consideratin as well, before executing delete operation

Some are:
First identify what is off-peak hours for your business.
calcualte size of data that you need to delete.
estimat amount of redo that might generate.
estimat time that it would take to complet delete operation, rebuilding indexes etc..
Try to devide whole delete operation in small chunks.
Use paralle and nologgin operation (With care, obviously !).

Recent interview

Hi,
Recently I was interviewed by one big company's very qualified DBA.

After some of question we have debate on how to trace and how to format trace file.

I told him that we can do it using tkprof, but he don't want to believe this as he stuck to his belief of oradebug. He said that if you want to format trace file there is one way and that's OraDebug only.

I am sure that tkprof do it better way.

I hope most of you are agree with me...if not let me correct

Left outer Join converted into Inner Join Part-2

Here is the detailed description of problem mentioned here.

CREATE TABLE TEMPA
(
P_UNIQUEID NUMBER(8),
COMPCODE NUMBER(4),
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
PRODUCTCD VARCHAR2(50 BYTE),
STARTDATE DATE,
DISCONTINUEDATE DATE
)

CREATE TABLE TEMPE
(
COMPCODE NUMBER(4) NOT NULL,
PRODUCTCD VARCHAR2(50 BYTE) NOT NULL,
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
SCHEMECD NUMBER(8) NOT NULL,
PARTYCODE VARCHAR2(10 BYTE),
SCHEMEFRDATE DATE NOT NULL,
SCHEMETODATE DATE NOT NULL,
SCHEMESHORTDESC VARCHAR2(30 BYTE) NOT NULL
)

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5233, 3, 1, 3, 999, 'Product-1', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5234, 3, 1, 3, 999, 'Product-2', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD,SCHEMECD, PARTYCODE, SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 14, 999,1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, SCHEMECD, PARTYCODE,SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 3, 999, 1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Commit;

Now lets check result of our sql statement in both 9i and 10g database.

Result of query on Oracle 9i Database (Oracle version 9.2.0.1.0, RHEL 2.1)

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234


Result of query on Oracle 10gR2 Database

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner


As you notice we have different result set for Oracle 9i and Oracle 10g(For Oracle 11g as well). The reason is in Oracle 9i we execute left outer join query(At least we believe so!!) and Oracle execute it as left outer join operation. While in Oracle 10g, same sql statement executed as inner join operation.

To confirm inner join here is execution plan for query:-

Execution Plan
----------------------------------------------------------
Plan hash value: 3150946153

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 248 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 248 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEMPE | 1 | 138 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEMPA | 2 | 220 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

The problem is the Sql is not written properly for left outer join operation. If you move some part of where clause up with ON clause then Oracle will perform Left outer join. See this:-

SQL> ed
Wrote file afiedt.buf
1 select a.p_uniqueid,e.schemeshortdesc,e.userid,e.partycode,e.schemefrdate,e.schemetodate
2 from tempa a
3 left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
4 and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
5 and e.partycode in('A0101A0199','999') and
6 e.userid<>'0' and e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and 7 e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
8 where
9 a.compcode=3 and
10 a.countrycd in(1,999) and
11 a.statecd in(3,999) and
12 a.districtcd in(14,999) and
13* '31-December-2010' between a.startdate and a.discontinuedate
14 /

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234

Execution Plan
----------------------------------------------------------
Plan hash value: 965687122

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 232 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 2 | 232 | 9 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEMPA | 2 | 114 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 59 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEMPE | 1 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Left outer Join converted into Inner Join

Last week one of my friend ask me,
"Why database return different result set when we upgrade from Oracle9i to Oracle 11g?"

He mean to say that, sql statement which return correct result in Oracle 9i, is not returning same result set in Oracle 11g.

I immediately told him to check data, May be data are not properly migrated. But I was wrong as he already validate that. Then I ask for table structure and sql statement. After analysis of sql statement I come to the conclusion that new Oracle 11g(and even 10g) optimizer may ignore left outer join and instead perform Inner join.

This is possible when you have not written sql join properly or sometime because of ANSI sql.

I will post detailed scenario and explanation later on this week.

Extra Shot:-
Detailed description available here