Oracle 11g 引入擴展統計,允許對多列組合建立統計信息,優化器可更準確估算多列關聯查詢的基數。
SELECT * FROM scott.emp WHERE 1=2;
SELECT dbms_stats.create_extended_stats('scott','emp','(empno,job)') FROM dual;
返回系統生成的虛擬列名,如 SYS_STU9D8L7V4NR#823O9R1EKCA6C。
SELECT * FROM dba_tab_cols WHERE column_name LIKE 'SYS_STU%';
SELECT * FROM dba_stat_extensions WHERE extension_name = 'SYS_STU9D8L7V4NR#823O9R1EKCA6C';
EXEC dbms_stats.gather_table_stats(
ownname => 'scott',
tabname => 'emp',
method_opt => 'for columns (empno,job) size AUTO'
);
SIZE AUTO 自動決定直方圖需求