Oracle in World: 6/1/08

Saturday, June 7, 2008

How to Develop efficient Sql Statements-SQL Tuing

1)Verify Optimizer Statistics:
--------------------------------------------
The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.

So, gather statistics of all tables that are involved in SQL statements. You can check whether your statistics is up to date or not by querying
SELECT COUNT(*) FROM table_name;
and,
select NUM_ROWS from dba_tables where table_name='TABLE_NAME';

If they are almost same then you have correct optimizer statistics. If they don't match then gather new statistics.

2)Review the Execution Plan:
---------------------------------------
When tuning (or writing) a SQL statement, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.

We can check it by examine the optimizer execution plan following,

•The plan is such that the driving table has the best filter.

•The join order in each step means that the fewest number of rows are being returned to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).

•The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when many rows are being returned.

•Views are used efficiently. Look at the SELECT list to see whether access to the view is necessary.

•There are any unintentional Cartesian products (even with small tables).

•Each table is being accessed efficiently:

-Consider the predicates in the SQL statement and the number of rows in the table. Look for suspicious activity, such as a full table scans on tables with large number of rows, which have predicates in the where clause. Determine why an index is not used for such a selective predicate.

-A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.

If any of these conditions are not optimal, then consider restructuring the SQL statement or the indexes available on the tables.

3)Restructuring the SQL Statements
----------------------------------------------
Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement.

While restructuring the SQL statements keep in mind of the following issues.

•Use equijoins whenever possible.
That is compose predicate using AND and =.

•Avoid Transformed Columns in the WHERE Clause.
That is use
a=b instead of to_number(a)=to_number(b)

When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement; if you have index on varcol

TO_CHAR(numcol) = varcol

rather than

varcol = TO_CHAR(numcol)

•Write Separate SQL Statements for Specific Tasks.
SQL is not a procedural language. Using one piece of SQL to do many different things usually results in a less-than-optimal result for each task. If you want SQL to accomplish different things, then write various statements, rather than writing one statement to do different things depending on the parameters you give it.

It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator.

•Use of EXISTS versus IN for Subqueries.
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

4)Controlling the Access Path and Join Order with Hints
--------------------------------------------------------------------------------------
You can use hints in SQL statements to instruct the optimizer about how the statement should be executed. Hints, such as /*+FULL */ control access paths.

Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:

•Avoid a full-table scan if it is more efficient to get the required rows through an index.

•Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.

•Choose the join order so as to join fewer rows to tables later in the join order.

•Be careful when joining views, when performing outer joins to views, and when reusing an existing view for a new purpose.

•Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data.

•Beware of writing a view for one purpose and then using it for other purposes to which it might be ill-suited. Querying from a view requires all tables from the view to be accessed for the data to be returned. Before reusing a view, determine whether all tables in the view need to be accessed to return the data. If not, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. The goal is to refer to the minimum number of tables and views necessary to return the required data.

•An outer join within a view is problematic because the performance implications of the outer join are not visible.

•Consider using materialized views.

5)Restructuring the Indexes
-----------------------------------------------
Often, there is a beneficial impact on performance by restructuring indexes. This can involve the following:

Remove nonselective indexes to speed the DML.
Index performance-critical access paths.
Consider reordering columns in existing concatenated indexes.
Add columns to the index to improve selectivity.

6)Modifying or Disabling Triggers and Constraints
-----------------------------------------------------------
Using triggers consumes system resources. If you use too many triggers, then you can find that performance is adversely affected and you might need to modify or disable them.

7)Restructuring the Data
-----------------------------------------
After restructuring the indexes and the statement, you can consider restructuring the data.

Introduce derived values. Avoid GROUP BY in response-critical code.

Review your data design. Change the design of your system if it can improve performance.

Consider partitioning, if appropriate.

8)Combine Multiples Scans with CASE Statements
-----------------------------------------------------------
Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance.

9)Maintaining Execution Plans Over Time
-------------------------------------------------------
You can maintain the existing execution plan of SQL statements over time either using stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.

Automatic SQL Tuning Features

Manual SQL tuning process poses many challenges to the application developer. So, the SQL tuning process has been automated by the automatic SQL Tuning manageability features.

Below is the feature name and the associated task can be done by them.

1)ADDM
----------
Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database, including high-load SQL statements.

To access ADDM from Home page>Advisor Central>Click ADDM under Advisor or to see previous ADDM findings and Recommendation under Advisor Tasks select ADDM from drop down menu. Also look at various filter options along with it.

2)SQL Tuning Advisor
-------------------------------
SQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements.

To access SQL Tuning Advisor from Home page>Advisor Central>Click SQL Tuning Advisor.

3)SQL Tuning Sets
------------------------------
When multiple SQL statements are used as input to ADDM or SQL Tuning Advisor, a SQL Tuning Set (STS) is constructed and stored. The STS includes the set of SQL statements along with their associated execution context and basic execution statistics.

4)SQLAccess Advisor
--------------------------
In addition to the SQL Tuning Advisor, Oracle provides the SQLAccess Advisor that provides advice on materialized views, indexes, and materialized view logs. The SQLAccess Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. The SQLAccess Advisor considers the trade-offs between space usage and query performance and recommends the most cost-effective configuration of new and existing materialized views and indexes.

To use SQL Access Advisor from Home page>Advisor Central>Click SQL Access Advisor.

Information to Gather During Tuning

While tune the SQL statement it is needed to determine high load sql statements along with their associated indexes. In the following section the information that is needed to gather during tuning is listed.

•Complete SQL text from V$SQLTEXT

•Structure of the table like in SQL*PLUS DESCRIBE table_name;

•Definitions of any indexes (columns, column orderings), and whether the indexes are unique or non-unique.

•Optimizer statistics for the segments (including the number of rows each table, selectivity of the index columns), including the date when the segments were last analyzed.

•Definitions of any views referred to in the SQL statement.

•Repeat steps two, three, and four for any tables referenced in the view definitions found in step five.

•Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN, or the TKPROF output).

•Any previous optimizer plans for that SQL statement.

It is important to generate and review execution plans for all of the key SQL statements in your application. Doing so lets you compare the optimizer execution plans of a SQL statement when the statement performed well to the plan when that the statement is not performing well. Having the comparison, along with information such as changes in data volumes, can assist in identifying the cause of performance degradation.

Ways of Identify High Load ot top SQL

There is various ways by which we can identify high load or top sql statements. The ways are listed below.

1)Automatic Database Diagnostic Monitor (ADDM)

2)Automatic Workload Repository(AWR)

3)V$SQL view

4)Custom Workload

5)SQL Trace

If you want to know the sql to identify high load sqls then the following post will help you.

http://arjudba.blogspot.com/2008/09/how-to-find-top-sql-through-queries.html

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-invoke-collect-and-run-awr-and.html

http://arjudba.blogspot.com/2008/09/how-to-find-top-sql-through-queries.html

http://arjudba.blogspot.com/2008/08/difference-or-advantage-between-awr-and.html

Goals for Tuning.

Objectives of Tuning the System:
---------------------------------------
The objectives of tuning a system can be either anyone of the two below.

1)To reduce the response time for end users of the system. Take it as a special care if your database response time is not optimal.
2)To reduce the resources used to process the same work. Take it as special care if you have limited hardware resource.

We can achieve both our objectives in three ways.

A)Reduce the Workload: SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.

B) Balance the Workload: Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime. If noncritical reports and batch jobs can be scheduled to run in the nighttime and their concurrency during day time reduced, then it frees up resources for the more critical programs in the day.

C) Parallelize the Workload: Queries that access large amounts of data (typical data warehouse queries) often can be parallelized. This is extremely useful for reducing the response time in low concurrency data warehouse. However, for OLTP environments, which tend to be high concurrency, this can adversely impact other users by increasing the overall resource usage of the program.

Three basic steps of SQL tuning.

Probably you have heard many times about performance tuning of Oracle. And one of the major part of performance tuning is to tune the SQL statement so that it generates better execution plan and performs well. It will be easy to tune the SQL statements if we remember three basic steps of SQL tuning. The three basic steps of SQL tuning is listed here.

Step1: At first we need to identified the high load SQLs or top SQLs that are responsible for performance problem or in another word the sql that consume application workload and system resources. We can easily identify them by reviewing past SQL execution history available in the system.

Step 2: Now generate execution plan of those high load sql statements and verify that the execution plans produced by the query optimizer for these statements perform reasonably.

Step 3: Implement corrective actions to generate better execution plans for poorly performing SQL statements. While taking corrective actions there is many considerations. Hopefully I will discuss these consideration in my blogs one by another.

Tuesday, June 3, 2008

Example of Antijoin, Semijoin, Curtesian Product,Self join

Antijoin Example:
------------------------
If I want to wish to select a list of students who are not in a particular set departments that I can use antijoin as below.

SQL>SELECT * FROM student
WHERE deptid NOT IN
(SELECT deptid FROM dept
WHERE deptid = 3)
ORDER BY NAME;
STDID NAME DEPTID
---------- --------------- ----------
24101 Raju 1

Semijoin Example:
----------------------
Whenever only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery.
SQL>SELECT * FROM dept
WHERE EXISTS
(SELECT * FROM student
WHERE dept.deptid = student.deptid
)
ORDER BY deptname;
DEPTID DEPTNAME
---------- ----------
3 CSE

Crossjoin Example: Cartesian Product
---------------------------------
SQL> insert into student values(22440,'Adu',2);
1 row created.

SQL> select name,deptname from dept CROSS JOIN student;
NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Adu EEE
Rafi CSE
Raju CSE
Arju CSE
Adu CSE

8 rows selected.

Self Join Example:
---------------------
A self join to return the name of each employee along with the name of the employee's manager.
SELECT e1.last_name||' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id

Related Documents:
------------------------
Joins in Oracle
Example of Outer and Equijoin
Difference between inner join and outer join in oracle
Difference between join,inner join, natural join and equijoin in oracle

Monday, June 2, 2008

how to take backup of oracle database from remote system

How to take backup of oracle database from remote system

In order to take oracle database backup from remote system you have to ensure the following things.

1)The database in which you will take backup is called the target database. The target database must be in mount or open state.

2)The target database listener must be running.

3)The user connected t the target database and eventually take backup must have SYSDBA privilege. So the user by which you connect that user entry must be in the password file.

In the following section I will take backup of machine neptune and database name is dbase. I will take backup from another machine named saturn.

Step 1:
Log on to neptune machine and see the listener service name and listener port number and ensure that listener is running. If you knew listener service name and port number previously and currently it is running then you can avoid this step.

In machine neptune invoke lsnrctl status.

SQL> !lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 03-JUN-2008 02:24:26

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 03-JUN-2008 02:24:09
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbase" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
The command completed successfully.

Here the interested subject is HOST neptune, PORT=1522 and Service dbase

If you got these property then go to step 2.
If you got TNS-00511 then start the listener and register system with the listener.

SQL> host lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 03-JUN-2008 02:23:54

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=neptune)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Solaris Error: 146: Connection refused

SQL> host lsnrctl start

SQL> alter system register;
System altered.

Step 2:
Log on to the target database. And add the user to password file. This is donr by invoking SYSDBA privilege to the user. If the user name is already entry ed in the passowrd file then ignore this step.

In target database I granted SYSDBA privilege to user ARJU which will take remote backup from other machine.

SQL> CONN / AS SYSDBA
Connected.
SQL> select username, sysdba from v$pwfile_users;

USERNAME SYSDB
------------------------------ -----
SYS TRUE

SQL> GRANT SYSDBA to ARJU;
SQL> select username, sysdba from v$pwfile_users;

USERNAME SYSDB
------------------------------ -----
SYS TRUE
ARJU TRUE

Step3:
Connect to the machine from which you want to take backup and invoke rman. The format you need to connect is,
rman target user_name/password_of_target_database@target_database_host_name:port_number/service_name
We already got this information by invoking lsnrctl status. So, enter that.
-bash-3.00$ rman target arju/a@neptune:1522/dbase

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 3 02:50:14 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DBASE (DBID=1509380669)

Step4:
Perform backup operation. Like
RMAN> backup database;

Starting backup at 03-JUN-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
.
.
.

Related Documentation:
Database Authentication
What is Remote Login Passwordfile

Difference between join, Inner join,Equijoin and Natural Join

An inner join is a join with a join condition that may contain both equality or non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign.
So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keywoed. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed. In the example it is also shown.
The following example will make you more clear.

In this example I used data as in example of Difference between Inner join and Outer join
SQL> select s.name,d.deptname from dept d, student s where d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

This example represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

Above example also represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid<>s.deptid;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE

Above example represents an inner join but not a equijoin.
SQL> select s.name,d.deptname from dept d JOIN student s on d.deptid<>s.deptid;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE

Above example show JOIN and INNER join keyword is same. If we don't specify INNER then by default inner join is performed.

Now let's have a look at natural join.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NUMBER
DEPTNAME VARCHAR2(10)

SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
STDID NUMBER
NAME VARCHAR2(15)
DEPTID NUMBER

After describing both table we see both table have a same column name deptid. Now if I perform natural join then tables with same column name is joined.
SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

As both table have same column deptid so deptid is joined.

Now I rename deptid column and see the result. We will notice in that case inner join will be performed.

SQL> alter table student rename column deptid to deptid1;
Table altered.

SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.

As both table don't have same column name so normal join/inner join is performed.
SQL> select s.name,d.deptname from dept d, student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.

SQL> select s.name,d.deptname from dept d CROSS JOIN student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.
Related Document:
-----------------------
Difference between Inner join and Outer join
Joins in Oracle

Difference between inner join and outer join

An inner join will return a row only if there is a joined row with data in both tables- being joined. An outer join will return a row even if the other table doesn't have a corresponding row.

With an example I will make the scenarios clear. I will create two tables named student and dept. In the student table there will be one department but the corresponding department is not exist in dept table. Now I perform both inner join and outer join on deptid column.

SQL> create table student(stdid number, name varchar2(15),deptid number);
Table created.

SQL> insert into student values (024413,'Rafi',3);
SQL> insert into student values (024101,'Raju',1);
SQL> insert into student values (024434,'Arju',3);

SQL> create table dept(deptid number,deptname varchar2(10));
Table created.

SQL> insert into dept values(2,'EEE');
SQL> insert into dept values(3,'CSE');


SQL>  select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE

See in case of INNER join rows return only if joined row exist with data in both tables.


SQL> select s.name,d.deptname from dept d RIGHT OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Arju            CSE
Rafi            CSE
Raju


SQL> select s.name,d.deptname from dept d LEFT OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE
                EEE


SQL> select s.name,d.deptname from dept d FULL OUTER JOIN student s on d.deptid=s.deptid;
NAME            DEPTNAME
--------------- ----------
Rafi            CSE
Arju            CSE
                EEE
Raju

As we see in case of OUTER join it return a row even if the other table doesn't have a corresponding row.

Related Documents:
Joins in Oracle

Examples of All outer joins and Equijoins in oracle

In previous post I have define all types of joins in oracle. In this post I will show example of all outer joins one by one. Also I have shown an example of equijoins. Hopefully I next post I will give example of semijoin, antijoin, selfjoin and cartesian product.

To demonstrate example I have used two table customers and orders and insert some data into it.

/* create the customer table */

Create Table Customers (
CustNo Integer Not Null Primary Key,
CustName Char (20),
Address Char (40)
);

/* create the orders table */

Create Table Orders (
OrderNo Integer Not Null Primary Key,
CustNo Integer,
OrderDate Date
);

/* put some data into the customer table */

Insert into Customers Values (1,'P. Jones','Leeds');
Insert into Customers Values (2,'A. Chan','Hong Kong');
Insert into Customers Values (3,'K. Green','Columbia');
Insert into Customers Values (4,'B. Smith','Leeds');
Insert into Customers Values (5,'A. Khan','');

/* put some data into the orders table */

Insert into Orders Values (1,1,'24-JAN-96');
Insert into Orders Values (2,1,'31-JAN-96');
Insert into Orders Values (3,2,'04-FEB-96');
Insert into Orders Values (4,4,'12-FEB-96');
Insert into Orders Values (5,6,'12-FEB-96');

Now have a look at both tables' data.
SQL> select * from customers;

CUSTNO CUSTNAME ADDRESS
---------- -------------------- ----------------------------------------
1 P. Jones Leeds
2 A. Chan Hong Kong
3 K. Green Columbia
4 B. Smith Leeds

SQL> select * from orders;

ORDERNO CUSTNO ORDERDATE
---------- ---------- ---------
1 1 24-JAN-96
2 1 31-JAN-96
3 2 04-FEB-96
4 4 12-FEB-96
5 6 12-FEB-96

Example of Left Outer Join:
-------------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo = o.CustNo(+) and c.address='Leeds';

or recommended later 9i syntax,
SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo
where c.Address = 'Leeds'; 2 3 4
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
B. Smith 12-FEB-96 Leeds

Right Outer Join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo(+) = o.CustNo and o.OrderDate > '31-JAN-96';

CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
or recommended later 9i syntax
SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Full outer join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';

CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Without condition here is the example of full, left and right outer join.
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia
12-FEB-96

6 rows selected.

SQL> select CustName, OrderDate, Address
from Customers c right outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 31-JAN-96 Leeds
P. Jones 24-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia

Example of Equijoins
--------------------------
SQL> select address, orderdate from customers c, orders o where c.custno=o.custno order by orderdate;

ADDRESS ORDERDATE
---------------------------------------- ---------
Leeds 24-JAN-96
Leeds 31-JAN-96
Hong Kong 04-FEB-96
Leeds 12-FEB-96

Joins in Oracle.

In this section I have given definition of several join terms. In another section inshallah I will demonstrate example of joins in oracle.
What is Join?
---------------------
•In a straightforward a join is a query that combines rows from two or more tables, views, or materialized views.

•Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query.

•In join if any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions
-------------------------
•Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause.

•The join condition compares two columns, each from a different table.

•To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.

•To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result.

•The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.

•WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

Types of Joins in Oracle:
-----------------------------------
A)Equijoins
------------------------
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

B)Self Joins
-----------------
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.

C)Cartesian Products
--------------------------
If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful.

D)Inner Joins
-------------------
An inner join is a join of two or more tables that returns only those rows that satisfy the join condition. It is also called simple join.

E)Outer Joins
------------------------
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Outer join can be categorizes into three.
a)Left Outer Join: To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

b)Right Outer Join:To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

c)Full Outer Join:To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

E)Antijoins
---------------
An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the subquery on the right side.

F)Semijoins
---------------------
A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Semijoin and antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause.

Default Column Values in Oracle.

Sunday, June 1, 2008

ORA-12083 while Dropping Table

How to Convert Long data type to Lob

It is relatively easy if you want to convert long datatype to lob data type.
This section describes the following techniques for migrating existing tables from LONG to LOB datatypes:

A)Using ALTER TABLE to Convert LONG Columns to LOB Columns

B)Copying a LONG to a LOB Column Using the TO_LOB Operator

C)Online Redefinition of Tables with LONG Columns where high availability is critical

D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility

With an example I have demonstrate these procedures.
A)Using ALTER TABLE to Convert LONG Columns to LOB Columns
----------------------------------------------------------------------

SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (2,'This is the second entered row');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> ALTER TABLE TEST_LONG_LOB MODIFY B CLOB;
Table altered.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB

B)Copying a LONG to a LOB Column Using the TO_LOB Operator
---------------------------------------------------------------------
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TABLE TEST_CLOB AS SELECT A, TO_LOB(B) B FROM TEST_LONG_LOB;
Table created.

After you ensure that the data is accurately copied, you can drop the original table and create a view or synonym for the new table using one of the following sequences:

SQL> DROP TABLE TEST_LONG_LOB;
Table dropped.

SQL> CREATE VIEW TEST_LONG_LOB AS SELECT * FROM TEST_CLOB;
View created.

or

SQL> DROP TABLE TEST_LONG_LOB;
Table dropped.

SQL> CREATE SYNONYM TEST_LONG_LOB FOR TEST_CLOB;
Synonym created.

or rename the table
SQL> RENAME TEST_CLOB TO TEST_LONG_LOB;

Table renamed.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B CLOB

C)Online Redefinition of Tables with LONG Columns where high availability is critical
---------------------------------------------------------------------------------------------
1)This is the table that need to change LONG data.
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> COMMIT;
Commit complete.

2) Determine if the table is a candidate for online re-organization
SQL> exec dbms_redefinition.can_redef_table('ARJU','TEST_LONG_LOB');
PL/SQL procedure successfully completed.
A primary key is mandatory since materialized views and logs are created during the start of redefinition.

3)Create an Interim Table.

SQL> CREATE TABLE TEST_LONG_LOB_INT(A NUMBER NOT NULL, B CLOB);
Table created.

Note that interim table has no primary key.

4)Start the re-organization process
SQL>declare
col_mapping varchar2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'a a , '||
'to_lob(b) b';

dbms_redefinition.start_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT', col_mapping);
END;
/
PL/SQL procedure successfully completed.
Here Arju is username.

5)Run dbms_redefinition.copy_table_dependents
SQL>declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT',
1, true, true, true, false,
error_count);

dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
PL/SQL procedure successfully completed.
6)Execute dbms_redefinition.finish_redef_table procedure.

exec dbms_redefinition.finish_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT');
PL/SQL procedure successfully completed.

SQL> DROP TABLE TEST_LONG_LOB_INT;
Table dropped.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB

D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility
------------------------------------------------------------------------------------------
If you are exporting data as part of a migration to a new database, create a table on the destination database with LOB columns and Data Pump will call the LONG-to-LOB function implicitly.
Serach within my blog about data pump export or import.

Related Documents

Large Object (LOB) Datatypes with Example.
Advantages of LOB against LONG- Feature of LOB datatype
How to move LOB data to another tablespace
How to Insert Blob data(image, video) into oracle BLOB size
Char, Varchar2, Long etc Datatype Limits in Oracle

How to check which option is not installed to your database

Based on your database version you may be restricted to use several options of your database or you may wish not to install several database options. With this options feature I have a post in ORA-00439. Now in this post I have shown how you can check or see which options is installed or which option is installed on to your system.

SQL>select ' You have installed '||parameter||' option'
from v$option
where value = 'TRUE';

you can check or see which options is not installed or which option is installed on to your system by following query,

select ' The '||parameter||' option is not installed'
from v$option
where value <> 'TRUE';

The Real Application Clusters option is not installed
The Oracle Label Security option is not installed
The Data Mining Scoring Engine option is not installed

Related Documents:
------------------------
ORA-00439

How to Make Money Online! Please Read it Full

Probably you get e-mail or message from many one about making money online. They ask you to register for a account or to click their advertisement and they offer money free of cost. But the reality is they try to bluff you in order to advertise their site. In this document I will give a whole procedure of how you can make money online. Please read it carefully and at least read it full.

In the following section I demonstrate what you need to do to make money online step by step.

Step1: The first thing you need is an website. It is better if you own it. If not then you can create blog and from there you can start. You can get free blog from http://www.blogger.com. Before creating a blog you must have a gmail account. You can get gmail account from http://mail.google.com

Step2:
---------
After creating a blog the next step is to create an adsense account. Before applying to adsense note that, you must do some blog posts. Posts must be unique and useful to readers. Too few posts and copied content will not be approved by adsense. To open an account click on http://google.com/adsense . With this adsense account you can place advertisement on your page and based on that advertisement you can earn money. Don't be in a hurry. Slow and steady wins the race. It takes time to active adsense account.

Step3:
-------------
After your adsense account is activated you can place advertisement on your site. Like as you see in my page the advertisement is displayed on the left side. In your page they also will be displayed. Anyone enter in your page and clicking on those links will benefit you to get revenue. But never click your own advertisements. In that case you may be barred from adsense forever. You can place adsense in in two ways. From adsense home copy html and place it inside your site. Also if you use blogsopt then from monetize section you can place ads on to your site.

Don't think about how you can get money. Google is generous enough to send cheque on a monthly basis to your address. In Bangladesh still Western Union is not supported. But I hope soon Google will support Western Union to send money.

Other considerations:
---------------------------
Google adsense revenue is not so much and hence you may look further to earn money.
One of the more lucrative forms of income is placing specific advertisers Ad's directly onto your site, you don't get paid if they click the ad but you do get paid a commission if they buy a product and this can be very profitable indeed. This is referral ads. Suppose in my page you can see referral ads of to get firefox with google toolbar.

For lucrative forms of income types of ad's you will need to register with sites like buy.at ,trade doubler.com, Cpays.com, 888.com, ClickBank.com. These sites have most of the major advertisers from all over the world registered with them, they will check your site content to see if your suitable for certain advertisers before letting you go full steam ahead, but all in all so long as you have valid content which isn't harmful to the advertisers then they are usually quite happy to have you on the book's.

One more final thing I recommend you to use google adwords to monetize your website. You have to pay money for that but you can get a greater value against that. I've posted the link for adwords at the lower left corner of this page, I would recommend you spend as much or as little as you can afford. And I also recommend you to use firefox as a browser which is fast and reliable. You can get it in this page at left middle side.

Related Documents
List of Available Advertising Network Companies

How to get Oracle Error Message from Database or OS

If you are on UNIX platform then from unix machine using unix command you can easily get error description, cause and action easily. You don't need to go to internet and then search error about it immediately. Suppose you got the error ORA-04043 and now you want to know the cause of the error and action of the error. From Unix machine (Linux, Solaris etc) you can easily get it by using oerr command. In order to use it For example, if you want to get cause and action of ORA-7300, then "ora" is the facility and "7300" is the error. So you should type "oerr ora 7300".

If you get LCD-111, type "oerr lcd 111", and so on.

Below is an example.

SQL> desc t;
ERROR:
ORA-04043: object t does not exist

SQL> !oerr ora 04043
04043, 00000, "object %s does not exist"
// *Cause: An object name was specified that was not recognized by the system.
// There are several possible causes:
// - An invalid name for a table, view, sequence, procedure, function,
// package, or package body was entered. Since the system could not
// recognize the invalid name, it responded with the message that the
// named object does not exist.
// - An attempt was made to rename an index or a cluster, or some
// other object that cannot be renamed.
// *Action: Check the spelling of the named object and rerun the code. (Valid
// names of tables, views, functions, etc. can be listed by querying
// the data dictionary.)

Similarly you can check other error message too.

Like,
bash-3.00$ oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number

However if you are not on UNIX platform you still can get your desired error message description.
In that case you have to use the function SQLERRM.
The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the ORA-0000: normal, successful completion message. Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message.

An Example:
------------------
SET SERVEROUT ON
prompt Please enter error numbers as negatives. E.g. -1
prompt
exec dbms_output.put_line('==> '||sqlerrm( &errno ) );
/
SQL> Enter value for errno: -7445
==> ORA-07445: exception encountered: core dump [] [] [] [] [] []

Related Documents
Types of Oracle Error Message

How to get the time difference between two date columns?

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let's investigate some solutions. Test data:

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
1
.041666667
.000694444

Solution 1:
---------------------------------------------

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 || ' HOURS ' ||
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5 || ' MINUTES ' ||
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10 || ' SECS ' time_difference
11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS

Solution 2:
-----------------------

If you don't want to go through the floor and ceiling maths, try this method.

SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00

Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle