Indexes

Creating Indexes

=================

-- 1. Create a basic B-tree index on a single column

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 composite index on multiple columns

CREATE INDEX idx_emp_dept ON employees(department_id, emp_name);

 

-- 4. Create a bitmap index (useful for low cardinality columns)

CREATE BITMAP INDEX idx_gender ON employees(gender);

 

-- 5. Create a reverse key index (helps distribute inserts)

CREATE INDEX idx_reverse_emp_id ON employees(emp_id) REVERSE;

 

-- 6. Create a function-based index

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

 

-- 7. Create an index on a table partition

CREATE INDEX idx_part ON sales(order_date) LOCAL;

 

-- 8. Create an index with parallelism for faster build

CREATE INDEX idx_parallel_emp_name ON employees(emp_name) PARALLEL 4;

 

-- 9. Create an index with tablespace specification

CREATE INDEX idx_ts_emp_name ON employees(emp_name) TABLESPACE idx_ts;

 

-- 10. Create an index with storage parameters

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

 

-- 11. Create a compressed index to save space

CREATE INDEX idx_comp_emp_name ON employees(emp_name) COMPRESS 2;

 

-- 12. Create a domain index (e.g., Oracle Text)

CREATE INDEX idx_text_doc ON documents(content) INDEXTYPE IS CTXSYS.CONTEXT;

 

-- 13. Create a bitmap join index (in data warehouse)

CREATE BITMAP INDEX idx_bm_join ON sales(customer_id);

 

-- 14. Create a function-based index with expression

CREATE INDEX idx_trunc_order_date ON sales(TRUNC(order_date));

 

-- 15. Create an invisible index (used for testing optimizer impact)

CREATE INDEX idx_invisible ON employees(emp_name) INVISIBLE;

 

-- 16. Create a descending index

CREATE INDEX idx_desc_salary ON employees(salary DESC);

 

-- 17. Create a unique index on concatenation of columns

CREATE UNIQUE INDEX idx_unique_name_dept ON employees(emp_name || department_id);

 

-- 18. Create a global partitioned index

CREATE INDEX idx_global_part ON sales(order_date) GLOBAL PARTITION BY RANGE (order_date) (

  PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),

  PARTITION p2 VALUES LESS THAN (MAXVALUE)

);

 

-- 19. Create a compressed function-based index

CREATE INDEX idx_comp_upper_name ON employees(UPPER(emp_name)) COMPRESS 1;

 

-- 20. Create an index on a LOB column (if supported)

CREATE INDEX idx_lob_content ON documents(content) LOB (content) STORE AS SECUREFILE;

 

Altering and Managing Indexes

=============================

-- 21. Rebuild an index (to defragment and optimize)

ALTER INDEX idx_emp_name REBUILD;

 

-- 22. Rebuild an index in parallel

ALTER INDEX idx_emp_name REBUILD PARALLEL 4;

 

-- 23. Rebuild an index with tablespace change

ALTER INDEX idx_emp_name REBUILD TABLESPACE idx_ts;

 

-- 24. Coalesce an index (merge leaf blocks without rebuilding)

ALTER INDEX idx_emp_name COALESCE;

 

-- 25. Enable parallelism on an existing index

ALTER INDEX idx_emp_name PARALLEL 8;

 

-- 26. Disable parallelism on an index

ALTER INDEX idx_emp_name NOPARALLEL;

 

-- 27. Rename an index

ALTER INDEX idx_emp_name RENAME TO idx_emp_name_new;

 

-- 28. Drop an index

DROP INDEX idx_emp_name;

 

-- 29. Mark an index as unusable

ALTER INDEX idx_emp_name UNUSABLE;

 

-- 30. Make an unusable index usable again by rebuilding

ALTER INDEX idx_emp_name REBUILD;

 

-- 31. Alter index visibility (make visible)

ALTER INDEX idx_invisible VISIBLE;

 

-- 32. Alter index visibility (make invisible)

ALTER INDEX idx_emp_name INVISIBLE;

 

-- 33. Change storage parameters of an index

ALTER INDEX idx_emp_name STORAGE (INITIAL 128K NEXT 128K);

 

-- 34. Change tablespace of an index by rebuild

ALTER INDEX idx_emp_name REBUILD TABLESPACE new_ts;

 

-- 35. Analyze index statistics

ANALYZE INDEX idx_emp_name COMPUTE STATISTICS;

 

-- 36. Gather optimizer stats for index

BEGIN

  DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_NAME');

END;

/

 

-- 37. Move index to a new tablespace

ALTER INDEX idx_emp_name REBUILD TABLESPACE idx_ts_new;

 

-- 38. Set index logging on/off

ALTER INDEX idx_emp_name LOGGING;

 

-- 39. Change index monitoring status to ON

ALTER INDEX idx_emp_name MONITORING USAGE;

 

-- 40. Check index usage statistics

SELECT index_name, leaf_blocks, distinct_keys FROM user_indexes WHERE index_name = 'IDX_EMP_NAME';

 

Index Usage and Info Queries

=============================

-- 41. List all indexes on a table

SELECT index_name, index_type FROM user_indexes WHERE table_name = 'EMPLOYEES';

 

-- 42. Find columns used in an index

SELECT index_name, column_name, column_position FROM user_ind_columns WHERE index_name = 'IDX_EMP_NAME';

 

-- 43. Show index size and stats

SELECT index_name, leaf_blocks, clustering_factor FROM user_indexes WHERE index_name = 'IDX_EMP_NAME';

 

-- 44. Show indexes owned by a user/schema

SELECT index_name, table_name FROM user_indexes WHERE owner = 'HR';

 

-- 45. Check if an index is unique

SELECT index_name, uniqueness FROM user_indexes WHERE index_name = 'IDX_UNIQUE_EMAIL';

 

-- 46. Find indexes with bitmap type

SELECT index_name FROM user_indexes WHERE index_type = 'BITMAP';

 

-- 47. Show index status (VALID, UNUSABLE)

SELECT index_name, status FROM user_indexes WHERE table_name = 'EMPLOYEES';

 

-- 48. Check if an index is visible or invisible

SELECT index_name, visibility FROM user_indexes WHERE index_name = 'IDX_EMP_NAME';

 

-- 49. Find index monitoring status

SELECT index_name, monitoring FROM user_indexes WHERE index_name = 'IDX_EMP_NAME';

 

-- 50. List function-based indexes on a table

SELECT index_name FROM user_indexes WHERE table_name = 'EMPLOYEES' AND FUNCTION_BASED = 'YES';

 

-- 51. Show index columns with data types

SELECT ic.index_name, ic.column_name, c.data_type

FROM user_ind_columns ic

JOIN user_tab_columns c ON ic.table_name = c.table_name AND ic.column_name = c.column_name

WHERE ic.index_name = 'IDX_EMP_NAME';

 

-- 52. Check global vs local partitioned index

SELECT index_name, partitioned, global_stats, local_stats FROM user_indexes WHERE table_name = 'SALES';

 

-- 53. Check compression status of indexes

SELECT index_name, compression FROM user_indexes WHERE table_name = 'EMPLOYEES';

 

-- 54. Find indexes with high clustering factor (less efficient)

SELECT index_name, clustering_factor FROM user_indexes WHERE table_name = 'EMPLOYEES' ORDER BY clustering_factor DESC;

 

-- 55. Check index columns length (for variable length columns)

SELECT index_name, column_name, column_length FROM user_ind_columns WHERE index_name = 'IDX_EMP_NAME';

 

-- 56. Show largest indexes by size

SELECT index_name, (bytes/1024/1024) AS size_mb FROM user_segments WHERE segment_type = 'INDEX' ORDER BY bytes DESC;

 

-- 57. Check index for invalid entries (user_errors)

SELECT * FROM user_errors WHERE name = 'IDX_EMP_NAME';

 

-- 58. List indexes created within last 7 days

SELECT index_name, created FROM user_objects WHERE object_type = 'INDEX' AND created > SYSDATE - 7;

 

-- 59. Show index fragmentation using DBA_EXTENTS

SELECT segment_name, COUNT(*) AS extent_count FROM dba_extents WHERE segment_name = 'IDX_EMP_NAME' GROUP BY segment_name;

 

-- 60. Get index root block address for diagnostics

SELECT index_name, root_block_addr FROM user_indexes WHERE index_name = 'IDX_EMP_NAME';

 

Using Indexes with Queries and Optimization

===========================================

-- 61. Explain plan using an index

EXPLAIN PLAN FOR SELECT * FROM employees WHERE emp_name = 'John';

 

-- 62. Force use of an index in a query

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

 

-- 63. Force full table scan ignoring indexes

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

 

-- 64. Check if an index is used for a query

SELECT * FROM table(dbms_xplan.display_cursor());

 

-- 65. Create a bitmap join index to speed join queries

CREATE BITMAP INDEX idx_bm_join_customer ON sales(customer_id);

 

-- 66. Create an index to optimize ORDER BY query

CREATE INDEX idx_emp_name_sort ON employees(emp_name);

 

-- 67. Use index monitoring to see if index is used

ALTER INDEX idx_emp_name MONITORING USAGE;

 

-- 68. Query to check if monitored index is used

SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';

 

-- 69. Drop monitoring on index after analysis

ALTER INDEX idx_emp_name NOMONITORING USAGE;

 

-- 70. Use an index to speed up LIKE queries (on prefix)

CREATE INDEX idx_emp_name_like ON employees(emp_name);

 

-- 71. Create a function-based index to speed up LOWER() queries

CREATE INDEX idx_lower_email ON employees(LOWER(email));

 

-- 72. Query with function using index

SELECT * FROM employees WHERE LOWER(email) = 'abc@xyz.com';

 

-- 73. Use index to optimize BETWEEN queries

CREATE INDEX idx_order_date ON sales(order_date);

 

-- 74. Partitioned index for partitioned tables

CREATE INDEX idx_partitioned ON sales(order_date) LOCAL;

 

-- 75. Use invisible index to test optimizer impact

CREATE INDEX idx_test_inv ON employees(emp_name) INVISIBLE;

 

-- 76. Drop unused index to save space

DROP INDEX idx_unused;

 

-- 77. Create compressed index for large tables to save space

CREATE INDEX idx_compressed ON big_table(col1, col2) COMPRESS 3;

 

-- 78. Use index for EXISTS subquery optimization

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

 

-- 79. Create index on foreign key column to improve join performance

CREATE INDEX idx_fk_dept_id ON employees(department_id);

 

-- 80. Create index on computed column for performance

ALTER TABLE employees ADD (full_name GENERATED ALWAYS AS (emp_name || ' ' || last_name) VIRTUAL);

CREATE INDEX idx_full_name ON employees(full_name);

 

Advanced Index Usage and Maintenance

====================================

-- 81. Drop index if exists (PL/SQL block)

BEGIN

  EXECUTE IMMEDIATE 'DROP INDEX idx_emp_name';

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE != -942 THEN

      RAISE;

    END IF;

END;

/

 

-- 82. Use DBMS_STATS to gather index stats

BEGIN

  DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_NAME');

END;

/

 

-- 83. Rebuild index online (no table locking)

ALTER INDEX idx_emp_name REBUILD ONLINE;

 

-- 84. Create index with pctfree parameter

CREATE INDEX idx_pctfree ON employees(emp_name) PCTFREE 10;

 

-- 85. Create index with caching option

CREATE INDEX idx_cache ON employees(emp_name) CACHE;

 

-- 86. Create index with NOLOGGING (for faster rebuild)

CREATE INDEX idx_nolog ON employees(emp_name) NOLOGGING;

 

-- 87. Create index with parallel degree 16

CREATE INDEX idx_parallel16 ON employees(emp_name) PARALLEL 16;

 

-- 88. Check if index is global or local partitioned

SELECT index_name, partitioned, partitioning_type FROM user_indexes WHERE index_name = 'IDX_GLOBAL_PART';

 

-- 89. Create an index on XMLType column (using XMLIndex)

CREATE INDEX idx_xml_content ON xml_table(xml_column) INDEXTYPE IS CTXSYS.XMLINDEX;

 

-- 90. Create an invisible unique index (for testing uniqueness)

CREATE UNIQUE INDEX idx_invisible_unique ON employees(email) INVISIBLE;

 

-- 91. Check index rebuild progress (Oracle 12c+)

SELECT operation, sofar, totalwork, units FROM v$session_longops WHERE opname LIKE '%IDX_EMP_NAME%';

 

-- 92. Drop unusable index partitions

ALTER INDEX idx_partitioned DROP PARTITION p1;

 

-- 93. Create a bitmap index with segment creation deferred

CREATE BITMAP INDEX idx_bitmap_def ON employees(status) NOSEGMENT;

 

-- 94. Create a unique index with global partitioning

CREATE UNIQUE INDEX idx_global_unique ON sales(order_id) GLOBAL PARTITION BY HASH (order_id) PARTITIONS 4;

 

-- 95. Create a domain index (e.g., for spatial data)

CREATE INDEX idx_spatial_geom ON locations(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

 

-- 96. Query to check invalid indexes in schema

SELECT index_name FROM user_indexes WHERE status = 'UNUSABLE';

 

-- 97. Query to check indexes with high leaf_blocks

SELECT index_name, leaf_blocks FROM user_indexes WHERE leaf_blocks > 1000;

 

-- 98. Create index on virtual column

ALTER TABLE employees ADD (emp_initials GENERATED ALWAYS AS (SUBSTR(emp_name,1,1) || SUBSTR(last_name,1,1)) VIRTUAL);

CREATE INDEX idx_emp_initials ON employees(emp_initials);

 

-- 99. Use index hint with table alias

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

 

-- 100. Drop all indexes on a table (PL/SQL block)

DECLARE

  CURSOR c IS SELECT index_name FROM user_indexes WHERE table_name = 'EMPLOYEES';

BEGIN

  FOR r IN c LOOP

    EXECUTE IMMEDIATE 'DROP INDEX ' || r.index_name;

  END LOOP;

END;

/