— 创建示例表
CREATE TABLE fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept VARCHAR2(50),
emp_salary NUMBER,
hire_date DATE
);/– 插入示例数据
INSERT INTO fgedu_employees VALUES (1001, ‘风哥1号’, ‘技术部’, 5000, SYSDATE – 365);INSERT INTO fgedu_employees VALUES (1002, ‘风哥2号’, ‘市场部’, 6000, SYSDATE – 180);INSERT INTO fgedu_employees VALUES (1003, ‘风哥3号’, ‘技术部’, 5500, SYSDATE – 90);INSERT INTO fgedu_employees VALUES (1004, ‘赵六’, ‘市场部’, 6500, SYSDATE – 60);INSERT INTO fgedu_employees VALUES (1005, ‘孙七’, ‘财务部’, 5800, SYSDATE – 30);– 插入更多数据
BEGIN
FOR i IN 1006 .. 10000 LOOP
INSERT INTO fgedu_employees VALUES (
i,
‘员工’ || TO_CHAR(i),
CASE MOD(i, 3)
WHEN 0 THEN ‘技术部’
WHEN 1 THEN ‘市场部’
ELSE ‘财务部’
END,
5000 + MOD(i, 2000),
SYSDATE – MOD(i, 365)
);END LOOP;COMMIT;END;/– 手动收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SYS’,
tabname => ‘FGEDU_EMPLOYEES’,
estimate_percent => 100,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
degree => 4,
cascade => TRUE
);– 收集指定列的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SYS’,
tabname => ‘FGEDU_EMPLOYEES’,
estimate_percent => 100,
method_opt => ‘FOR COLUMNS emp_dept SIZE 10, emp_salary SIZE 254’,
degree => 4
);– 收集模式级统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => ‘SYS’,
estimate_percent => 100,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
degree => 4,
cascade => TRUE
);– 收集数据库级统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent => 100,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
degree => 4,
cascade => TRUE
);– 查看表统计信息
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables
WHERE table_name = ‘FGEDU_EMPLOYEES’;– 查看列统计信息
SELECT column_name, num_distinct, low_value, high_value, last_analyzed
FROM user_tab_col_statistics
WHERE table_name = ‘FGEDU_EMPLOYEES’;