Back to basics Part-3

Welcome to part-3 of "Back to Basics" series.



After study of Part-1 and Part-2 ,We have platform ready (Means availability of Database, User, tablespace etc..) using which we can build our database building.



Here is list of database objects:

Table,

View,

Index,

Sequence,

Trigger,

Procedure,

Function,

Package.



You can use any of object from this list as per your requirement.

Let me give short explanation of each of these object.



Table are used for storing(organizing) data into it. Wheneve you want to keep data for your use you have to create table for that and need to insert your data into the table.



Views are define by select sql statement on table. Normally(I repeat Normally !) views does not contain any data, Actual data are stored in Table. When you execute select sql statement it returns data from the table. If you need same select sql statement often and it is much complex to write everytime then you can think to create view for that sql statement. Database keep mapping of view name and sql statement for which view is created.



Index are created on table to retrieve desire row of table more efficiently. How? this is out of context of this series.... Sorry! But will cover that as well in the future.



Remember that Table and Indexes are logical object. Means actual data are stored in data files. But as a user of DBMS it is not require to think that much in the depth. Just remember that data are stored, organize in Tables.



Sequence are independent object which is used to generate sequence of number, Mainly used in auto generation of Primary Key.



Trigger, Procedure, Function and Package are pl/sql block. Now, question is what is Pl/sql?

Pl/sql is database programming language for Oracle. For sql server T-sql is used for Database programming. Using pl/sql (or T-sql for sql server) you can write programming logic and sql statement which is gets executes in DBMS when you call it.

Back to basics Part-2

First thing that you should do in database is create user for your self. To create user write sql as given below:

SQL> create user temp identified by oracle default tablespace users temporary tablespace temp;

User created.

Still you can not use this user to login into database.To login using this user, first you need to grant some privilege to this user. Privilege need to login into database is : create session.

SQL> grant create session to temp;
Grant succeeded.

Now you can login into database, to do this just execute

SQL> conn temp/oracle
Connected.

Try to create table using sql given in Part-1. You will get error :)

User is ready, but still he has no space allocated in database into which he can store table after creation and he has no permission to create table as well. In database, storage is in terms of Tablesapce (It is logical name, actual storage is in datafile!!). So you need to grant some quota of tablespace to user temp, execute following sql for this:

SQL> alter user temp quota 5m on users;
User altered.
Here users is tablespace name created by Oracle during installation.

Now space is allocated(5 mb only), please modify this number as you wish and as you need.

To assign create table permission to user temp, grant create table privilege to temp user, to accomplish this execute sql as mention below.

SQL> grant create table to temp;
Grant succeeded.

Woohoo!! now you can create table, so create it and play some sql on it.

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