Indexes and Views in Oracle

INDEXES in Oracle

What is Index and Why Should We Use Index?

Indexes are used to access data in the table faster. A book is like a table of contents. When searching for a topic in the book, we can quickly find out the page number of the topic we’re looking for from the table of contents.

If the book doesn’t have a table of contents, we’ll cycle through the pages until we find the topic we’re looking for. The indexes in our database are the same. Stores a row in our table and the ROWID information that corresponds to that row.

When a data is searched in a table, Oracle first checks the presence of the index. If there is no index, it reads all the records in the table until you find the desired record. This is known as FULL TABLE SCAN

When we encounter a low-performing SQL, the first thing we usually check is that the index we’ve used in the WHERE condition of the query is indexed?

By default, Oracle creates B-tree indexes.

Two Types of Indexes:
B Tree (Balanced Tree Index) – Default Index in Oracle
Bitmap

B-Tree Index
The default index type in the Oracle database is B-tree (Balanced Tree) index. – always use for key columns
Example:

CREATE INDEX JOB_INDX ON OT.EMPLOYEES(JOB_ID);
CREATE INDEX supplier_idx ON supplier (supplier_name, city);

Bitmap Index
This index type is the most commonly used index type after B-Tree. This type of index is used when certain values repeat continuously.
Example:

CREATE BITMAP INDEX EXP_1 ON HR.EMPLOYEES(GENDER);

Function Based Index
In this type of index, when you use the function in the Where condition of your queries, it will not use the index unless you have created the index with this function. In this case you need to create Function based index
Example:

Create index MAD_IX on HR.EMPLOYEES(lower(name));

Unique Index
This type of index is the advanced version of the B-Tree index structure. Normally, the B-Tree index structure is Non-Unique.
When we use the Unique keyword to create this index type, Oracle will create a Unique index.
This type of index is especially used on columns with unique records. It is created as follows.
Example:

Create unique index MTC_IX on customer(SSID);

Reverse key Index
This index is an improved type of B-Tree index. In this index type, the value of the index key is always reversed.
Example:
Suppose that our index values are 123, 456, 789, in this case Reversed key index values will be 321, 654, 987.
This index is created as follows.
Example:

Create Index MID_IX on customer(customer_id) reverse;

Compressed Index
The use of index compression in indexes with multiple columns can be very useful, especially if the column contains data repetition.
Less disk space and less I / O operation to achieve compressed index.
Example:

CREATE INDEX INDX_COMP ON HR.EMPLOYEES (FIRST_NAME, LAST_NAME) COMPRESS 2;

Employees may have the same name or surname. We’ve done compression on the concatenated index.

See the indexes in table and drop indexes:
Index If you want to see the Indexes in the table, you can use the below query,

select * from user_indexes where table_name='CUSTOMERS';

Index If you want to drop the Index, you can use the below query,

DROP INDEX Index_name;

VIEWS in Oracle

Introduction to Oracle Views
View is actually a virtual table that is not physically stored in the database and does not store any sort of data.
It is created generally by using join query with two or more tables which enables the view to hide the data complexity.
It will also provide security, since It is not storing any data in the view physically so it actually restricts the access of various columns of a table to the user of the database.
Views are created by a query joining one or more tables.
• Simple views
• Complex views

CREATE VIEW in Oracle
The Syntax to Create the VIEW:

CREATE VIEW view_name AS
SELECT column1, column1 FROM table
WHERE condition;

Example 1:

CREATE VIEW empview AS
SELECT *
FROM employees
WHERE employees.manager_id = 100;

Example 2:

CREATE VIEW employeeview1 AS
SELECT
employee_id,
first_name || ' ' || last_name full_name,
phone_NUMBER
FROM
employees;

We can now check the just created above Oracle VIEW by using this query.
Select * from employeeview;

Example 3:
Creating oracle view with columns alias

CREATE VIEW employeeview1 AS
SELECT employee_id, first_name || ' ' || last_name "full name", FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 )

as years
FROM employees;

Select * from employeeview1;

Example 4:
Access view with where clause and order by

SELECT *
FROM employeeview
WHERE years > 10
ORDER BY full name;

Example 5:
Creating oracle view with multiple tables

CREATE OR REPLACE VIEW dept_manger AS
SELECT
employee_id,first_name || ' ' || last_name as name, department_name
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
on EMPLOYEES. EMPLOYEE_ID = DEPARTMENTS. MANAGER_ID;

Example 6:
Delete view by using the Drop statement
Drop view employee_view;

Advantages of Views in an oracle
• The view can be created on selected data or column of the table, which restrict the view of a table and can hide some of the data or column in the tables.
• The view creates to view the data without storing the data into the view.
• View can be created to Join two or more tables data and store it as one view or object.
• A view is created to achieve security.
• The view gives good performance to run complex queries or join queries.
• View will not occupy memory space

Disadvantages of Views in an oracle
• DML operations cannot perform in view.
• The view becomes inactive if the table is dropped on which it is created.

INLINE VIEW (IMPORTANT INTERVEW QUESTION)
An inline view is not a real view but a subquery in the FROM clause of a SELECT statement. Consider the following SELECT statement:

SELECT
column_list
FROM
(
   SELECT
   *
   FROM
   table_name
) t;

The subquery specified in the FROM clause of a query is called an inline view. Because an inline view can replace a table in a query, it is also called a derived table. It is also called as sub-select.
A) The following query retrieves the top 10 most expensive products from the products table:

SELECT
*
FROM
(
  SELECT
  product_id,
  product_name,
  list_price
  FROM
  products
  ORDER BY
  list_price DESC
)
WHERE
 ROWNUM <= 10;

In this example, first, the inline view returns all products sorted by list prices in descending order. And then the outer query retrieves the first 10 rows from the inline view.
B) Inline view joins with a table example
It returns the product categories and the highest list price of products in each category:

SELECT
category_name,
max_list_price
FROM
product_categories a,
(
  SELECT
  category_id,
  MAX( list_price ) max_list_price
  FROM
  products
  GROUP BY
  category_id
) b
WHERE
  a.category_id = b.category_id
  ORDER BY
    category_name;

Watch the video to practice in realtime