B树索引学习总结。 - 数据库编程

DEX VALID SQL> col column_name for a20 SQL> col column_position for 9999 SQL> r 1* SELECT INDEX_NAME,COLUMN_NAME,COLUMN_POSITION FROM USER_ind_columns where table_name in ('T','EMP','DEPT') ORDER by index_name,column_position INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------- --------------- DEPT_PK DEPTNO 1 EMP_IDX2 DEPTNO 1 EMP_PK EMPNO 1 EMP_UK EMPNO 1 EMP_UK ENAME 2 T_IDX1 A 1 6 rows selected. SQL> SQL>

修改索引状态：

SQL> alter index emp_idx2 invisible;

Index altered.

SQL> select index_name,status,visibility from user_indexes;

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
EMP_UK                         VALID    VISIBLE
EMP_PK                         VALID    VISIBLE
EMP_IDX2                       VALID    INVISIBLE
DEPT_PK                        VALID    VISIBLE
T_IDX1                         VALID    VISIBLE

SQL> alter index emp_idx2 visible;

Index altered.

SQL> select index_name,status,visibility from user_indexes;

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
EMP_UK                         VALID    VISIBLE
EMP_PK                         VALID    VISIBLE
EMP_IDX2                       VALID    VISIBLE
DEPT_PK                        VALID    VISIBLE
T_IDX1                         VALID    VISIBLE

SQL> 
SQL> alter index emp_idx2 unusable;

Index altered.

SQL> select index_name,status,visibility from user_indexes;

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
EMP_UK                         VALID    VISIBLE
EMP_PK                         VALID    VISIBLE
EMP_IDX2                       UNUSABLE VISIBLE
DEPT_PK                        VALID    VISIBLE
T_IDX1                         VALID    VISIBLE

SQL> alter index emp_idx2 rebuild;

Index altered.

SQL> select index_name,status,visibility from user_indexes;

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
EMP_UK                         VALID    VISIBLE
EMP_PK                         VALID    VISIBLE
EMP_IDX2                       VALID    VISIBLE
DEPT_PK                        VALID    VISIBLE
T_IDX1                         VALID    VISIBLE

SQL> 
SQL> 
SQL>

判断外键索引是否存在：

方法一：

SET VERIFY OFF;
SET LINESIZE 200;
COL IND_COLUMN FOR A25;
COL CONS_COLUMN FOR A25;
COL TAB_NAME FOR A20;
COL OWNER FOR A25;
select distinct a.owner owner,
                a.constraint_name cons_name,
                a.table_name tab_name,
                b.column_name cons_column,
                nvl(c.column_name, '***Check index****') ind_column,
                c.index_name
  from dba_constraints a, dba_cons_columns b, dba_ind_columns c
 where constraint_type = 'R'
   and a.owner = upper('&&user_name')
   and a.owner = b.owner
   and a.constraint_name = b.constraint_name
   and b.column_name = c.column_name(+)
   and b.table_name = c.table_name(+)
   and b.position = c.column_position(+)
 order by tab_name, ind_column;


#############################

SQL> conn amy/rhys
Connected.
SQL> drop index emp_idx2;

Index dropped.

SQL> conn sys/root as sysdba
Connected.
SQL> SET VERIFY OFF;
SET LINESIZE 200;
SQL> SQL> COL IND_COLUMN FOR A25;
SQL> COL CONS_COLUMN FOR A25;
SQL> COL TAB_NAME FOR A20;
SQL> COL OWNER FOR A25;
SQL> select distinct a.owner owner,
  2                  a.constraint_name cons_name,
  3                  a.table_name tab_name,
                b.column_name cons_column,
  4    5                  nvl(c.column_name, '***Check index****') ind_column,
  6                  c.index_name
  7    from dba_constraints a, dba_cons_columns b, dba_ind_columns c
  8   where constraint_type = 'R'
  9     and a.owner = upper('&&user_name')
 10     and a.owner = b.owner
 11     and a.constraint_name = b.constraint_name
 12     and b.column_name = c.column_name(+)
 13     and b.table_name = c.table_name(+)
 14     and b.position =

B树索引学习总结。(五)