关于分区的几个视图

1 查看索引在哪列以及索引类型

select user_ind_columns.index_name,user_ind_columns.column_name,

user_ind_columns.column_position,user_indexes.uniqueness,user_ind_columns.table_name

from user_ind_columns,user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = 'TT';

 

INDEX_NAME    COLUMN_NAM COLUMN_POSITION UNIQUENES TABLE_NAME

------------------------------ ---------- --------------- --------- ------------------------------

ID_INX                 ID                       1  NONUNIQUE    TT

ID_NAME_IDX          ID                       1  NONUNIQUE    TT

ID_NAME_IDX          NAME                   2  NONUNIQUE   TT

2 查看分区表每个分区的数据量有多少

analyze table table_name compute statistics;

select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS from all_tab_partitions where table_name='TLOG';( HIGH_VAL也就是分区)

TABLE_NAME                     PARTITION_NAME                 HIGH_VAL   NUM_ROWS

------------------------------ ------------------------------ -------- ----------

TLOG                           TLOG_20150504                  20150505     303437

TLOG                           TLOG_20150521                  20150522     303437

TLOG                           TLOG_20150505                  20150506     303437

TLOG                           TLOG_20141218                  20141219     303437

TLOG                           TLOG_20150215                  20150216     303437

TLOG                           TLOG_20150118                  20150119     303437

3  查看分区表根据哪个字段分区

select * from all_PART_KEY_COLUMNS where name='TLOG';

OWNER          NAME          OBJECT_TYPE COLUMN_NAME          COLUMN_POSITION

------------------------------ ------------------------------ ----------- ---------------

APPUSER        TLOG        TABLE                CUTOFFDAY          1




4  查看每个分区对应的索引名

select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_2_IDX             LOCAL1
ORDERS_LOCAL_2_IDX             LOCAL2
ORDERS_LOCAL_2_IDX             LOCAL3
ORDERS_LOCAL_2_IDX             LOCAL4
ORDERS_LOCAL_2_IDX             LOCAL5
ORDERS_LOCAL_2_IDX             LOCAL6


5 index_status，索引状态信息

点击(此处)折叠或打开

1. SQL> analyze index id_inx validate structure;
   
2. 索引已分析
   
3. SQL> select name,lf_rows,br_rows,pre_rows,del_lf_rows from index_stats where nam
   
4. e='ID_INX';
   
5. 
   
6. NAME LF_ROWS BR_ROWS PRE_ROWS DEL_LF_ROWS
   
7. ------------------------------ ---------- ---------- ---------- -----------
   
8. ID_INX 1900 4 0 900
   
9. 已选择 1 行。

6 查看blevel的高度

点击(此处)折叠或打开

SQL> analyze table test estimate statistics;

Table analyzed.

SQL> select index_name,blevel,num_rows from user_indexes where index_name='IDX_TEST';

INDEX_NAME BLEVEL NUM_ROWS
------------------------------ ---------- ----------
IDX_TEST 2 1142420

7 查看索引的大小

1. select bytes/1024/1024/1024 G,blocks,t.segment_name,t.segment_type,t.owner ,m.table_name from dba_segments t,dba_indexes m
   
2.   where t.SEGMENT_NAME =\'PKTLOG2\'and t.segment_name=m.index_name

 这个视图也可以

1. select a.owner,a.segment_name,a.segment_type,a.tablespace_name,a.header_file,a.header_block,a.bytes from
   
2.  dba_segments a where a.tablespace_name='APPSTL' and a.segment_type='INDEX' and a.segment_name like 'PK_%' order by a.bytes desc

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1475997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1475997/