Outer Join restriction

create table TAB1
( col1 VARCHAR2(5),col2 VARCHAR2(5), col3 VARCHAR2(5),  col4 VARCHAR2(5),
  col5 VARCHAR2(5)) ;

create table TAB2
(  col1 VARCHAR2(5), col2 VARCHAR2(5), col3 VARCHAR2(5),
  col4 VARCHAR2(5),  col5 VARCHAR2(5)) ;


insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', null, 'c');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('d', 'd', 'd', 'd', 'd');

insert into TAB1 (COL1, COL2, COL3, COL4, COL5)
values ('e', 'e', 'e', 'e', 'e');


insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('a', 'a', 'a', 'a', 'a');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('b', 'b', 'b', 'b', 'b');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');

insert into TAB2 (COL1, COL2, COL3, COL4, COL5)
values ('c', 'c', 'c', 'c', 'c');

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

select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';

select tab1.*,tab2.* from tab1 left join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
and tab1.col3 <> 'c';

select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab2.col3 (+)<> 'c';

select tab1.*,tab2.* from tab1 , tab2
where tab1.col1 = tab2.col1 (+)
and tab1.col2 = tab2.col2 (+)
and tab1.col3 (+)<> 'c';

Conventional & Direct-Path INSERT

•During conventional insert operations, Oracle reuses free space in the table During such operations, Oracle also maintains referential integrity constraints.

Eg : INSERT INTO TABLE T


•During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.


Eg : INSERT /*+ append */ INTO TABLE T

When we insert a rows into a data block, Oracle has the choice of re-using a data block from the freelist or grabbing a "brand new" empty data block, extending the table into the tablespace and raising the high water mark (HWM), usually in chunks of five data blocks

Direct path inserts - These are performed with blocks above the table high water mark
Append - Append tells Oracle to extend the table and grab new, empty data blocks for the insert
Existing free space is not re-used. So, if you direct load insert a bunch of rows, then delete them, and insert them again, the space made by the delete will not be re-used. •Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
To make sure your SQL is using Direct Load Insert, run it through Explain Plan. You should see a line reading LOAD AS SELECT.
•Direct Load Insert cannot occur on: ◦Index Organised Tables ◦Tables with LOB Columns ◦Tables with Object Columns ◦Clustered Tables
Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows ----

Oracle Hints

1) Hint to use with Like clause

If your SQL contain a LIKE clause then you could consider using a hint on the column where an index exists on this column.

Use the /*+index(table index_name) */ hint. The LIKE clause may prevent the use of the index. If the index is physically smaller than the table then it will likely be faster to use the index rather than the table.

2)  APPEND

Append only works with Insert into table1 select * from table2;
It really doesn't make sense for a single-row insert

Oracle Questions

 1) Please find this  link of some Oracle questions to practice yourself  .


2) Questions

ORA-01555 : snapshot too old

ORA-01555 : snapshot too old : rollback segment number 60 with name

 This is because rollback records needed by a reader for consistent read are overwritten by other writers
This error normally occurs if you are running a very big transaction (like activating millions of records in an odds) and the redo log is to small. Try to enlarge the redo log and run your transaction again.

sql> show parameter undo_retention

 Increase Undo Table space to Auto Extended on.

1- increase the setting of UNDO_RETENTION:

 to change: SQL> alter system set undo_retention=(new-value)

2- increase the tablespace UNDO that contain the ROLLBACK segments

alter tablespace UNDO add datafile '(name-of-the-new-datafile)' size (size-value)M;