NOT IN / NOT EXISTS

 It is recommend to use NOT EXIST instead of NOT IN ; do you know/thought  why ?  ! .




You dont see any differences here because the tables are small with less data and also there will not be any difference in the explain plan too.For very large tables performance improvement are seen by using NOT EXISTS instead  of  NOT IN ; so test it and see whats the difference .


  EXAMPLE

   create table oracle4u ( orclid number(10), constraint pk primary key(orclid) ,s_name varchar2(100) );


   create table "oracle4u.com" ( orcl4id number(10), constraint opp primary key(orcl4id) ,s_name varchar2(100) );


   insert into oracle4u select rownum,OBJECT_NAME  from SYS.ALL_OBJECTS;


   insert into oracle4ucom select rownum,TABLE_NAME  from SYS.ALL_tables union select null,'TEST'  from dual

commit;
-------------------


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from oracle4ucom fg)


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from   oracle4ucom fg where fg.orcl4id is not null)


select * from oracle4u pp where  not exists  (select fg.orcl4id  from oracle4ucom fg where pp.orclid=fg.orcl4id )


select *  from oracle4u pp  Left Outer JOIN oracle4ucom fg ON pp.orclid=fg.orcl4id  WHERE fg.orcl4id IS NULL

Oracle Cloud

-----------------------------

ORA-20229: Workspace Manager Error

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

ORA-20229: statement 'delete from mdsys.sdo_geom_metadata_table' failed during EnableVersioning. Error:
ORA-20229: statement 'select count(*)
       from mdsys.sdo_geom_metadata_table
       where upper(
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1



when this error occurs


delete the GOLF_YARD_lt  entry from user_sdo_geom_metadata table
and then execute

EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

Even if  its not working !

Then check whether a trigger exist on GOLF_YARD table or GOLF_YARD view ie : ( when
disabling GOLF_YARD that trigger may not be dropped) .That trigger is now restricting you to version enable your table GOLF_YARD drop the trigger/triggers from GOLF_YARD

and then execute


EXEC DBMS_WM.ENABLEVERSIONING('GOLF_YARD');

it will works sure

---------

Outer Join tends to Complex

  Left outer join retreives all rows in the table1 which is left table ;even if there is no match with table2.


 Right outer join retreives all rows in the table1 which is right table ;even if there is no match with table2.
 
Example
 
CREATE TABLE DEPT_MASTER
(DEPT_ID NUMBER(2) Primary key,
DEPT_DESC VARCHAR2(100 BYTE) NOT NULL,
S_DESCRIPTION VARCHAR2(100 BYTE),
N_STATUS NUMBER(1) DEFAULT 0 NOT NULL,
D_SYSDATE DATE DEFAULT sysdate);
CREATE TABLE USER_DETAILS
( USER_ID NUMBER(10) Primary key,
FIRST_NAME VARCHAR2(50 BYTE) NOT NULL,
LAST_NAME VARCHAR2(50 BYTE) NOT NULL,
DEPT_ID NUMBER(2),
ACTIVE_STATUS NUMBER(1) NOT NULL,
GROUP_ID NUMBER(2) NOT NULL);

ALTER TABLE USER_DETAILS ADD ( CONSTRAINT FK_USER_DETAILS_DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT_MASTER (DEPT_ID));

Insert some data in both tables.

Old Fashion Left Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC  from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;
New  Fashioned  Left Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d left outer join dept_master dd on D.DEPT_ID=DD.DEPT_ID order by 1;

Old Fashion Right Outer Join prior to Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID   order by 1;

New  Fashioned  Right Outer join from Oracle 9i
select d.user_id,DD.DEPT_DESC from user_details d right outer join dept_master dd on d.DEPT_ID=DD.DEPT_ID order by 1;

------------------------------------------------------------------

Here the scope of this article is not meant to describe this in detail , only to analyze in detail about the Old fashioned outer join it becomes complex and complicated , we can just have a look on it.

Left outer join can be written as below in 3 ways

a) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,left table and (+) outer notation in left to right table column

b) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where dd.DEPT_ID(+)=D.DEPT_ID order by 1;-- left outer join ,right table and (+) outer notation in left to left table column

c) select d.user_id,DD.DEPT_DESC from user_details d,dept_master dd where d.DEPT_ID=Dd.DEPT_ID(+) order by 1;-- left outer join (actual) ,left table and (+) outer notation in right to right table column

 Right outer join can be written as below in 3 ways
 

a) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where D.DEPT_ID(+)=Dd.DEPT_ID order by 1;-- right outer join (actual) right table and (+) outer notation in left to left table column

b) select d.user_id,DD.DEPT_DESC from user_details d , dept_master dd where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

c) select d.user_id,DD.DEPT_DESC from dept_master dd,user_details d where DD.DEPT_ID=D.DEPT_ID(+) order by 1 ;

Normal Join


1) select d.user_id,DD.DEPT_DESC from user_details d  join dept_master dd on d.dept_id=dd.dept_id;

2) select d.user_id,DD.DEPT_DESC from user_details d natural join dept_master dd ;

3) select d.user_id,DD.DEPT_DESC from user_details d join dept_master dd using(DEPT_ID) ;

-------------

Oracle WorkspaceManager ErrorC0des

 
exec dbms_wm.enableversioning('TABLEA');

ORA-20129: table 'TABLEA' does not exist
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version enabling  


----------------------------------------------------------------


exec dbms_wm.disableversioning('TABLEA');

ORA-20132: table 'TABLEA' is not version enabled
ORA-06512: at "WMSYS.LT", line 9355
ORA-06512: at line 1

This error is due to that the table does not exist in the current schema for version disabling

---------------------------

 exec dbms_wm.enableversioning('USER_RECORDSS_ON_01_SEP_2011')

ORA-20136: table names are limited to 25 characters
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

In an Oracle Workspace Manager only 25 characters named tables are allowed to version enbale


 --------------------