{14 パフォーマンス}自動列グループ検出機能を使用

 

https://docs.oracle.com/cd/F19136_01/tgsql/managing-extended-statistics.html#GUID-E1F39134-24F1-4EF7-B614-82F9428CA762

 

CONNECT sh/sh@pdb11

DROP TABLE customers_test;
CREATE TABLE customers_test AS SELECT * FROM CUSTOMERS;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'customers_test');

BEGIN
  DBMS_STATS.SEED_COL_USAGE(null,null,300);
END;
/

EXPLAIN PLAN FOR
  SELECT *
  FROM   customers_test
  WHERE  cust_city = 'Los Angeles'
  AND    cust_state_province = 'CA'
  AND    country_id = 52790;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
 
EXPLAIN PLAN FOR
  SELECT   country_id, cust_state_province, count(cust_city)
  FROM     customers_test
  GROUP BY country_id, cust_state_province;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));

SET LONG 100000
SET LINES 120
SET PAGES 0
SELECT DBMS_STATS.REPORT_COL_USAGE('SH', 'customers_test')
FROM   DUAL;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SH', 'customers_test') FROM DUAL;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','customers_test');

SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'CUSTOMERS_TEST'
ORDER BY 1;

EXPLAIN PLAN FOR
  SELECT *
  FROM   customers_test
  WHERE  cust_city = 'Los Angeles'
  AND    cust_state_province = 'CA'
  AND    country_id = 52790;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));
 
EXPLAIN PLAN FOR
  SELECT   country_id, cust_state_province, count(cust_city)
  FROM     customers_test
  GROUP BY country_id, cust_state_province;
 
SELECT PLAN_TABLE_OUTPUT 
FROM   TABLE(DBMS_XPLAN.DISPLAY('plan_table', null,'basic rows'));