Difference between Simple and Composite Index in oracle sql plsql interview question and answer.

 "Infosys interview question has been asked."

DIFFERENCE:

SIMPLE INDEX	COMPOSITE INDEX
If the index is created only one field of the table is called as simple index.	If the index is created more than one field of the table is called as composite index.
Only one entry is created in the simple index.	Multiple entries are created in the composite index.
It is also called as single column index.	It is also called as multiple column index.
It is composed of single columns.	It is composed of multiple columns.
Data which is accessed by the single variable.	Data which is accessed by the multiple variables.
Syntax: create index index_name on table_name(column_name);	Syntax: create index index_name on table_name(column_name1,column_name2);
Example: create index si on emp(empno);	Example: create index ci on emp(job,deptno);

QUERIES:

By using the below query, one can check the index created on the table emp.

select * from user_indexes where table_name='EMP';

select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,COMPRESSION,TABLESPACE_NAME,INDEXING

 from user_indexes where table_name='EMP';