search
HomeDatabaseMysql TutorialOracle表与索引的分析及索引重建

1.分析表与索引(analyze 不会重建索引) analyze table tablename compute statistics 等同于 analyze table tablename compute

1.分析表与索引(analyze 不会重建索引)

analyze table tablename compute statistics
等同于 analyze table tablename compute statistics for table for all indexes for all columns

for table 的统计信息存在于视图:user_tables 、all_tables、dba_tables

for all indexes 的统计信息存在于视图: user_indexes 、all_indexes、dba_indexes

for all columns 的统计信息存在于视图:user_tab_columns、all_tab_columns、dba_tab_columns

注:分析表与索引见 AnalyzeAllTable存储过程

2、一般来讲可以采用以下三种方式来手工分析索引。
analyze index idx_t validate structure:
analyze index idx_t compute statistics:
analyze index idx_t estimate statistics sample 10 percent

1)analyze index idx_t validate structure:
这段分析语句是用来分析索引的block中是否有坏块儿,那么根据分析我们可以得到索引的结构数据,这些数据会保留到
index_stats中,来判断这个索引是否需要rebuild. 需要注意的是这样的分析是不会收集索引的统计信息的。

2)validate structure有二种模式: online, offline, 一般来讲默认的方式是offline。
当以offline的模式analyze索引时,会对table加一个表级共享锁,对目前table的一些实时DMl操作会产生一定的影响。
而以online模式分析时候,则不会加任何lock,,但在index_stats中是看不到任何信息的。

3)analyze index idx_t compute statistics:
用来统计索引的统计信息(全分析),主要为CBO服务。

4)analyze index idx_t estimate statistics sample 10 percent
主要是用来指定比例进行抽样分析,也是为CBO服务. 例中是抽样10%

3.重建索引
alter index index_name rebuild tablespace tablespace_name
alter index index_name rebuild tablespace tablespace_name 加入表空间名,会将指定的索引移动到指定的表空间当中。

注:
analyze 操作只是统计信息,并将统计信息存放起来供日后分析SQL使用,不进行重建之类的具体实施性操作,因此要重建索引的话
还是要用 alter index index_name rebuild

4、其他的统计方法

1)DBMS_STATS:这个当然是最强大的分析包了
--创建统计信息历史保留表
exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table');

--导出整个scheme的统计信息
exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table');

--分析scheme
Exec dbms_stats.gather_schema_stats(ownname => 'test',options => 'GATHER AUTO',
 estimate_percent => dbms_stats.auto_sample_size,
 method_opt => 'for all indexed columns',
 degree => 6 );

--分析表
exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'sm_user',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

--分析索引
exec dbms_stats.gather_index_stats(ownname => 'TEST',indname => 'pk_user_index',estimate_percent => '10',degree => '4') ;

--如果发现执行计划走错,删除表的统计信息
exec dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'SM_USER') ;

--导入表的历史统计信息
exec dbms_stats.import_table_stats(ownname => 'TEST',tabname => 'SM_USER',stattab => 'stat_table') ;

--如果进行分析后,大部分表的执行计划都走错,需要导回整个scheme的统计信息
exec dbms_stats.import_schema_stats(ownname => 'TEST',stattab => 'SM_USER');

--导入索引的统计信息
exec dbms_stats.import_index_stats(ownname => 'TEST',indname => 'PK_USER_INDEX',stattab => 'stat_table')

analyze和dbms_stats不同的地方:
analyze是同时更新表和索引的统计信息,而dbms_stats会先更新表的统计信息,然后再更新索引的统计信息,
这里就有一个问题,就是当表的统计信息更新后,而索引的统计信息没有被更新,这时候cbo就有可能选择错误的plan

2)DBMS_UTILITY.ANALYZE_SCHEMA:可直接分析SCHEMA中所有对象
如:EXEC DBMS_UTILITY.ANALYZE_SCHEMA ('LTTFM','COMPUTE');

3)DBMS_DDL.ANALYZE_OBJECT:收集对象的的统计信息

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

How do you handle large datasets in MySQL?How do you handle large datasets in MySQL?Mar 21, 2025 pm 12:15 PM

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

How do you drop a table in MySQL using the DROP TABLE statement?How do you drop a table in MySQL using the DROP TABLE statement?Mar 19, 2025 pm 03:52 PM

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

How do you represent relationships using foreign keys?How do you represent relationships using foreign keys?Mar 19, 2025 pm 03:48 PM

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

How do you create indexes on JSON columns?How do you create indexes on JSON columns?Mar 21, 2025 pm 12:13 PM

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?Mar 18, 2025 pm 12:00 PM

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.