Indexes

Q1. What is the difference between foreign key and reference key?

Answer. A Foreign key is the key which refers to another table’s primary key.
A Reference key is the primary key of table referred by another table.

Q2. Differentiate between Primary key and Unique Key

Answer. Primary key and Unique key both enforces uniqueness of the column on which they are defined.

Primary key	Unique key
Creates clustered index by default	Creates a non-clustered index by default
Des not allow NULL value	Allows NULL value

Q3. Describe B-Tree Indexes

Answer. B-trees, short for balanced trees, are the most common type of database index. A B-tree index is an ordered list of values divided into ranges. By associating a key with a row or range of rows, B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.

Q4. Describe Bitmap Indexes

Answer. In a bitmap index, the database stores a bitmap for each index key. In a B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows. Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an adhoc fashion.

Q5. Describe Function-Based Indexes

Answer. A function-based index computes the value of a function or expression involving one or more columns and stores it in the index. A function-based index can be either a B-tree or a bitmap index. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout.

Q6. Difference between cluster and non-cluster index

Answer. Here’s a summary of the differences:

• A clustered index determines the order in which the rows of the table will be stored on disk – and it stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
• Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speed up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.
• A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.
• A table can have multiple non-clustered indexes. But a table can have only one clustered index.
• Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index stores the row-level data in its leaf nodes.

Q7. What is a foreign key with Cascade DELETE?

Answer. A foreign key constraint created with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will be deleted automatically.

CREATE TABLE suppliers
 (supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk
    PRIMARY KEY (supplier_id));
  
CREATE TABLE products
 (product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY(supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE CASCADE); 
 
DELETE FROM suppliers CASCADE; 

The CASCADE keyword in the following delete example is not necessary, but it’s good to use indicate other developers that you are expecting a recursive delete.

Truncate table supplier;

The above command will result into following error message.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

To resolve this error use following command instead.

Truncate table supplier cascade;

The TRUNCATE TABLE … CASCADE command succeeds and recursively truncates all the dependent tables.

Note: Both the DELETE CASCADE & the TRUNCATE CASCADE fails, if any of the relationships in the hierarchy are not defined with the ON DELETE CASCADE clause

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.