ONLINE COMPUTER EDUCATION

  INTRODUCTION: It is the join strategy where the one table data is loaded into the hash table for an efficient lookup. Other table data is scanned to match with the hash table which is suitable for the large datasets. Database optimizer uses the two of the smaller tables to build the hash table into the memory. Its function is used to map join key values to the hash table locations. Larger table is also scanned for each row. It's fast and efficient for quick matching of rows which is based on the join condition. It is an effective when one or both of the tables are large and cannot fit in the memory. It is also effective when the join condition is an equally predicate i.e. table1.column=table2.column. It's cost is limited to the single read pass which is over the two data sets. It is situated in the PGA and the rows are accessed without latching. It will reduce I/O by preventing the necessity of repeatedly latching and reading blocks in the database buffer cache. If the data s...

 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 ...

 Many to Many Relationships in join operations:  Joining tables or bridging tables are done on the basis of many to many relationships which are used to store records by every possible combination of records of both the tables. In order to handle many to many relationships is quite complex than one to one relationship and one to many relationships. It occurs when the multiple records in the table are related to the multiple records in another table. Students which are enrolling into the multiple courses are example of this type, which are part of the school or college database. Understanding of this concept are useful for designing scalable and flexible databases. In order to fulfill the various application requirements, the complex data interaction is required. This concept is important in the relational database management system. Problems or challenges occurred like database schema becoming too complex or maintaining the performance on the consistent basis. In ecommerce pla...