注册

统计信息问题

sanynkc 2024/08/27 491 5 已解决

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】:kylin
【CPU】:
【问题描述】*:用户下有两个表,一个city,一个region表。我执行用户统计信息,命令如下:
DBMS_STATS.GATHER_SCHEMA_STATS('DMHR',100,TRUE,'FOR ALL COLUMNS SIZE AUTO',4);
查看结果
18:19:05 sysdba_sysdba@ycq-m.dmm> select sch.name as schemaname, tab.name as tabname, st.LAST_GATHERED
from SYSSTATS st , sysobjects tab, sysobjects sch
where sch.id = tab.schid and st.id=tab.id
and sch.name in (select OBJECT_NAME from SYS.ALL_OBJECTS where ALL_OBJECTS.OBJECT_TYPE='SCH'
AND OWNER='YCQ02')
order by sch.name, tab.name;

行号 SCHEMANAME TABNAME LAST_GATHERED


1 DMHR CITY 2024-08-27 17:04:41.018000
2 DMHR CITY 2024-08-27 17:04:41.026000
3 DMHR CITY 2024-08-27 17:04:41.023000
4 DMHR CITY 2024-08-27 17:04:41.025000
5 DMHR IDX_REGION_ID 2024-08-27 17:04:41.033000
6 DMHR INDEX33568842 2024-08-27 17:04:41.035000
7 DMHR REGION 2024-08-27 17:04:41.036000
8 DMHR REGION 2024-08-27 17:04:41.039000
9 DMHR REGION 2024-08-27 17:04:41.038000

9 rows got

18:19:06 sysdba_sysdba@ycq-m.dmm>

其中city的默认索引没有统计信息:
18:25:44 sysdba_sysdba@ycq-m.dmm> SELECT A.INDEX_NAME AS "索引名"
,a.table_owner as "模式"
,A.table_NAME AS "表名"
,A.STATUS AS "状态"
,A.TABLESPACE_NAME AS "所属表空间"
,A.NUM_ROWS
,A.LAST_ANALYZED
FROM DBA_INDEXES A
WHERE OWNER IN (select OBJECT_NAME from SYS.ALL_OBJECTS where ALL_OBJECTS.OBJECT_TYPE='SCH'
AND OWNER='YCQ02')
ORDER BY table_NAME ASC;

行号 索引名 模式 表名 状态 所属表空间 NUM_ROWS LAST_ANALYZED


1 INDEX33568935 DMHR CITY VALID YCQ NULL NULL
2 IDX_REGION_ID DMHR REGION VALID YCQ 7 2024-8月-27 00:00:00
3 INDEX33568842 DMHR REGION VALID YCQ 7 2024-8月-27 00:00:00

3 rows got

18:26:08 sysdba_sysdba@ycq-m.dmm> dbms_stats.index_stats_show('DMHR','INDEX33568935');

行号 BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE


1 NULL NULL NULL NULL NULL NULL

1 rows got

18:26:09 sysdba_sysdba@ycq-m.dmm>
问题一:
DBMS_STATS.GATHER_SCHEMA_STATS('DMHR',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
统计信息是否包含索引,若包含,为什么sysstats中不显示?
还有sysstats中一个表有多条记录什么情况?

问题二:
CREATE CLUSTER INDEX DMHR.IDX_REGION_ID ON DMHR.REGION(REGION_ID);
使用这个创建聚簇索引,为什么会改变表多应索引名称。

回答 0
暂无回答
扫一扫
联系客服