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).
Wednesday, January 19, 2011
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
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
Subscribe to:
Posts (Atom)
My site statCount
