Database Questions - Part 2
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
Explain the difference between a data block, an extent and a segment ?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
- Pessimistic locking
- Optimistic locking
Difference in Inner join and Outer join?
- To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
- To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
- AVG: returns the variance or variability of an expression.
- COUNT: returns the number of rows returned by a query.
- FIRST: returns the first value from an ordered sequence.
- LAST: returns the last value from an ordered sequence.
- MAX: returns the maximum value of a column.
- MIN: returns the minimum value of a column.
- STDDEV: returns the standard deviation of a set of numbers.
- SUM: returns the sum or total the values of a column.
- VARIANCE: returns the variance or variability of an expression
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
After triggers execute following the triggering action, such as an insert, update, or delete.
- Declare cursor
- Open cursor
- Fetch row from the cursor
- Process fetched row
- Close cursor
- Deallocate cursor.
- A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
- Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.
- Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
- Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.
5. GROUP BY
6. CUBE | ROLLUP
11. ORDER BY
Difference in clustered and unclustered index?
The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines). In a non-clustered index: The physical order of the rows is not the same as the index order. Typically created on non-primary key columns used in JOIN, WHERE, and ORDER BY clauses. There can be more than one non-clustered index on a database table.
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected. Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).
- No indexes
- A clustered index
- A clustered index and many non-clustered indexes
- A non-clustered index
- Many non-clustered indexes
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
- Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
- TRUNCATE is a DDL Command.
- TRUNCATE resets the identity of the table.
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- DELETE does not reset Identity property of the table.
- DELETE can be used with or without a WHERE clause
- DELETE activates Triggers if defined on table.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset the identity of the table.
- Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
- Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
- Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
- Root node: A root node contains node pointers to only one branch node.
- Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more.
- Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.
- Stored procedure can reduced network traffic and latency, boosting application performance.
- Stored procedure execution plans can be reused; they staying cached in SQL Server’s memory, reducing server overhead.
- Stored procedures help promote code reuse.
- Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
- Stored procedures provide better security to your data.
- Use Type-Safe SQL Parameters
- Use Parameterized Input with Stored Procedures
- Use the Parameters Collection with Dynamic SQL
- Filtering Input parameters
- Use the escape character in LIKE clause
- Wrapping Parameters with QUOTENAME() and REPLACE()
ID number not null,
Partition p1 values less than (to_date('01-JAN-2000','DD-MON-YYYY')),