(Oracle DB) - 11. Index

/* Index
	#	Database object for accessing to specified data in the table.
	#	Group of Indexes
		-	By the number of columns : single index, composite index
		-	By uniqueness : UNIQUE index, NON-UNIQUE index
		-	By data structure and algorithm : binary tree Index, bitmap index, function-based index
	#	Index can be generated by one or more column(s) in a table.
	#	Binary tree index is the general index,
		it consists of 'Index key' and 'address of the row in the key column of the table'
		'Index key' : The value of the column in the indexing table
*/

/* CREATE INDEX */
CREATE UNIQUE INDEX ex2_10_ix01	-- UNIQUE means col11 is not allowed to get duplicate values
	ON ex2_10 (col11)
;

/* Inspection of Index */
SELECT
	index_name,
	index_type,
	table_name,
	uniqueness
	FROM user_indexes	-- System View
WHERE table_name = 'EX2_10'
;
	-- If we declare constraint UNIQUE or Primary Key, oracle generates UNIQUE Index

SELECT
	constraint_name,
	constraint_type,
	table_name,
	index_name
	FROM user_constraints	-- System View
WHERE table_name = 'JOB_HISTORY'
;	

SELECT
	index_name,
	index_type,
	table_name,
	uniqueness
	FROM user_indexes	-- System View
WHERE table_name = 'JOB_HISTORY'
;


/* Composite Index */
CREATE INDEX ex2_10_ix02	-- NON_UNIQUE
	ON ex2_10
	(col11, col2)
;

SELECT
	index_name,
	index_type,
	table_name,
	uniqueness
	FROM user_indexes	-- System View
WHERE table_name = 'EX2_10';


/* DROP Index */
DROP INDEX 	ex2_10_ix02;