-- ------------------------------
-- Check columns used in an index
-- ------------------------------
column column_name format a40
set pagesize 999
SELECT
index_name
,column_name
FROM
all_ind_columns
WHERE 1=1
AND table_name = upper('&&1')
ORDER BY 1,2
;

-- ----------------------------------------
-- Check columns used in a functional index
-- ----------------------------------------
set long 40
set pagesize 999
SELECT
index_name
,column_expression
FROM
all_ind_expressions
WHERE 1=1
AND table_name = upper('&&1')
ORDER BY index_name, column_position
;

-- --------------------------------
-- Execution test for explain plan
-- uses SQL in the xpt.sql script
-- --------------------------------
-- Windows
host copy xphead.sql+xpt.sql+xptail.sql=xpt2.sql
-- Unix
--!cat xphead.sql xpt.sql xptail.sql xpt2.sql
spool xpt.out
@xpt2.sql
spool off

-- --------------------------------------------
-- SQL to be explained using explain plan tools
-- include terminating semicolon (;)
-- --------------------------------------------
select count(*)
from big_table
;

-- ----------------------------
-- Explain plan starting script
-- ----------------------------
SET pagesize 999
SET feedback OFF
DELETE FROM plan_table WHERE STATEMENT_ID = 'NOBLEA';
explain plan set statement_id = 'NOBLEA' FOR

-- ---------------------------------------
-- tail end of the script for explain plan
-- ---------------------------------------
SELECT
ROWNUM||' '||LPAD(' ', 2*(LEVEL-1))||operation||' '||options||' '||
DECODE(object_instance, NULL, object_name
, TO_CHAR(object_instance)||'*'||object_name) PLAN
FROM
plan_table
START WITH id=0
CONNECT BY PRIOR id = parent_id AND statement_id = 'NOBLEA'
ORDER BY id;
ROLLBACK;