BULK COLLECT Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. It can be used with all three types of collections: associative arrays, nested...

Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...

Bulk Binds Bulk binding decreases context switch between PL/SQL engine and SQL Engine that makes code execution faster. Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses...

Collection Methods A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain. You invoke...

What is a sub query? What are its various types? - Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables. - A sub query is added in the where clause of the main query. There can be two types of subqueries: a.) Correlated...

A trigger is an database object which executes automatically in response to a an on a particular table in a database. the event can be insert , update or delete. Triggers are stored programs, which are automatically executed or fired when some events...

second highest salary in each department SELECT * FROM (SELECT emp.*, DENSE_RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) secondhsal FROM emp )x WHERE x.second hsal=2 ;

Deleting the duplicate records The usual need is to delete all but one such row. The first thing is to know the deciding factor -out of the duplicates, which one is to be retained? The next SQL retains the row with the minimum rowid: SQL> delete from...

Log shipping: Shipping the transnational log backups from one server (Primary) to another server (Secondary) SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or...

clustered index --------------- datapages store into leaf level nodes only one clustered index for table (data arrange physically only one way) good performance not allow duplicates If we create primary key on table on any key column then automatically...