Oracle 如何在亿级记录表中创建索引
1. 查看表的具体情况
查看是不是分区表,有多少个分区、分区字段:
SQL> col table_name for a20
SQL> col column_name for a20
SQL> select a.table_name,a.partitioned,b.partition_count,c.column_name
2 from user_tables a, user_part_tables b, user_part_key_columns c
3 where a.table_name="STAT_SUBMIT_CENTER"
4 and b.table_name="STAT_SUBMIT_CENTER"
5 and c.name="STAT_SUBMIT_CENTER";
TABLE_NAME PAR PARTITION_COUNT COLUMN_NAME
-------------------- --- --------------- --------------------
STAT_SUBMIT_CENTER YES 50 MSGDATE
查看已使用的每个分区的大小:
SQL> select segment_name,partition_name,round(bytes/1024/1024) from user_segments
where segment_name ="STAT_SUBMIT_CENTER" and bytes/1024/1024>0.25 order by 3 desc;
SEGMENT_NAME PARTITION_NAME
SEGMENT_NAME PARTITION_NAME ROUND(BYTES/1024/1024)
-------------------------- ------------------------------ ----------------------
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051101 1722
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051021 1488
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051111 1440
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051121 1355
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051221 1335
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050Array11 130Array
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051211 1253
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051201 1247
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050Array21 11Array8
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060101 1151
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060111 1068
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051001 1018
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051011 865
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20060121 7Array6
14 rows selected.
查看整个表的大小:
SQL> select segment_name,sum(bytes/1024/1024) from user_segments
where segmen