Indexing Deep Dive

-- Full clustering factor health check for all indexes on a table
SELECT i.index_name,
       i.num_rows,
       i.clustering_factor,
       t.blocks                                                   AS table_blocks,
       ROUND(i.clustering_factor / NULLIF(i.num_rows,  0), 2)     AS cf_vs_rows,
       ROUND(i.clustering_factor / NULLIF(t.blocks, 0), 2)      AS cf_vs_blocks,
       CASE
         WHEN i.clustering_factor <= t.blocks * 2      THEN 'EXCELLENT'
         WHEN i.clustering_factor <= t.blocks * 10     THEN 'GOOD'
         WHEN i.clustering_factor <= i.num_rows * 0.5  THEN 'POOR'
         ELSE 'TERRIBLE — index likely ignored for range scans'
       END AS verdict
FROM   dba_indexes i
JOIN   dba_tables  t ON t.table_name = i.table_name
                    AND t.owner      = i.owner
WHERE  i.table_name = 'ORDERS'
AND    i.owner      = 'APPS'
ORDER BY i.clustering_factor;

-- Query running 50,000 times per hour:
-- SELECT order_date, total_amount FROM orders WHERE customer_id=:b1 AND status='OPEN'

-- Without covering: index → ROWID → table (2 I/Os per row)
-- With covering:    index only, no table visit (1 I/O per row)

CREATE INDEX idx_ord_covering
  ON orders(customer_id, status, order_date, total_amount);
--           ^^equality   ^^equality ^needed        ^needed
--           these two drive         these two satisfy the SELECT list
--           the scan                without visiting the table

-- Verify Oracle uses index-only access — look for this in the plan:
-- INDEX RANGE SCAN with NO subsequent TABLE ACCESS BY ROWID

-- Check which columns are in the index
SELECT column_name, column_position
FROM   dba_ind_columns
WHERE  index_name  = 'IDX_ORD_COVERING'
AND    table_owner = 'APPS'
ORDER BY column_position;

-- Enable monitoring on suspect indexes
ALTER INDEX idx_ord_status MONITORING USAGE;

-- After a week, check if it was used
SELECT index_name, table_name, monitoring, used, start_monitoring
FROM   v$object_usage
WHERE  table_name = 'ORDERS';

-- 12c+: Automatic tracking via V$INDEX_USAGE_INFO
SELECT name, owner, total_access_count,
       total_exec_count, last_used
FROM   v$index_usage_info
WHERE  owner = 'APPS'
ORDER BY total_access_count ASC;  -- 0 = strong drop candidate

-- Find indexes whose write cost exceeds read benefit
SELECT i.index_name, i.num_rows,
       s_r.value  AS index_reads,
       s_w.value  AS index_writes,
       ROUND(s_r.value * 100 /
         NULLIF(s_w.value + s_r.value, 0), 1) AS read_pct
FROM   dba_indexes          i,
       v$segment_statistics  s_r,
       v$segment_statistics  s_w
WHERE  s_r.object_name    = i.index_name
AND    s_w.object_name    = i.index_name
AND    s_r.statistic_name = 'logical reads'
AND    s_w.statistic_name = 'db block changes'
AND    i.owner = 'APPS'
ORDER BY read_pct ASC; -- near 0 = almost pure write overhead

-- Analyze index structure (12c+: use ONLINE to avoid locking)
ANALYZE INDEX idx_ord_status VALIDATE STRUCTURE ONLINE;

-- Check fragmentation stats
SELECT name, height, blocks, lf_rows,
       del_lf_rows,
       ROUND(del_lf_rows * 100 /
         NULLIF(lf_rows + del_lf_rows, 0), 1) AS pct_deleted,
       rows_per_key
FROM   index_stats;
-- Rebuild if: pct_deleted > 20% OR height >= 4

-- Rebuild options (choose based on availability window)
ALTER INDEX idx_ord_status REBUILD;                   -- offline, fastest
ALTER INDEX idx_ord_status REBUILD ONLINE;            -- no DML lock (11g+)
ALTER INDEX idx_ord_status REBUILD ONLINE PARALLEL 4; -- parallel rebuild
ALTER INDEX idx_ord_status COALESCE;                  -- compact only, faster

-- Step 1: Create invisible — all other sessions unaffected
CREATE INDEX idx_ord_test
  ON orders(customer_id, status, order_date)
  INVISIBLE;

-- Step 2: Enable ONLY for your test session
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Step 3: Run the problem query — it will consider the invisible index
-- Pull the plan — is it better? Compare A-Rows, elapsed time
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(...));

-- Step 4a: Satisfied? Make visible to ALL sessions
ALTER INDEX idx_ord_test VISIBLE;

-- Step 4b: Not happy? Drop with zero production impact
DROP INDEX idx_ord_test;

-- STEP 1: Pull plan with predicates
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  '&sql_id', NULL, 'ALLSTATS LAST +PREDICATE +COST'
));

-- STEP 2: Check existing indexes on predicate columns
SELECT index_name, column_name, column_position, descend
FROM   dba_ind_columns
WHERE  table_name  = 'ORDERS'
AND    table_owner = 'APPS'
ORDER BY index_name, column_position;

-- STEP 3: Check clustering factor
SELECT index_name, num_rows, clustering_factor,
       ROUND(clustering_factor/NULLIF(num_rows,0),2) AS cf_ratio
FROM   dba_indexes
WHERE  table_name = 'ORDERS' AND owner = 'APPS';

-- STEP 4: Check column stats on predicate columns
SELECT column_name, num_distinct, density, histogram, num_buckets
FROM   dba_tab_col_statistics
WHERE  table_name = 'ORDERS' AND owner = 'APPS'
AND    column_name IN ('STATUS','CUSTOMER_ID','ORDER_DATE');

-- STEP 5: Create invisible and test
CREATE INDEX idx_ord_cust_status_date
  ON orders(customer_id, status, order_date) INVISIBLE;

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Re-run problem query, check plan + elapsed time

-- STEP 6: Deploy or clean up
ALTER INDEX idx_ord_cust_status_date VISIBLE;   -- deploy
-- OR
DROP INDEX idx_ord_cust_status_date;              -- clean up