查看索引的状态
博客专区 > 易野 的博客 > 博客详情
查看索引的状态
易野 发表于4个月前
查看索引的状态
  • 发表于 4个月前
  • 阅读 13
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=34212043984587&id=1019722.6&_adf.ctrl-state=jbg21eo77_41

Script 1: Index Fragmentation 
 
TFSIFRAG.SQL provides information critical in determining whether an 
index is a candidate for rebuilding.  An index is a candidate for 
rebuilding when a relatively high number of index leaf row deletes have 
occured. 
 
 Please note: This report does not indicate the actual index "balance."  

 
Script 2: Index Statistics 
 
TFSISTAT.SQL prints a variety of statistics about the given index.
Statistics include B*tree height, the number of distinct and repeated
keys, the number of  branch rows and blocks, the number of leaf rows
and blocks and information about space utilization. 
 
 Please note: You will receive an "ORA-01476:  divisor is equal to
 zero" for an index on a table into which no rows have been inserted.  
 
 
Script 3: Index Keys for a Table  
 
TFSINKEY.SQL prints uniqueness, name and column information of indexes for the
given table and owner.  The arguments are not case-sensitive, but may not 
include wildcards.  The script will behave unreliably for an index not owned 
by the owner of its base table.

 

========= 
Script #1: 
==========
 
SET ECHO off 
REM NAME:   TFSIFRAG.SQL 
REM USAGE:"@path/tfsifrag schema_name index_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on INDEX_STATS 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Reports index fragmentation statistics 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                     Index Fragmentation Statistic 
REM                 
REM    index name        S_EMP_USERID_UK 
REM    leaf rows deleted            0 
REM    leaf rows in use            25 
REM    index badness            0.000   
REM  
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off  
def ownr  = &&1  
def name  = &&2  
  
ttitle - 
  center 'Index Fragmentation Statistic'   skip 2 
  
set heading off  
  
col name                 newline  
col lf_blk_rows          newline  
col del_lf_rows          newline  
col ibadness newline   
  
validate index &ownr..&name;  
  
select  
  'index name        '||name,  
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,  
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,	  
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness  
from  
  index_stats  
/  
  
undef ownr  
undef name  
set verify on
 
 
==============
Sample Output: 
==============
 
                         Index Fragmentation Statistic 
 
 
index name                   S_EMP_USERID_UK 
leaf rows deleted            0 
leaf rows in use             25 
index badness                0.000 
 
 
 
 
========== 
Script #2: 
==========
 
SET ECHO off 
REM NAME:   TFSISTAT.SQL 
REM USAGE:"@path/tfsistat schema_name index_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on INDEX_STATS 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Report index statistics. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                                Index Statistics  
REM 
REM    S_EMP_USERID_UK  
REM    ----------------------------------------------------------  
REM    height                          1 
REM    blocks                          5 
REM    del_lf_rows                     0  
REM    del_lf_rows_len                 0 
REM    distinct_keys                  25 
REM    most_repeated_key               1  
REM    btree_space                 1,876 
REM    used_space                    447  
REM    pct_used                       24 
REM    rows_per_key                    1 
REM    blks_gets_per_access            2  
REM    lf_rows                        25            br_rows               0  
REM    lf_blks                         1            br_blks               0 
REM    lf_rows_len                   447            br_rows_len           0  
REM    lf_blk_len                  1,876            br_blk_len            0   
REM  
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off
def ownr        = &&1 
def name        = &&2 
 
ttitle - 
  center  'Index Statistics'  skip 2 
 
set heading off 
 
col name   newline 
col headsep              newline 
col height               newline 
col blocks               newline 
col lf_rows              newline 
col lf_blks        	 newline 
col lf_rows_len          newline 
col lf_blk_len           newline 
col br_rows              newline 
col br_blks              newline 
col br_rows_len          newline 
col br_blk_len           newline 
col del_lf_rows          newline 
col del_lf_rows_len      newline 
col distinct_keys        newline 
col most_repeated_key    newline 
col btree_space          newline 
col used_space    	 newline 
col pct_used             newline 
col rows_per_key         newline 
col blks_gets_per_access newline 
 
validate index &ownr..&name; 
 
select 
  name, 
  '----------------------------------------------------------'    headsep, 
  'height               '||to_char(height,     '999,999,990')     height, 
  'blocks               '||to_char(blocks,     '999,999,990')     blocks, 
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows, 
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len, 
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys, 
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key, 
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space, 
  'used_space           '||to_char(used_space,'999,999,990')        used_space, 
  'pct_used                     '||to_char(pct_used,'990')          pct_used, 
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key, 
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access, 
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+ 
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows, 
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+ 
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks, 
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+ 
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len, 
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+ 
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len 
from 
  index_stats 
/ 
 
undef ownr 
undef name 
set verify on
 
 
==============
Sample Output: 
==============
 
                                Index Statistics                
S_EMP_USERID_UK 
----------------------------------------------------------  
height                          1  
blocks                          5  
del_lf_rows                     0  
del_lf_rows_len                 0   
distinct_keys                  	25  
most_repeated_key               1  
btree_space                 	1,876 
used_space                    	447  
pct_used                       	24  
rows_per_key                    1  
blks_gets_per_access            2  
lf_rows                		25         
br_rows                 	0 
lf_blks				1         
br_blks                 	0  
lf_rows_len           		447         
br_rows_len            		0  
lf_blk_len          		1,876         
br_blk_len              	0 
 
 
 
 
========== 
Script #3: 
==========  
 
SET ECHO off 
REM NAME:   TFSIKEYS.SQL 
REM USAGE:"@path/tfsikeys idx_owner table_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM Shows the index keys for a particular table. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM             Index Keys Summary 
REM 
REM    Uniqueness                Index Name                    Column Name 
REM    ---------- ----------------------------------------  ------------------ 
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID  
REM 
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID 
REM   
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off
def ixowner	= &&1 
def tabname	= &&2 
 
ttitle - 
   center  'Index Keys Summary'  skip 2 
 
col uniq    format a10 heading 'Uniqueness'  justify c trunc 
col indname format a40 heading 'Index Name'  justify c trunc 
col colname format a25 heading 'Column Name' justify c trunc 
 
break - 
  on indname skip 1 - 
  on uniq 
 
select 
  ind.uniqueness                  uniq, 
  ind.owner||'.'||col.index_name  indname, 
  col.column_name                 colname 
from 
  dba_ind_columns  col, 
  dba_indexes      ind 
where 
  ind.owner = upper('&ixowner') 
    and 
  ind.table_name = upper('&tabname') 
    and 
  col.index_owner = ind.owner  
    and 
  col.index_name = ind.index_name 
order by 
  col.index_name, 
  col.column_position 
/ 
 
undef ixowner 
undef tabname 
set verify on


==============
Sample Output: 
==============

 
         Index Keys Summary 
 
 
Uniqueness                Index Name                    Column Name 
---------- ---------------------------------------- ---------------------- 
UNIQUE                SCOTT.S_EMP_ID_PK                        ID 
                                                                       
UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID
标签: Oracle
共有 人打赏支持
粉丝 0
博文 85
码字总数 66043
×
易野
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: