Wednesday, January 19, 2011

which situation EXIST condition is better than IN and IN condition is better than EXIST

which situation EXIST condition is better than IN and IN condition is better than EXIST
=======================================================================================

ANSWER:

Result of the subquery is small Then "IN" is typicaly more appropriate.
and
Result of the subquery is big/large/long Then "EXIST" is more appropriate.



suppose :-


select * from table_1
where id in (select Id from table_2)

Is normaly processed as:
select * from table_1 , ( select distinct y from table_2 ) t2
where table_1.x = table_2.y;

Means the subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table

and oposite is (EXIST)

select * from table_1 where exists ( select null from table_2 where table_2.y = table_1.x )
That is processed more like:
for x in ( select * from table_1 ) loop
if ( exists ( select null from table_2 where table_2.y = x.x ) then
OUTPUT THE RECORD end
end if
end loop

It always results in a full scan of Table_1 whereas the first query can make use of an index on Table_1(x).
Posted by at No comments:

Tuesday, January 18, 2011

Table Copy From One oracle Database to Another oracle database


Table Copy From One oracle Database to Another oracle database
==============================================================

using SQL*Plus COPY Command. (not dblink). It is very easy.
you have to TNS configure of that two remote database only .

[ The COPY command will be obsoleted in future releases of SQL*Plus.
COPY supports the datatypes listed for the COPY command,
but no new datatypes will be supported. ]


Copies data from a query to a table in a local or remote database.
COPY supports the following datatypes:

CHAR
DATE
LONG
NUMBER
VARCHAR2



COPY Command Syntax
-------------------

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query



sqlplus copy syntax help :-
------------------------

SQL> copy help=y

usage: COPY FROM TO { () } USING
: database string, e.g., hr/your_password@d:chicago-mktg
: ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
: name of the destination table
: a comma-separated list of destination column aliases
: any valid SQL SELECT statement

SQL>


example:-
---------


SQL> copy from scott/tiger@juncls to scott/tiger@test107
create empback using select * from dept ;


Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMPBACK created.

4 rows selected from scott@juncls.
4 rows inserted into EMPBACK.
4 rows committed into EMPBACK at scott@test107.

SQL>



Specifying Another User's Table
=================================

SQL> copy from scott/tiger@juncls to scott/tiger@test107
create empback using select * from hr.departments ;
====




Copying Data between Tables on One Database
============================================

You can copy data from one table to another in a single database (local or remote).
To copy between tables in your local database, specify your own username and password
and the service name for your local database in either a FROM or a TO clause (omit the other clause):

COPY FROM HR/your_password@MYDATABASE -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

To copy between tables on a remote database, include the same username, password, and
service name in the FROM and TO clauses:

COPY FROM HR/your_password@BOSTONDB -
TO HR/your_password@BOSTONDB -
INSERT EMPLOYEE_COPY2 -
USING SELECT * FROM EMPLOYEE_COPY

Posted by at 1 comment:
Labels:
Subscribe to: Posts (Atom)

My site statCount