Explain Nested join in oracle SQL.

-

 


Introduction:

It is also called as the nested loop join. It is called as type of join algorithm which is used to retrieve the data from the two or more tables. For every row of the outer table is matched with the inner table. Inner table is called as the child table where the outer table is called as the parent table. One table is accessed through an index lookup for each row which is retrieved from another table.

A row in the outer table is selected at once time and matched with every row of the outer table.All the rows which are present in the inner table are scanned as many times as the number of rows in the outer table which is done by an optimiser.It provides an analysis of the statistics of the table.If there is no indexes,




Why should we use?

  • When one of the tables is small or indexed and having the few rows.
  • When the indexes are available on the join keys of the inner table and join condition allows for an efficient access to the inner table.
  • The join conditions are highly selective with small subset of data is joined.             
  • OLTP transactional system.
  •  Queries which include the ordering or leading hints.
  •  When the outer table is small.
  •  When an Optimizer is set to first_rows which is used to retrieve the first few rows quickly.


select e.ename,d.dname
  from emp e
  join dept d
  on e.deptno=d.deptno;

Oracle may use the nested loop join like if there is an index on emp.deptno & dept.deptno which is very efficient.Here,emp is the outer table and dept is the inner table.

  create index ui_dept on dept(deptno);
  
    create index ui_emp on emp(deptno);

With the above queries,ui_dept and ui_emp are the index name which are created on the table dept and emp.

EXPLAIN PLAN FOR
select e.ename,d.dname
  from emp e
  join dept d
  on e.deptno=d.deptno;



  select * from table(DBMS_XPLAN.display);



Characteristics in terms of performance:

Advantages:

It is very efficient for the small datasets.

Low overhead occurs for starting the returning rows.

It is considered as good when the driving table or outer table has few rows.

Disadvantages:

It can be inefficient for the larger tables without the proper indexing.

Performance will be degrading if an inner table is accessed many times.

Conclusion:
  • If the outer table is large and the inner table is not indexed efficiently, it can become inefficient.
  • It is efficient when the outer table is small, and the inner table is indexed efficiently.
  • The database starts by reading the first row from the outer table.
  • For each and every row of the outer table, the database checks all the rows in the inner table to satisfy the join condition.
  • If there is match, the corresponding rows from both the tables are combined & the resulting row is added to the result set.
  • This process repeats for each remaining row in the outer table.

Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more