oracle创建索引:[Oracle]如何在亿级记录表中创建索引来源: 发布时间:星期二, 2009年2月17日 浏览:10次 评论:0
="t18">
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( s/1024/1024) from user_segments
where segment_name ='STAT_SUBMIT_CENTER' and s/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_20050911 1309
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051211 1253
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20051201 1247
STAT_SUBMIT_CENTER STAT_SUBMIT_CENTER_20050921 1198
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 796
14 rows selected.
查看整个表大小:
SQL> select segment_name,sum(s/1024/1024) from user_segments
where segment_name ='STAT_SUBMIT_CENTER' group by segment_name;
SEGMENT_NAME
SEGMENT_NAME SUM(BYTES/1024/1024)
-------------------------------- --------------------
STAT_SUBMIT_CENTER 17234
查看表记录数:
SQL>  timing _disibledevent=>
create index IDX_SUBMIT_RECORDTIME _disibledevent=>
Explained.
SQL> @?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost Pstart Pstop
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 9 4
1 SORT AGGREGATE 1 9
2 PARTITION RANGE ALL 1 50
* 3 INDEX FAST FULL SCAN IDX_SUBMIT_RECORDTIME 8878K 76M 4 1 50
-------------------------------------------------------------------------------------------------
Predicate Information (ident ied by operation id):
---------------------------------------------------
3 - filter("STAT_SUBMIT_CENTER"."RECORDTIME">TRUNC(SYSDATE@!))
Note: cpu costing is off
16 rows selected.
SQL> autotrace _disibledevent=>
0
相关文章读者评论发表评论 |
|
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
