ANALYZE TABLE
說明
ANALYZE TABLE 陳述式收集特定資料表或特定資料庫中所有資料表的統計資料，供查詢最佳化器使用，以找出更好的查詢執行計畫。
語法
ANALYZE TABLE table_identifier [ partition_spec ]
COMPUTE STATISTICS [ NOSCAN | FOR COLUMNS col [ , ... ] | FOR ALL COLUMNS ]
ANALYZE TABLES [ { FROM | IN } database_name ] COMPUTE STATISTICS [ NOSCAN ]
參數
-
table_identifier
指定資料表名稱，可以選擇性地加上資料庫名稱。
語法：
[ database_name. ] table_name -
partition_spec
一個選擇性參數，指定分割區的鍵值對清單，以逗號分隔。指定時，會傳回分割區統計資料。
語法：
PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] ) -
{ FROM
|IN } database_name指定要分析的資料庫名稱。沒有資料庫名稱，
ANALYZE會收集目前資料庫中目前使用者有權限分析的所有資料表。 -
NOSCAN
只收集資料表的位元組大小 (不需要掃描整個資料表)。
-
FOR COLUMNS col [ , … ]
|FOR ALL COLUMNS收集指定每一個欄位的欄位統計資料，或收集每個欄位和資料表統計資料。
如果沒有指定分析選項，會收集列數和位元組大小。
範例
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE teachers (name STRING, teacher_id INT);
INSERT INTO teachers VALUES ('Tom', 1), ('Jerry', 2);
CREATE TABLE students (name STRING, student_id INT) PARTITIONED BY (student_id);
INSERT INTO students VALUES ('Mark', 111111), ('John', 222222);
ANALYZE TABLE students COMPUTE STATISTICS NOSCAN;
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 bytes| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS;
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 bytes, 2 rows| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students PARTITION (student_id = 111111) COMPUTE STATISTICS;
DESC EXTENDED students PARTITION (student_id = 111111);
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
|Partition Statistics| 432 bytes, 1 rows| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLE students COMPUTE STATISTICS FOR COLUMNS name;
DESC EXTENDED students name;
+--------------+----------+
| info_name|info_value|
+--------------+----------+
| col_name| name|
| data_type| string|
| comment| NULL|
| min| NULL|
| max| NULL|
| num_nulls| 0|
|distinct_count| 2|
| avg_col_len| 4|
| max_col_len| 4|
| histogram| NULL|
+--------------+----------+
ANALYZE TABLES IN school_db COMPUTE STATISTICS NOSCAN;
DESC EXTENDED teachers;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| teacher_id| int| null|
| ...| ...| ...|
| Statistics| 1382 bytes| |
| ...| ...| ...|
+--------------------+--------------------+-------+
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 bytes| |
| ...| ...| ...|
+--------------------+--------------------+-------+
ANALYZE TABLES COMPUTE STATISTICS;
DESC EXTENDED teachers;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| teacher_id| int| null|
| ...| ...| ...|
| Statistics| 1382 bytes, 2 rows| |
| ...| ...| ...|
+--------------------+--------------------+-------+
DESC EXTENDED students;
+--------------------+--------------------+-------+
| col_name| data_type|comment|
+--------------------+--------------------+-------+
| name| string| null|
| student_id| int| null|
| ...| ...| ...|
| Statistics| 864 bytes, 2 rows| |
| ...| ...| ...|
+--------------------+--------------------+-------+