Overview

Method

A. Example Scripts

A.1 Collecting Statistics for Partition Tables

A.1.1 create_table_partition.sql

The following is an example of creating a Partition Table.

CREATE TABLE DAILY_LOG (
   LOG_DAY VARCHAR2(8),
   C1 NUMBER,
   C2 NUMBER,
   C3 CHAR(2000),
   C4 CHAR(2000),
   C5 CHAR(2000)   )
   PARTITION BY RANGE (LOG_DAY)
   (
     PARTITION P20230101 VALUES LESS THAN ('20230102'),
     PARTITION P20230102 VALUES LESS THAN ('20230103'),
     PARTITION P20230103 VALUES LESS THAN ('20230104'),
     PARTITION P20230104 VALUES LESS THAN ('20230105'),
     PARTITION P20230105 VALUES LESS THAN ('20230106'),
     PARTITION P20230106 VALUES LESS THAN ('20230107'),
     PARTITION P20230107 VALUES LESS THAN ('20230108'),
     PARTITION P20230108 VALUES LESS THAN ('20230109'),
     PARTITION P20230109 VALUES LESS THAN ('20230110'),
     PARTITION P20230110 VALUES LESS THAN ('20230111'),
     PARTITION P20230111 VALUES LESS THAN ('20230112'),
     PARTITION P20230112 VALUES LESS THAN ('20230113'),
     PARTITION P20230113 VALUES LESS THAN ('20230114'),
     PARTITION P20230114 VALUES LESS THAN ('20230115'),
     PARTITION P20230115 VALUES LESS THAN ('20230116'),
     PARTITION P20230116 VALUES LESS THAN ('20230117'),
     PARTITION P20230117 VALUES LESS THAN ('20230118'),
     PARTITION P20230118 VALUES LESS THAN ('20230119'),
     PARTITION P20230119 VALUES LESS THAN ('20230120'),
     PARTITION P20230120 VALUES LESS THAN ('20230121')
   );
 
 BEGIN
   FOR i IN 1..20 LOOP
     INSERT INTO DAILY_LOG
     SELECT '202301'||LPAD(i,2,'0'), LEVEL, LEVEL, LEVEL, LEVEL, LEVEL
     FROM DUAL CONNECT BY LEVEL <= 10000;
     COMMIT;
   END LOOP;
 END;
 /

A.1.2 create_index.sql

The following is an example of creating Indexes.

CREATE INDEX DAILY_LOG_IDX_01 ON DAILY_LOG (LOG_DAY) LOCAL;
CREATE INDEX DAILY_LOG_IDX_02 ON DAILY_LOG (LOG_DAY, C1) LOCAL;
CREATE INDEX DAILY_LOG_IDX_03 ON DAILY_LOG (LOG_DAY, C1, C2) LOCAL;
CREATE INDEX DAILY_LOG_IDX_04 ON DAILY_LOG (LOG_DAY, C3) LOCAL;

A.1.3 add_partition.sql

The following is an example of adding a partition.

ALTER TABLE DAILY_LOG ADD PARTITION P20230121 VALUES LESS THAN ('20250122');
INSERT INTO DAILY_LOG SELECT '20250121',1,1,1,1,1 FROM DUAL CONNECT BY LEVEL<=10000;
COMMIT;

Partitions P20250101 through P20250120 have statistics information, but P20250121 has not had statistics collected. Therefore, the maximum value of the LOG_DAY column statistics will likely be '20250120'.

A.1.4 view_partition_plan.sql

The following example shows the difference in query plans between partitions with and without statistics information.

set autotrace traceonly exp planstat
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250120';
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250121';
set autotrace off

Partitions P20250101 through P20250120 have statistics information, but P20250121 has not had statistics collected. Therefore, the maximum value of the LOG_DAY column statistics will likely be '20250120'. The above two queries will produce different plans accordingly.

A.1.5 stats_copy.sql

The following is an example of copying statistics and checking the plan using COPY_TABLE_STATS.

CALL DBMS_STATS.COPY_TABLE_STATS (USER, 'DAILY_LOG', 'P20250120', 'P20250121');
ALTER SYSTEM FLUSH PPC;
set autotrace traceonly exp planstat
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20230120';
SELECT * FROM DAILY_LOG WHERE LOG_DAY='20250121';
set autotrace off

Through COPY_TABLE_STATS, statistics information is created for the P20250121 partition, and histograms are also updated. Now, both plans will show the same FULL TABLE SCAN.

A.2 Collecting Column Statistics

A.2.1 create_table_for_histogram.sql

The following is an example of creating schema and data.

 CREATE TABLE ARMY (
   ID   
NUMBER(10),
   NAME VARCHAR2(10),
   JOB  VARCHAR2(20),
   SAL  NUMBER,
   DEPT VARCHAR2(20)
   )
 PCTFREE 90;
 
 INSERT INTO ARMY
   SELECT LEVEL,
     CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26))
     ||CHR(65+MOD(ABS(DBMS_RANDOM.RANDOM),26)),
     CASE WHEN MOD(LEVEL,100)<80 THEN 'INFANTRY'
     WHEN MOD(LEVEL,100)<90 THEN 'ARCHER'
     WHEN MOD(LEVEL,100)<95 THEN 'CAVALRY'
     WHEN MOD(LEVEL,100)<98 THEN 'WIZARD'
     ELSE 'GENERAL' END,
     (1+MOD(ABS(DBMS_RANDOM.RANDOM),99))*100,
     CASE MOD(LEVEL,8)
     WHEN 0 THEN 'PROMISE'
     WHEN 1 THEN 'PROMISE'
     WHEN 2 THEN 'RETHINKING'
     WHEN 3 THEN 'INFIELD'
     WHEN 4 THEN 'INFIELD'
     WHEN 5 THEN 'DETAIL1'
     WHEN 6 THEN 'ENDLESS'
     ELSE 'ENDLESS' END
 FROM DUAL CONNECT BY LEVEL<=10000;

 COMMIT;
 
 ALTER TABLE ARMY ADD CONSTRAINT ARMY_PK PRIMARY KEY (ID);

A.2.2 create_index_for_histogram.sql

The following is an example of creating Indexes.

 CREATE INDEX ARMY_IDX_NAME ON ARMY (NAME);
 CREATE INDEX ARMY_IDX_JOB ON ARMY (JOB);
 CREATE INDEX ARMY_IDX_SAL ON ARMY (SAL);
 CREATE INDEX ARMY_IDX_DEPT ON ARMY (DEPT);

A.2.3 view_histogram.sql

The following is an example of checking the generated histograms.

 set linesize 300
 set pagesize 50000
 col name format a10
 col end_point_actual format a50
 col low_val format a50
 col high_val format a50
 
 SELECT C.NAME, HH.BUCKET_CNT, HH.NULL_CNT, HH.DISTINCT_CNT,
   HH.LOW_VAL, HH.HIGH_VAL, HH.DENSITY, HH.AVG_COL_SIZE
 FROM SYS._DD_HIST_HEAD HH, SYS._DD_COL C
 WHERE HH.OBJ_ID = C.OBJ_ID
   AND HH.COL_NO = C.COL_NO
   AND C.OBJ_ID = (SELECT OBJ_ID FROM SYS._DD_OBJ WHERE NAME='ARMY' AND TYPE_NO=1)
   AND C.NAME NOT LIKE 'NOTE%'
   ORDER BY C.COL_NO;
 
 SELECT C.NAME, H.* 
 FROM SYS._DD_HISTOGRAM H, SYS._DD_HIST_HEAD HH, SYS._DD_COL C
 WHERE H.HIST_HEAD_ID = HH.HIST_HEAD_ID
   AND HH.OBJ_ID = C.OBJ_ID
   AND HH.COL_NO = C.COL_NO
   AND C.OBJ_ID = (SELECT OBJ_ID FROM SYS._DD_OBJ WHERE NAME='ARMY' AND TYPE_NO=1)
   AND C.NAME NOT LIKE 'NOTE%'
 ORDER BY C.COL_NO, H.BUCKET;

A.2.4 view_plan_detail_for_histogram.sql

The following is an example of checking query plans.

set lines 150
set autotrace traceonly exp planstat
SELECT * FROM ARMY WHERE ID <= 100;
SELECT * FROM ARMY WHERE SAL >= 5000; 
SELECT * FROM ARMY WHERE DEPT = 'PROMISE'; 
SELECT * FROM ARMY WHERE JOB = 'INFANTRY';
set autotrace off

A.2.5 set_default_histogram.sql

The following is an example of collecting with the number of Buckets set to 1.

CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',estimate_percent=100);

A.2.6 set_histogram.sql

The following is an example of collecting with the number of Buckets set to 10.

CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',
  method_opt='FOR ALL COLUMNS SIZE 10',
  estimate_percent=100);