Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> set linesize 130 pagesize 0 SQL> SQL> drop table fct_sales; Table dropped. SQL> drop table dim_month_of_sale; Table dropped. SQL> drop table dim_location; Table dropped. SQL> SQL> create table dim_month_of_sale 2 ( 3 month_of_sale date primary key 4 check (month_of_sale = trunc(month_of_sale,'MM')), 5 fiscal_month char(7) not null unique, 6 fiscal_year char(4) not null 7 ) 8 tablespace test 9 / Table created. SQL> -- add twelve month records SQL> insert into dim_month_of_sale 2 select add_months(date '2005-10-01',rownum-1), 3 to_char(add_months(date '2005-10-01',rownum+2),'YYYY MM'), 4 to_char(add_months(date '2005-10-01',rownum+2),'YYYY') 5 from dual 6 connect by level <= 12 7 / 12 rows created. SQL> SQL> create table dim_location 2 ( 3 location_cd number primary key, 4 region varchar2(8) not null 5 ) 6 tablespace test 7 / Table created. SQL> insert into dim_location 2 select rownum, 3 'Region '||to_char(mod(rownum,3)+1,'fm9') 4 from dual 5 connect by level <= 12 6 / 12 rows created. SQL> SQL> /* DOC>|| Create RANGE partitioned fact table DOC>*/ SQL> create table fct_sales 2 ( 3 month_of_sale date not null 4 references dim_month_of_sale 5 check (month_of_sale = trunc(month_of_sale,'MM')), 6 location_cd number not null 7 references dim_location, 8 transaction_id number not null, 9 sale_amt number not null 10 ) 11 pctfree 0 nologging 12 tablespace test 13 partition by range(month_of_sale) 14 ( 15 partition Y2005M10 values less than (date '2005-11-01'), 16 partition Y2005M11 values less than (date '2005-12-01'), 17 partition Y2005M12 values less than (date '2006-01-01'), 18 partition Y2006M01 values less than (date '2006-02-01'), 19 partition Y2006M02 values less than (date '2006-03-01'), 20 partition Y2006M03 values less than (date '2006-04-01'), 21 partition Y2006M04 values less than (date '2006-05-01'), 22 partition Y2006M05 values less than (date '2006-06-01'), 23 partition Y2006M06 values less than (date '2006-07-01'), 24 partition Y2006M07 values less than (date '2006-08-01'), 25 partition Y2006M08 values less than (date '2006-09-01'), 26 partition Y2006M09 values less than (date '2006-10-01')) 27 / Table created. SQL> SQL> /* DOC>|| Populate fact table with 12000 rows per partition DOC>*/ SQL> SQL> insert /*+ append */ 2 into fct_sales 3 ( 4 month_of_sale, 5 location_cd, 6 transaction_id, 7 sale_amt 8 ) 9 select month_of_sale, 10 location_cd, 11 rownum, 12 dbms_random.value(0.01,100) 13 from dim_month_of_sale, 14 dim_location, 15 (select 1 from dual connect by level <= 1000) 16 / 144000 rows created. SQL> commit 2 / Commit complete. SQL> SQL> SQL> begin 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 'fct_sales', 5 method_opt=> 'for columns location_cd size 254', 6 granularity => 'ALL'); 7 dbms_stats.gather_table_stats( 8 ownname => user, 9 tabname => 'dim_month_of_sale', 10 method_opt=> 'for all columns size 254'); 11 dbms_stats.gather_table_stats( 12 ownname => user, 13 tabname => 'dim_location', 14 method_opt=> 'for all columns size 254'); 15 end; 16 / PL/SQL procedure successfully completed. SQL> SQL> -- Check that no column statistics are in place for MONTH_OF_SALE SQL> select column_name, 2 count(low_value), 3 count(*) 4 from user_part_col_statistics 5 where table_name = 'FCT_SALES' 6 group by column_name 7 / MONTH_OF_SALE 0 12 TRANSACTION_ID 0 12 LOCATION_CD 12 12 SALE_AMT 0 12 SQL> SQL> explain plan for 2 select * 3 from fct_sales fs, 4 dim_month_of_sale dm 5 where fs.month_of_sale = dm.month_of_sale and 6 dm.month_of_sale = date '2006-01-01' 7 / Explained. SQL> SQL> select * from table(dbms_xplan.display()) 2 / Plan hash value: 3599529138 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 58 | 10 (10)| 00:00:01 | | | | 1 | NESTED LOOPS | | 1 | 58 | 10 (10)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| DIM_MONTH_OF_SALE | 1 | 21 | 1 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | SYS_C007850 | 1 | | 0 (0)| 00:00:01 | | | | 4 | PARTITION RANGE SINGLE | | 1 | 37 | 9 (12)| 00:00:01 | 4 | 4 | |* 5 | TABLE ACCESS FULL | FCT_SALES | 1 | 37 | 9 (12)| 00:00:01 | 4 | 4 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DM"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 5 - filter("FS"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 21 rows selected. SQL> SQL> -- Gather column statistics for the partition key SQL> SQL> begin 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 'fct_sales', 5 method_opt=> 'for columns location_cd size 254, month_of_sale size 1', 6 granularity => 'ALL'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> explain plan for 2 select * 3 from fct_sales fs, 4 dim_month_of_sale dm 5 where fs.month_of_sale = dm.month_of_sale and 6 dm.month_of_sale = date '2006-01-01' 7 / Explained. SQL> SQL> select * from table(dbms_xplan.display()) 2 / Plan hash value: 3599529138 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 120 | 6960 | 12 (25)| 00:00:01 | | | | 1 | NESTED LOOPS | | 120 | 6960 | 12 (25)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| DIM_MONTH_OF_SALE | 1 | 21 | 1 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | SYS_C007850 | 1 | | 0 (0)| 00:00:01 | | | | 4 | PARTITION RANGE SINGLE | | 120 | 4440 | 11 (28)| 00:00:01 | 4 | 4 | |* 5 | TABLE ACCESS FULL | FCT_SALES | 120 | 4440 | 11 (28)| 00:00:01 | 4 | 4 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DM"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 5 - filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "FS"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 21 rows selected. SQL> SQL> SQL> drop table fct_sales 2 / Table dropped. SQL> SQL> /* DOC>|| Create LIST partitioned fact table DOC>*/ SQL> create table fct_sales 2 ( 3 month_of_sale date not null 4 references dim_month_of_sale 5 check (month_of_sale = trunc(month_of_sale,'MM')), 6 location_cd number not null 7 references dim_location, 8 transaction_id number not null, 9 sale_amt number not null 10 ) 11 pctfree 0 nologging 12 tablespace test 13 partition by list (month_of_sale) 14 ( 15 partition Y2005M10 values (date '2005-10-01'), 16 partition Y2005M11 values (date '2005-11-01'), 17 partition Y2005M12 values (date '2005-12-01'), 18 partition Y2006M01 values (date '2006-01-01'), 19 partition Y2006M02 values (date '2006-02-01'), 20 partition Y2006M03 values (date '2006-03-01'), 21 partition Y2006M04 values (date '2006-04-01'), 22 partition Y2006M05 values (date '2006-05-01'), 23 partition Y2006M06 values (date '2006-06-01'), 24 partition Y2006M07 values (date '2006-07-01'), 25 partition Y2006M08 values (date '2006-08-01'), 26 partition Y2006M09 values (date '2006-09-01')) 27 / Table created. SQL> SQL> /* DOC>|| Populate fact table with 12000 rows per partition DOC>*/ SQL> SQL> insert /*+ append */ 2 into fct_sales 3 ( 4 month_of_sale, 5 location_cd, 6 transaction_id, 7 sale_amt 8 ) 9 select month_of_sale, 10 location_cd, 11 rownum, 12 dbms_random.value(0.01,100) 13 from dim_month_of_sale, 14 dim_location, 15 (select 1 from dual connect by level <= 1000) 16 / 144000 rows created. SQL> commit 2 / Commit complete. SQL> begin 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 'fct_sales', 5 method_opt=> 'for columns location_cd size 254', 6 granularity => 'ALL'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> -- Check that no column statistics are in place for MONTH_OF_SALE SQL> select column_name, 2 count(low_value), 3 count(*) 4 from user_part_col_statistics 5 where table_name = 'FCT_SALES' 6 group by column_name 7 / MONTH_OF_SALE 0 12 TRANSACTION_ID 0 12 LOCATION_CD 12 12 SALE_AMT 0 12 SQL> SQL> explain plan for 2 select * 3 from fct_sales fs, 4 dim_month_of_sale dm 5 where fs.month_of_sale = dm.month_of_sale and 6 dm.month_of_sale = date '2006-01-01' 7 / Explained. SQL> SQL> select * from table(dbms_xplan.display()) 2 / Plan hash value: 2812282414 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 58 | 10 (10)| 00:00:01 | | | | 1 | NESTED LOOPS | | 1 | 58 | 10 (10)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| DIM_MONTH_OF_SALE | 1 | 21 | 1 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | SYS_C007850 | 1 | | 0 (0)| 00:00:01 | | | | 4 | PARTITION LIST SINGLE | | 1 | 37 | 9 (12)| 00:00:01 | 4 | 4 | |* 5 | TABLE ACCESS FULL | FCT_SALES | 1 | 37 | 9 (12)| 00:00:01 | 4 | 4 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DM"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 5 - filter("FS"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 21 rows selected. SQL> SQL> -- Gather column statistics for the partition key SQL> SQL> begin 2 dbms_stats.gather_table_stats( 3 ownname => user, 4 tabname => 'fct_sales', 5 method_opt=> 'for columns location_cd size 254, month_of_sale size 1', 6 granularity => 'ALL'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> explain plan for 2 select * 3 from fct_sales fs, 4 dim_month_of_sale dm 5 where fs.month_of_sale = dm.month_of_sale and 6 dm.month_of_sale = date '2006-01-01' 7 / Explained. SQL> SQL> select * from table(dbms_xplan.display()) 2 / Plan hash value: 2812282414 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 120 | 6960 | 12 (25)| 00:00:01 | | | | 1 | NESTED LOOPS | | 120 | 6960 | 12 (25)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| DIM_MONTH_OF_SALE | 1 | 21 | 1 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | SYS_C007850 | 1 | | 0 (0)| 00:00:01 | | | | 4 | PARTITION LIST SINGLE | | 120 | 4440 | 11 (28)| 00:00:01 | 4 | 4 | |* 5 | TABLE ACCESS FULL | FCT_SALES | 120 | 4440 | 11 (28)| 00:00:01 | 4 | 4 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DM"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 5 - filter(TRUNC(INTERNAL_FUNCTION("MONTH_OF_SALE"),'fmmm')=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "FS"."MONTH_OF_SALE"=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 21 rows selected. SQL>