Oracle Interview Questions

 

1. SQL query to return employee record with highest salary
2. SQL query to return highest salary from employee table
3. SQL query to return second highest salary from employee table
4. SQL query to return range of employees based on ID
5. SQL query to return employee name, highest salary and department name
6. SQL query to return highest salary, employee name, department name for each department
7. Difference between TRUNCATE and DELETE
8. Difference between Procedure and Function
9. NVL, NVL2, NULLIF, COALESCE
10. Difference between RANK and DENSE RANK
11. Types of SQL commands
12. How to get unique records without using DISTINCT keyword
13. What is DUAL table
14. What is Trigger and types of triggers
15. Difference between DECODE and CASE
16. Difference between TRANSLATE and REPLACE
17. Views, Simple View and Complex View
18. Difference between View and Materialized View
19. Pseudo columns, Aggregate or Group functions, Analytic functions
20. Features of Oracle 12.2
20.1 Object name greater than 30 characters
20.2 Utilizing LISTAGG function for string greater than 4000 characters
20.3 Session sequence
20.4 DDL Logging
20.5 JSON functions
21. SQL query to return nth highest record or <nth records
22. Index
23. EBS Cycles

 


1. SQL query to return employee record with highest salary

SELECT *
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);

2. SQL query to return highest salary from employee table

SELECT MAX(sal) FROM emp;

3. SQL query to return second highest salary from employee table

SELECT MAX(sal)
FROM emp
WHERE sal NOT IN (SELECT MAX(sal) FROM emp);

4. SQL query to return range of employees based on ID

SELECT * FROM emp WHERE emp_id BETWEEN 1000 AND 2000;

5. SQL query to return employee name, highest salary and department name

SELECT e.emp_name
 ,e.sal
 ,d.dept_name
FROM emp e
 ,dept d
WHERE e.deptno = d.deptno
 AND e.sal IN (SELECT MAX(sal) FROM emp);

6. SQL query to return highest salary, employee name, department name for each department

SELECT e.emp_name
 ,e.sal
 ,d.dept_name
FROM emp e
 ,dept d
WHERE e.deptno = d.deptno
 AND e.sal IN ( SELECT MAX(sal)
 FROM emp
GROUP BY deptno);

7. Difference between TRUNCATE and DELETE

TRUNCATE DELETE
DDL command (CREATE, ALTER, DROP, TRUNCATE) DML command (INSERT, UPDATE, DELETE)
All records will be deleted. No WHERE clause Selective records can be deleted using WHERE clause
Resets the high level watermark in DB Does not resets the high level watermark in DB
Implicitly (indirectly/ unintentionally) executes auto COMMIT and cannot be rollbacked This required explicit COMMIT and we can ROLLBACK the records before COMMIT
Releases all locks associated to that session, clearing the SAVEPOINT’s The corresponding records are locked to other users and the deleted records are placed into the ROLLBACK segment area
DB triggers will not fire when TRUNCATE statement is executed

CREATE TABLE emp_sample(empno NUMBER PRIMARY KEY, ename VARCHAR2(100));

INSERT INTO emp_sample SELECT level, 'EMP_NAME' || level FROM DUAL CONNECT BY level <= 100;

COMMIT;

CREATE OR REPLACE TRIGGER trig_emp_sample AFTER
INSERT OR UPDATE OR DELETE ON emp_sample
FOR EACH ROW
BEGIN
raise_application_error(-20001, 'Trigger is fired..!!');
END;

DELETE FROM emp_sample; --Here we see error since trigger is fired

TRUNCATE TABLE emp_sample; --No error
DB triggers will fire when DELETE statement is executed
 Faster Slower because it uses undo segment

 Retrieve the space used by table

SELECT bytes FROM dba_segments WHERE segment_name = ‘EMP_SAMPLE’;

 Will not retrieve the space used by table
 When trying to delete parent table records even the child table records will be deleted by using ‘TRUNCATE TABLE <tab_name> CASCADE’ from Oracle 12C. The table should be created using ‘ON DELETE CASCADE’ key word When trying to delete parent table records even the child table records will be deleted by using ‘ON DELETE CASCADE’ from Oracle 12C. The table should be created using ‘ON DELETE CASCADE’ key word

8. Difference between Procedure and Function

Procedure Function
Procedure may return or may not return a value Function has to return a single value and should have RETURN clause in its definition.
It may return one or more values using OUT & INOUT parameters
Procedure can be called only in PL/SQL block Function can be called in SELECT statements
Procedure can have IN, OUT, INOUT parameters Function can have IN, OUT and INOUT parameters. However in real time we use only IN parameter.
Just in case we have OUT and INOUT parameter then that function cannot be executed using SELECT statement.1. The below code executes and it has only IN parameter

CREATE OR REPLACE FUNCTION function_name (p_in_num IN  NUMBER) RETURN NUMBER AS
BEGIN
RETURN p_in_num * p_in_num;
END;
SELECT function_name(4) FROM DUAL; --16

2. Function with IN & OUT parameter

CREATE OR REPLACE FUNCTION function_name (p_in_num IN NUMBER, p_out_num OUT NUMBER)
RETURN NUMBER AS
BEGIN
p_out_num := p_in_num * p_in_num * p_in_num;
RETURN p_in_num * p_in_num;
END;

--Below will not execute due to existence of SELECT statement
DECLARE
lv_temp NUMBER;
BEGIN
SELECT function_name(2, lv_temp) INTO lv_temp FROM DUAL;
END;

--Below will execute due to non-existence of SELECT statement
DECLARE
lv_square NUMBER;
lv_cube NUMBER;
BEGIN
lv_square := function_name(2, lv_cube);
dbms_output.put_line('lv_square := ' || lv_square); --4
dbms_output.put_line('lv_cube := ' || lv_cube); --8
END;
RETURN keyword exits the procedure RETURN keyword returns the value.
Function can be compiled without RETURN keyword but SELECT query will not be executed.
IN – Read only;
OUT – Write only;
IN OUT – Read/ write;
DDL and DML statements can be used directly

DDL and DML statements can be used using PRAGMA AUTONOMOUS_TRANSACTION

1. The below code executes

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
BEGIN
RETURN 1;
END;
SELECT function_name FROM DUAL;

2. The below DML code errors

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
BEGIN
UPDATE emp SET sal = sal;
COMMIT;
RETURN 1;
END;
SELECT function_name FROM DUAL;

3. The below DML code executes

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE emp SET sal = sal;
COMMIT;
RETURN 1;
END;
SELECT function_name FROM DUAL;

4. The below DDL code executes

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test_table (id NUMBER)';
EXECUTE IMMEDIATE 'DROP TABLE test_table';
RETURN 1;
END;
SELECT function_name FROM DUAL;

5. The below DML code executes when executing in PL/SQL block as an expression

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
BEGIN
UPDATE emp SET sal = sal;
COMMIT;
RETURN 1;
END;

DECLARE 
lv_var NUMBER;
BEGIN
lv_var := function_name;
COMMIT;
END;

6. The below DML code errors when executing in PL/SQL block in SELECT statement

CREATE OR REPLACE FUNCTION function_name RETURN NUMBER AS
BEGIN
UPDATE emp SET sal = sal;
COMMIT;
RETURN 1;
END;

DECLARE 
lv_var NUMBER;
BEGIN
SELECT function_name INTO lv_var FROM DUAL;
COMMIT;
END;

9. NVL, NVL2, NULLIF, COALESCE

SELECT NVL('A', 'B') result FROM DUAL; -- If first value ('A') is NULL then returns second value ('B') else returns first value ('A')
SELECT NVL(NULL, 'B') result FROM DUAL; -- Here first value is NULL so returns second value
SELECT NVL('A', NULL) result FROM DUAL; -- Here first value is NOT NULL so returns first value
SELECT NVL(NULL, NULL) result FROM DUAL; -- Here both are NULL so returns NULL value
SELECT NVL2('A', 'B', 'C') result FROM DUAL; --If first value ('A') is NOT NULL then returns second value ('B') else returns third value ('C')
SELECT NVL2(NULL, 'B', 'C') result FROM DUAL; --Here first value is NULL so returns third value ('C')
SELECT NVL2('A', NULL, 'C') result FROM DUAL; --Here first value is NOT NULL so returns second value (NULL)
SELECT NULLIF('A', 'A') result FROM DUAL; -- If both are same then returns NULL
SELECT NULLIF('B', 'C') result FROM DUAL; -- If both are not same then returns first value ('B')
SELECT NULLIF(NULL, 'C') result FROM DUAL; -- If first value is NULL then we get error
SELECT NULLIF('A', NULL) result FROM DUAL; -- Here first value is NOT NULL so we get first value ('A')
SELECT COALESCE('A', 'B', 'C', 'D', 'E') result FROM DUAL; --Can have n number of values and returns first NOT NULL value ('A')
SELECT COALESCE('A', 'B', NULL, 'D', 'E') result FROM DUAL; --Can have n number of values and returns first NOT NULL value ('A')
SELECT COALESCE(NULL, NULL, 'C', 'D') result FROM DUAL; --Can have n number of values and returns first NOT NULL value ('C')

10. Difference between RANK and DENSE RANK

SELECT salary
 ,RANK() OVER(ORDER BY sal DESC) rank
 ,DENSE_RANK() OVER(ORDER BY sal DESC) dense_rank
FROM emp;
SALARY RANK DENSE_RANK
1000 1 1
2000 2 2
2000 2 2
3000 4 3
4000 5 4
5000 6 5
5000 6 5
5000 6 5
6000 9 6

11. Types of SQL commands

DDL DML DCL TCL DRL
CREATE INSERT GRANT COMMIT SELECT
ALTER UPDATE REVOKE ROLLBACK
DROP DELETE SAVEPOINT
TRUNCATE MERGE
RENAME

Session control statement : ALTER SESSION
System control statement : ALTER SYSTEM


12. How to get unique records without using DISTINCT keyword
We have nine ways to achieve this:
i) Using GROUP BY clause

SELECT a, b, c FROM table_name GROUP BY a, b, c;

ii) Using set operator – UNION (Impacts performance)

SELECT a, b, c FROM table_name
UNION
SELECT a, b, c FROM table_name;

iii) Using set operator – UNION

SELECT a, b, c FROM table_name
UNION
SELECT NULL, NULL, NULL FROM dual WHERE 1 = 2;

iv)Using set operator – INTERSECT

SELECT a, b, c FROM table_name
INTERSECT
SELECT a, b, c FROM table_name;

v) Using set operator – MINUS

SELECT a, b, c FROM table_name
MINUS
SELECT NULL, NULL, NULL FROM dual;

vi) Using analytic function – ROW_NUMBER

SELECT a, b, c FROM (SELECT a, b, c, ROW_NUMBER() over(partition by a, b, c ORDER BY a, b, c) R FROM table_name)
 WHERE R = 1;

vii) Using analytic function – RANK

SELECT a, b, c FROM (SELECT a, b, c, RANK() over(partition by a, b, c ORDER BY ROWNUM) R FROM table_name)
WHERE R = 1;

viii) Using ROWID

SELECT * FROM table_name
WHERE rowid IN (
SELECT MIN(rowid)
FROM table_name
GROUP BY a, b, c)
ORDER BY a, b, c;

ix) Using co-related sub query

SELECT * FROM table_name a
WHERE 1 = (SELECT COUNT(1)
FROM table_name b
WHERE a.column_name1 = b.column_name1
AND a.column_name2 = b.column_name2
AND a.rowdid >= b.rowid);

13. What is DUAL table – Dummy table automatically created by Oracle DB along with the data dictionary. It has only one column (dummy) with data type as VARCHAR2(1) and the value is ‘X’.
We can use this table for computational purposes like SELECT 1 + 2 FROM DUAL; SELECT 100*20 FROM DUAL; SELECT SYSDATE FROM DUAL; SELECT USER FROM DUAL;
We cannot use DECODE expression directly in PL/SQL block. We have to use in SELECT DECODE(a, b, c) INTO lv_col FROM DUAL;


14. What is Trigger and types of triggers
A trigger is a PL/SQL block which is automatically fired when an event occurs in the DB

DML Trigger DDL Trigger SYSTEM Trigger Instead of Trigger Compound Trigger
INSERT CREATE LOGON TRIGGER on Views Combines DML Trigger in single block
UPDATE ALTER LOGOFF  Written on top of Views  Before/ after statement + before/ after row level
DELETE DROP STARTUP
TRUNCATE SHUTDOWN
RENAME
GRANT
REVOKE
AUDIT

DML Trigger is classified into two types.

Statement Level Trigger Row Level Trigger
Trigger executes only once for all DML statements Trigger executes for each and every row
CREATE OR REPLACE TRIGGER trigger_name BEFORE/ AFTER
 INSERT OR UPDATE OR DELETE ON emp 
BEGIN 
dbms_output.put_line('Statement level trigger');
END;
CREATE OR REPLACE TRIGGER trigger_name BEFORE/ AFTER
 INSERT OR UPDATE OR DELETE ON emp 
 FOR EACH ROW
BEGIN 
dbms_output.put_line('Row level trigger');
END;
Its 2 * 3 (2=BEFORE/ AFTER and 3 DML statements) ways we can execute Its 2 * 3 (2=BEFORE/ AFTER and 3 DML statements) ways we can execute

Execution order:
1. Before Statement level
2. Before Row level
3. After Row level
4. After Statement level


15. Difference between DECODE and CASE
Both serves the IF..ELSE functionality but CASE has more advanced features than DECODE

DECODE CASE
SELECT s_no
 ,s_name
 ,s_mark
 ,s_result
 ,DECODE(s_result,'P','Pass','F','Fail','No Result') result
FROM student_tab;
SELECT s_no
 ,s_name
 ,s_mark
 ,s_result
 ,CASE s_result
 WHEN 'P' THEN 'Pass'
 WHEN 'F' THEN 'Fail'
 ELSE 'No Result'
 END
result
FROM student_tab;
SELECT s_no
 ,s_name
 ,s_mark
 ,s_result
 ,CASE 
 WHEN s_result = 'P' THEN 'Pass'
 WHEN s_result = 'F' THEN 'Fail'
 ELSE 'No Result'
 END
result
FROM student_tab;
SELECT s_no
 ,s_name
 ,s_mark
 ,s_result
 ,CASE 
 WHEN s_result = 'P' AND s_mark >= 60 THEN 'Pass - First Class'
 WHEN s_result = 'P' AND s_mark < 60 THEN 'Pass - Second Class'
 WHEN s_result = 'F' THEN 'Fail'
 ELSE 'No Result'
 END
result
FROM student_tab;
SELECT DECODE(:input,'1','One','2','Two','3','Three','None') result
FROM dual;
SELECT
 CASE :input
 WHEN '1' THEN 'One'
 WHEN '2' THEN 'Two'
 WHEN '3' THEN 'Three'
 ELSE 'NONE'
 END
result
FROM dual;
SELECT DECODE(:input,'1','One',2,'Two',3,'Three','None') result
FROM dual;
The below will not work due to datatype inconsistency

SELECT
 CASE :input
 WHEN '1' THEN 'One'
 WHEN 2 THEN 'Two'
 WHEN '3' THEN 'Three'
 ELSE 'NONE'
 END
result
FROM dual;
DECLARE
 lv_result VARCHAR2(20);
BEGIN
 lv_result := upper('Hello World');
 dbms_output.put_line(lv_result);
END;
The below will not work as DECODE cannot be used as assignment varible

DECLARE
 lv_result VARCHAR2(20);
 lv_input VARCHAR2(1) := 'P';
BEGIN
 lv_result := DECODE(lv_input,'P','Pass','F','Fail','No Result');
END;
DECLARE
 lv_result VARCHAR2(20);
 lv_input VARCHAR2(1) := 'P';
BEGIN
 lv_result :=
 CASE lv_input
 WHEN 'P' THEN 'Pass'
 WHEN 'F' THEN 'Fail'
 ELSE 'No Result'
 END;
END;
DECLARE
 lv_result VARCHAR2(20);
 lv_input VARCHAR2(1) := 'P';
BEGIN
 SELECT DECODE(lv_input,'P','Pass','F','Fail','No Result') INTO
 lv_result
 FROM dual;
END;
DECLARE
 lv_result VARCHAR2(20);
 lv_input VARCHAR2(1) := 'P';
BEGIN
 CASE
 lv_input
 WHEN 'P' THEN
 lv_result := 'Pass';
 WHEN 'F' THEN
 lv_result := 'Fail';
 ELSE
 lv_result := 'No Result';
 END CASE;
END;

16. Difference between TRANSLATE and REPLACE

TRANSLATE REPLACE
Replace character by character in a string Replace sequence of characters in a string
Also used to remove ascii characters. When user gives space bar or press enter button in data field then ascii characters like CHR(10) or CHR(13) inserts into DB. To remove those acsii character we use REPLACE.

SELECT REPLACE (hcp.email_address, CHR (13), NULL) FROM hz_contact_points hcp
SELECT TRANSLATE ('123SRI', 'S3', '4T') FROM DUAL;
12T4RI
SELECT REPLACE ('123SRI', 'S3', '4T') FROM DUAL;
123SRI
SELECT TRANSLATE ('123SRI', '3S', '4T') FROM DUAL;
124TRI
SELECT REPLACE ('123SRI', '3S', '4T') FROM DUAL;
124TRI
SELECT TRANSLATE ('123SRI', 'S2', '4') FROM DUAL;
134RI -- Here S replaced by 4 and 2 will be deleted
SELECT REPLACE ('123SRI0', '3S', '.') FROM DUAL;
12.RI0
SELECT TRANSLATE ('123SRI0', '.3S', '.') FROM DUAL;
12RI0 -- Characters wont display after .
SELECT REPLACE ('Hello World', 'Hello', 'Bye') FROM DUAL;
Bye World
SELECT TRANSLATE ('ABCDE01230450', '.123456789', '.') FROM DUAL;
ABCDE000
SELECT TRANSLATE ('ABCDE01230450', '0'||'ABCDE', '0') FROM DUAL;
01230450

17. Views, Simple View and Complex View
View – Is a logical table created on top of one or more base tables or views. View does not have data of this own and it fetches data from base tables which it was defined. Syntax: CREATE OR REPLACE VIEW view_name AS SELECT statement;

CREATE OR REPLACE FORCE VIEW view_10 AS SELECT * FROM emp WHERE deptno = 10;

FORCE is used to create a view even though base tables do not exist. In case we are creating view first and then base table then we have to use FORCE keyword.

SELECT * FROM view_10;
CREATE OR REPLACE VIEW emp_sal_view 
AS SELECT deptno, max(sal) max_sal, avg(sal) avg_sal, min(sal) min_sal FROM emp GROUP BY deptno;

Query to get views in DB

SELECT * FROM user_views;

Query to get column details of View

SELECT * FROM user_tab_columns WHERE table_name = 'EMP_SAL_VIEW';
Simple View Complex View
Based on single table Based on any number of tables
No aggregate / group functions (No GROUP BY clause) Can use aggregate/group functions
No DISTINCT keyword Can use DISTINCT keyword
No Pseudo columns. No Analytic functions. Can use Pseduo columns. Can use analytic functions.
DML’s are allowed No DML’s are allowed

18. Difference between View and Materialized View

View Materialized View
Logical table which does not have data. Stores the data of the result set (query) physically.
SELECT * FROM view — Fetches data from base table SELECT * FROM materialized_view — Fetches data from physical location
Any change on base table data reflects immediately on view. REFRESH command helps to update MV data.
CREATE OR REPLACE VIEW emp_10 AS
 SELECT * FROM emp WHERE deptno = 10;

SELECT * FROM emp_10;

DELETE FROM emp;

SELECT * FROM emp_10; -- No records found
CREATE MATERIALIZED VIEW emp_10_mv AS
 SELECT * FROM emp WHERE deptno = 10; 

SELECT * FROM emp_10_mv;

DELETE FROM emp;

SELECT * FROM emp_10_mv; -- We still get result since data is fecthing from physical location

EXEC dbms_mview.refresh('EMP_10_MV');

SELECT * FROM emp_10_mv; -- No records since data is refreshed

19. Pseudo columns, Aggregate or Group functions, Analytic functions

Pseudo columns Aggregate or Group functions Analytic functions
Pseudo columns behave like a normal table columns but the values are actually not stored in table. SUM RANK

1
1
3
Only SELECT operation can performed but no DML operations. COUNT DENSE_RANK

1
1
2
CURRVAL MAX ROW_NUMBER
NEXTVAL MIN LEAD
LEVEL AVG LAG
ROWID FIRST
ROWNUM LAST
FIRST_VALUE
LAST_VALUE
LISTAGG
NTH_VALUE

20. Features of Oracle 12.2
i) Object name greater than 30 characters
ii) Utilizing LISTAGG function for string greater than 4000 characters
iii) Session sequence. This is 12.1 feature
iv) DDL Logging. This is 12.1 feature
v) JSON functions


20.1 Object name greater than 30 characters

• This is achievable from database 12.2 version on wards. To know your current version use SELECT * FROM v$version;
• The compatible initialization parameter must be set to 12.2.0 or higher. SELECT value FROM v$parameter WHERE name = ‘compatible’;
• To know the object length in your database describe ‘user_tables’ and see the datatype of column ‘TABLE_NAME’


20.2 Utilizing LISTAGG function for string greater than 4000 characters
From 12.2 if the string length is > 4000 then we have two options to return output.
i) Throw an error : Same error which we get when the length exceeds 4000 characters

SELECT LISTAGG(table_name
,'||' ON OVERFLOW ERROR) WITHIN GROUP(
ORDER BY table_name) table_names
FROM all_tables;

ii) Truncate the data

SELECT LISTAGG(table_name
,'||' ON OVERFLOW TRUNCATE) WITHIN GROUP(
ORDER BY table_name) table_names
FROM all_tables;
--The last ... denotes still more data exists and (number) denotes the number of truncated fields.

--The ... and number can be replaced by using below query
SELECT LISTAGG(table_name
,'||' ON OVERFLOW TRUNCATE '**Some more data exists**' WITHOUT count) WITHIN GROUP(
ORDER BY table_name) table_names
FROM all_tables;

--No custom message and no number
SELECT LISTAGG(table_name
,'||' ON OVERFLOW TRUNCATE '' WITHOUT count) WITHIN GROUP(
ORDER BY table_name) table_names
FROM all_tables;

20.3 Session sequence
In a particular session we get reset sequences. This is 12.1 feature.
Ex: Session 1 — Sequence 1, 2, 3…; Session 2 — Sequence 1, 2, 3…; Session 3 — Sequence 1, 2, 3…

CREATE SEQUENCE sequence_name SESSION;

The default sequence what we use is GLOBAL sequence and no need to explicitly define while creating sequence. CREATE SEQUENCE sequence_name; = CREATE SEQUENCE sequence_name GLOBAL;


20.4 DDL Logging

From 12.1, we can capture the log files for DDL commands during deployment.

ALTER SYSTEM SET ENABLE_DDL_LOGGING = TRUE;
ALTER SESSION SET ENABLE_DDL_LOGGING = TRUE;

DDL log file location:
<oracle_home_directory>\diag\rdbms\orcl12c\orcl12c\log\ddl

Log files:
1. Text file
2. XML file


20.5 JSON functions

JSON = Java Script Object Notation

JSON document:
{“ENAME”:”SCOTT”}
{“ENAME”:”KING”, “ENAME”:”BLAKE”, “ENAME”:”CLARK”}
{“Emp List”:[“KING”, “BLAKE”, “CLARK”]}


21. SQL query to return nth highest record or <nth records

Replace < 5 with = 5 to get 5th highest record

1. SELECT * FROM (SELECT * FROM emp ORDER BY SAL DESC) WHERE ROWNUM < 5;

2. SELECT e.* FROM emp e, (SELECT empno, ROW_NUMBER () OVER (ORDER BY sal DESC) row_num FROM emp) b WHERE b.row_num < 5 AND b.empno = e.empno;

3. WITH b AS (SELECT empno, ROW_NUMBER () OVER (ORDER BY sal DESC) row_num FROM emp)
SELECT e.* FROM emp e, b WHERE b.row_num < 5 AND b.empno = e.empno;

4. SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 4 ROWS ONLY;

22. Index
An index is a database object and is used to speed up the search operation in the database. For example a standard book contains an Index/Contents and if we want to go to particular page then with the help of index we go to that page directly avoiding full book search. Similarly an index in DB contains a pointer which points to the row containing the value avoiding full table scan. Also index sorts the data in ascending order and helps faster retrieval of data. Indexes are used for performance improvement.

SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';
SELECT * FROM user_ind_statistics WHERE table_name = 'EMP';

Types

B-Tree Bitmap Function Based Reverse Key Composite
Create on column which has more number of unique/ distinct values Create on column which has less number of unique/ distinct values UPPER or LOWER functions The key index values will be reversed Multiple columns
Ex: EMPNO Ex: DEPTNO or Gender(Male/ Female) or Status (Active/ Inactive) Avoid index block contention.
B means balanced. Sorts the values and divides into gropus. Say we have 14 empno. 1 to 7 will be one group and 8 to 14 will be in one group. Again 1 to 7 or 8 to 14 will spilt into group like 1 to 3 and 4 to 7. This avoid full table scan.
The indexed column along with rowid column will be stored in another data set for faster retrieval of data.
CREATE INDEX idx_name ON tbl_name(col_name); CREATE BITMAP INDEX idx_name ON tbl_name(col_name); CREATE INDEX idx_name ON tbl_name(UPPER(col_name)); CREATE INDEX idx_name ON tbl_name(col_name) REVERSE; CREATE INDEX idx_name ON tbl_name(col_name1, col_name2);
In user_indexes table the index_type = NORMAL
Hampers DML operations.

Check if index is used by query:
The first example has index unique scan due to empno in WHERE clause whereas in second example we have full table scan since no index on deptno column.

Example 1:
EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno=7839;
SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("EMPNO"=7839)

Example 2:
EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno=10;
SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 3 | 114 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("DEPTNO"=10)

Also we can verify using tables v$sql and v$sql_plan;

Scan Types/ Operations
i) Range scan
ii) Unique scan
iii) Full scan
iv) Full scan (Max/ Min)
v) Fast full scan


23. EBS Cycles

Process Tables Types Concurrent Programs
Item mtl_system_items_b msib
mtl_system_items_tl msit
Supplier ap_suppliers as
ap_supplier_sites_all assa
ap_supplier_contacts asc
Buyer per_all_people_f papf
per_all_assignments_f paaf
Requisition po_requisition_headers_all prha
po_requisition_lines_all prla
po_req_distributions_all prda
i) Standard – Within organisation and no approval required.
ii) Purchase – External organisation and approval required.
Request for Quotation po_headers_all pha
WHERE pha.type_lookup_code = ‘RFQ’
i) Standard – One time purchasing
ii) Bid – Expensive/ Large number of items
iii) Catalog – Regular purchasing
Quotation po_headers_all pha
WHERE pha.type_lookup_code = ‘QUOTATION’
Purchase Order po_headers_all pha
po_lines_all pla
po_distributions_all pda
po_line_locations_all plla
i) Standard – One time purchasing
ii) Blanket – Agreement
iii) Planned – Long term agreement
iv) Contract – Terms & conditions
Goods Receipt Note rcv_shipment_headers rsh
rcv_shipment_lines rsl
rcv_transactions rt
AP Invoice ap_invoices_all aia
ap_invoice_lines_all aila
ap_invoice_distributions_all aida
Pay on Receipt AutoInvoice
Payments of Invoice ap_checks_all aca
ap_invoice_payments_all aipa
ap_payment_schedules_all apsa
Journals & Posting gl_je_batches gjb
gl_je_headers gjh
gl_je_lines gjl
gl_balances gb
Customers hz_parties hp
hz_party_sites hps
hz_party_site_uses hpsu
hz_cust_accounts hca
hz_cust_account_sites hcas
hz_cust_account_site_uses hcasu
hz_locations hlhp.party_type IN (‘PERSON’, ‘ORGANIZATION’, ‘GROUP’)
Enter Sales Order oe_order_headers_all ooha
oe_order_lines_all oola
Book Sales Order wsh_delivery_details wdd
wsh_delivery_assignments wda
Launch Pick Release wsh_delivery_details wdd
wsh_delivery_assignments wda
wsh_new_deliveries wnd
i) Pick slip report
ii) Shipping exception report
iii) Auto pack report
Ship Confirm  wsh_delivery_details wdd i) Interface trip stop
ii) Commercial invoice
iii) Packing slip report
iv) Bill of lading
AR Invoice ra_interface_lines_all rail
ra_customer_trx_all rcta
ra_customer_trx_lines_all rctla
i) Workflow background process (OM to AR interface)
ii) AutoInvoice Master (AR interface to AR base)
Receipt Creation ar_receivable_applications_all araa
ar_cash_receipts_all acra
Manual or Auto Lockbox
Journals & Posting gl_je_batches gjb
gl_je_headers gjh
gl_je_lines gjl
gl_balances gb
i) Create Accounting