Indexes 100 Queries

1-20: Creating Indexes

-- 1. Create a simple B-tree index

CREATE INDEX idx_emp_name ON employees(emp_name);

 

-- 2. Create a unique index

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

 

-- 3. Create a bitmap index

CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

 

-- 4. Create a function-based index on UPPER function

CREATE INDEX idx_upper_emp_name ON employees(UPPER(emp_name));

 

-- 5. Create a composite index on multiple columns

CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

 

-- 6. Create an index on a partitioned table (local index)

CREATE INDEX idx_partitioned_local ON sales (sale_date) LOCAL;

 

-- 7. Create an index on a partitioned table (global index)

CREATE INDEX idx_partitioned_global ON sales (sale_date) GLOBAL;

 

-- 8. Create an index with reverse key

CREATE INDEX idx_reverse_key ON employees(emp_id) REVERSE;

 

-- 9. Create an index with parallel build

CREATE INDEX idx_parallel_build ON employees(emp_name) PARALLEL 4;

 

-- 10. Create an index with tablespace specification

CREATE INDEX idx_tablespace ON employees(emp_name) TABLESPACE indx_ts;

 

-- 11. Create an index with storage parameters

CREATE INDEX idx_storage ON employees(emp_name) STORAGE (INITIAL 64K NEXT 64K);

 

-- 12. Create a bitmap join index (Oracle specific, in OLAP context)

-- Note: Available in OLAP environments

 

-- 13. Create a domain index (for text or spatial indexes)

-- Example: CREATE INDEX idx_text ON documents(text_column) INDEXTYPE IS CTXSYS.CONTEXT;

 

-- 14. Create an invisible index (not used by optimizer)

CREATE INDEX idx_invisible ON employees(emp_name) INVISIBLE;

 

-- 15. Create an index with no logging (for faster creation)

CREATE INDEX idx_nolog ON employees(emp_name) NOLOGGING;

 

-- 16. Create an index on a column with NULL values (normal index excludes NULLs)

CREATE INDEX idx_nullable_col ON employees(optional_col);

 

-- 17. Create a compressed index

CREATE INDEX idx_compressed ON employees(emp_name) COMPRESS 2;

 

-- 18. Create a bitmap index on a column with low cardinality

CREATE BITMAP INDEX idx_gender ON employees(gender);

 

-- 19. Create a function-based index on concatenation

CREATE INDEX idx_fullname ON employees(UPPER(first_name || ' ' || last_name));

 

-- 20. Create a unique function-based index

CREATE UNIQUE INDEX idx_unique_upper_email ON employees(UPPER(email));

 

21-40: Altering and Managing Indexes

-- 21. Rebuild an index (offline)

ALTER INDEX idx_emp_name REBUILD;

 

-- 22. Rebuild an index online

ALTER INDEX idx_emp_name REBUILD ONLINE;

 

-- 23. Rebuild an index with parallel option

ALTER INDEX idx_emp_name REBUILD PARALLEL 4;

 

-- 24. Coalesce an index (compact space without rebuild)

ALTER INDEX idx_emp_name COALESCE;

 

-- 25. Drop an index

DROP INDEX idx_emp_name;

 

-- 26. Rename an index

ALTER INDEX idx_emp_name RENAME TO idx_employee_name;

 

-- 27. Make an index unusable (disable it)

ALTER INDEX idx_emp_name UNUSABLE;

 

-- 28. Make an unusable index usable again (rebuild)

ALTER INDEX idx_emp_name REBUILD;

 

-- 29. Gather statistics on an index

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'IDX_EMP_NAME');

 

-- 30. Analyze index for statistics (deprecated, replaced by DBMS_STATS)

ANALYZE INDEX idx_emp_name COMPUTE STATISTICS;

 

-- 31. Disable index monitoring

ALTER INDEX idx_emp_name MONITORING USAGE OFF;

 

-- 32. Enable index monitoring usage

ALTER INDEX idx_emp_name MONITORING USAGE;

 

-- 33. Rename index tablespace (move index to new tablespace)

ALTER INDEX idx_emp_name REBUILD TABLESPACE new_tablespace;

 

-- 34. Alter index parallelism degree

ALTER INDEX idx_emp_name PARALLEL 8;

 

-- 35. Change index logging mode (NOLOGGING or LOGGING)

ALTER INDEX idx_emp_name NOLOGGING;

 

-- 36. Enable index logging mode

ALTER INDEX idx_emp_name LOGGING;

 

-- 37. Change index compression level

ALTER INDEX idx_emp_name COMPRESS 1;

 

-- 38. Set index visibility to invisible

ALTER INDEX idx_emp_name INVISIBLE;

 

-- 39. Set index visibility to visible

ALTER INDEX idx_emp_name VISIBLE;

 

-- 40. Move an index to a new tablespace

ALTER INDEX idx_emp_name REBUILD TABLESPACE indx_ts;

 

41-60: Querying Index Metadata

-- 41. List all indexes for a user

SELECT index_name, table_name, uniqueness

FROM user_indexes;

 

-- 42. List all indexes on a specific table

SELECT index_name, uniqueness, status

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

 

-- 43. List columns of a specific index

SELECT index_name, column_name, column_position

FROM user_ind_columns

WHERE index_name = 'IDX_EMP_NAME'

ORDER BY column_position;

 

-- 44. Find size of indexes (in bytes)

SELECT index_name, bytes

FROM user_segments

WHERE segment_type = 'INDEX';

 

-- 45. List indexes that are unusable

SELECT index_name

FROM user_indexes

WHERE status = 'UNUSABLE';

 

-- 46. List all bitmap indexes

SELECT index_name

FROM user_indexes

WHERE index_type = 'BITMAP';

 

-- 47. List function-based indexes

SELECT index_name, index_type

FROM user_indexes

WHERE function_based = 'YES';

 

-- 48. Find indexes with monitoring enabled

SELECT index_name

FROM user_indexes

WHERE monitoring = 'YES';

 

-- 49. List indexes by tablespace

SELECT index_name, tablespace_name

FROM user_indexes

WHERE tablespace_name = 'INDX_TS';

 

-- 50. Show index usage statistics

SELECT name, leaf_blocks, distinct_keys, clustering_factor

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

 

-- 51. List all unique indexes

SELECT index_name

FROM user_indexes

WHERE uniqueness = 'UNIQUE';

 

-- 52. List index stats using DBA views (if you have privileges)

SELECT owner, index_name, table_name, blevel, leaf_blocks

FROM dba_indexes

WHERE owner = USER;

 

-- 53. Find indexes on columns with NULL values

SELECT idx.index_name, col.column_name

FROM user_indexes idx

JOIN user_ind_columns col ON idx.index_name = col.index_name

WHERE col.column_name IS NOT NULL;

 

-- 54. Show all indexes on partitioned tables

SELECT index_name, table_name, partitioned

FROM user_indexes

WHERE partitioned = 'YES';

 

-- 55. List indexes on temporary tables

SELECT index_name, table_name

FROM user_indexes

WHERE temporary = 'Y';

 

-- 56. List index columns with their order (ASC/DESC)

SELECT index_name, column_name, descend

FROM user_ind_columns

WHERE index_name = 'IDX_EMP_NAME';

 

-- 57. List indexes with compression enabled

SELECT index_name, compression

FROM user_indexes

WHERE compression > 0;

 

-- 58. List indexes on tables with many rows

SELECT i.index_name, i.table_name, t.num_rows

FROM user_indexes i

JOIN user_tables t ON i.table_name = t.table_name

WHERE t.num_rows > 100000;

 

-- 59. Find top 10 largest indexes by size

SELECT index_name, bytes/1024/1024 AS size_mb

FROM user_segments

WHERE segment_type = 'INDEX'

ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY;

 

-- 60. List indexes with monitoring enabled and their usage

SELECT index_name, monitoring

FROM user_indexes

WHERE monitoring = 'YES';

 

61-80: Using Indexes in Queries and Optimization

-- 61. Check if a query uses an index (EXPLAIN PLAN)

EXPLAIN PLAN FOR

SELECT * FROM employees WHERE emp_name = 'John';

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

-- 62. Force index usage with hint

SELECT /*+ INDEX(employees idx_emp_name) */ * FROM employees WHERE emp_name = 'John';

 

-- 63. Use index skip scan (optimizer decides)

-- Just create composite index and query with leading column skipped

 

-- 64. Use index for LIKE queries (with prefix)

SELECT * FROM employees WHERE emp_name LIKE 'Jo%';

 

-- 65. Use function-based index for case-insensitive search

SELECT * FROM employees WHERE UPPER(emp_name) = 'JOHN';

 

-- 66. Use bitmap index for AND conditions on low-cardinality columns

SELECT * FROM employees WHERE gender = 'M' AND department_id = 10;

 

-- 67. Use index to speed up JOIN

SELECT e.emp_id, d.dept_name

FROM employees e JOIN departments d ON e.department_id = d.dept_id;

 

-- 68. Use index to speed up ORDER BY

SELECT * FROM employees ORDER BY emp_name;

 

-- 69. Use index for range queries

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

 

-- 70. Use index in EXISTS subquery

SELECT * FROM departments d

WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.dept_id);

 

-- 71. Use index with DISTINCT clause

SELECT DISTINCT emp_name FROM employees;

 

-- 72. Use index to speed up GROUP BY

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

 

-- 73. Use index to speed up DELETE

DELETE FROM employees WHERE emp_name = 'John';

 

-- 74. Use index to speed up UPDATE

UPDATE employees SET salary = salary * 1.1 WHERE emp_name = 'John';

 

-- 75. Use index for NULL checks

SELECT * FROM employees WHERE email IS NULL;

 

-- 76. Avoid full table scans with indexes on predicates

SELECT * FROM employees WHERE emp_name = 'Alice';

 

-- 77. Use index on expression in WHERE clause

SELECT * FROM employees WHERE UPPER(emp_name) = 'ALICE';

 

-- 78. Use index to speed up LIKE '%text%' queries (requires full-text index)

 

-- 79. Use parallel query with indexes

SELECT /*+ parallel(e, 8) */ * FROM employees e WHERE department_id = 10;

 

-- 80. Use index for IN-list searches

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

 

81-100: Index Maintenance, Monitoring & Advanced Topics

-- 81. Monitor index usage stats (after enabling monitoring)

SELECT index_name, leaf_blocks, distinct_keys, clustering_factor

FROM user_indexes

WHERE monitoring = 'YES';

 

-- 82. Find unused indexes (usage count zero)

SELECT index_name

FROM user_indexes

WHERE monitoring = 'YES'

AND leaf_blocks = 0;

 

-- 83. Drop unused indexes to save space

 

-- 84. Gather table and index stats for optimizer

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'EMPLOYEES', cascade => TRUE);

 

-- 85. Enable fast index creation (in-memory or NOLOGGING)

 

-- 86. Create invisible indexes for testing

CREATE INDEX idx_invisible_test ON employees(emp_name) INVISIBLE;

 

-- 87. Make an index visible again

ALTER INDEX idx_invisible_test VISIBLE;

 

-- 88. Check fragmentation by comparing leaf_blocks to num_rows

SELECT index_name, leaf_blocks, num_rows

FROM user_indexes ui

JOIN user_tables ut ON ui.table_name = ut.table_name;

 

-- 89. Identify stale or fragmented indexes for rebuild

 

-- 90. Use partitioned indexes on partitioned tables

 

-- 91. Drop global indexes after partition maintenance

 

-- 92. Rebuild indexes after large data loads

 

-- 93. Use bitmap indexes for data warehousing queries

 

-- 94. Use function-based indexes for complex search conditions

 

-- 95. Use reverse key indexes to reduce contention

 

-- 96. Use compressed indexes to save space on large tables

 

-- 97. Use domain indexes for text search

 

-- 98. Check index locking or blocking issues during rebuild

 

-- 99. Use V$ views to monitor index usage in real-time

 

SELECT * FROM v$object_usage;

 

-- 100. Script to rebuild all unusable indexes in schema

BEGIN

  FOR i IN (SELECT index_name FROM user_indexes WHERE status = 'UNUSABLE') LOOP

    EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' REBUILD';

  END LOOP;

END;

/