02 October, 2010

Data Skew and Cardinality Changing --- 2

In my earlier post, I demonstrated how Data Skew can change over time, and the difference between Oracle's Cardinality estimates and the real row count can increase. That was with a Partitioned Table (partitioned by Time).

I now demonstrate the behaviour with a non-Partitioned Table, using the same data. The SALES_NP table captures Sales orders for various types of products. PROD_ID=31 represents '3.5" diskettes". (More details are available in the previous post).

I first create SALES_NP with data upto the Year 2000 :

SQL> drop table sales_np purge;

Table dropped.

SQL> create table sales_np as select * from sales_partitioned where 1=2;

Table created.

SQL> create index sales_np_prod_ndx on sales_np(prod_id);

Index created.

SQL> create index sales_np_time_ndx on sales_np(time_id);

Index created.

SQL>
SQL> -- Populate the table with data upto Year 2000
SQL> insert into sales_np select * from sales_partitioned where time_id < to_date('01-JAN-2001','DD-MON-YYYY');

659425 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','SALES_NP',estimate_percent=>100,granularity=>'ALL',degree=>4,-
> method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL>


I then verify the row counts :

SQL> -- What are the total number of Sales records by year ?
SQL> select /*+ FULL(s) PARALLEL (s 4) */ to_char(time_id,'YYYY'), count(*)
2 from sales_np s
3 group by to_char(time_id,'YYYY')
4 order by 1
5 /

TO_C COUNT(*)
---- ----------
1998 178834
1999 247945
2000 232646

SQL>
SQL> -- How many sales exist for the range 01-Jan-1998 to 31-Dec-2001 ?
SQL> select count(*)
2 from sales_np
3 where time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
4 /

COUNT(*)
----------
659425

SQL>
SQL> -- How many sales of 3.5" diskettes were made in the range 01-Jan-1998 to 31-Dec-2001 ?
SQL> select prod_id, to_char(time_id,'YYYY'), count(*)
2 from sales_np
3 where
4 prod_id=31
5 and time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
6 group by prod_id,to_char(time_id,'YYYY')
7 order by 1,2
8 /

PROD_ID TO_C COUNT(*)
---------- ---- ----------
31 1998 6602
31 1999 6586
31 2000 7568

SQL>
SQL> -- How many sales 3.5" diskettes were made in 2000 ?
SQL> select prod_id, to_char(time_id,'YYYY'), count(*)
2 from sales_np
3 where
4 prod_id=31
5 and time_id > to_date('31-DEC-1999','DD-MON-YYYY') and time_id < to_date('01-JAN-2001','DD-MON-YYYY')
6 group by prod_id,to_char(time_id,'YYYY')
7 order by 1,2
8 /

PROD_ID TO_C COUNT(*)
---------- ---- ----------
31 2000 7568

SQL>

So, the Year 2000 saw 7,568 sales orders for 3.5" diskettes.

Let's see Oracle's cardinality estimates for each of the years :

SQL> REM REM ###################################################### #################
SQL> -- Verify the Cardinality Estimates for selected years
SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-1998','DD-MON-YYYY') and time_id < to_date('01-JAN-2000','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 945744872

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7829 | 93948 | 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 7829 | 93948 | 347 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SALES_NP_PROD_NDX | 20756 | | 67 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TIME_ID" "TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("PROD_ID"=31)

16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-1999','DD-MON-YYYY') and time_id < to_date('01-JAN-2001','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 945744872

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7339 | 88068 | 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 7339 | 88068 | 347 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SALES_NP_PROD_NDX | 20756 | | 67 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID" 2 - access("PROD_ID"=31)

16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3261648362

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 228 | 40 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 19 | 228 | 40 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_NP_TIME_NDX | 602 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PROD_ID"=31)
2 - access("TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"
16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3261648362

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 228 | 28 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 19 | 228 | 28 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_NP_TIME_NDX | 401 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PROD_ID"=31)
2 - access("TIME_ID">TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"
16 rows selected.

SQL>

So we know the estimates for the years 1999 and 2000 (at 7829 and 7339 rows) aren't too far off the real row counts (6586 and 7568).

I now insert the data for the year 2001 :

SQL> -- let's insert 2001 data
SQL>
SQL> insert /*+ APPEND */ into sales_np
2 select *
3 from sales_partitioned
4 where 1=1
5 and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
6 and decode(prod_id,31,mod(cust_id,3),0)=0
7 /

257877 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> -- the number of total sales orders in 2001
SQL> select count(*)
2 from sales_np
3 where time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
4 /

COUNT(*)
----------
257877

SQL>
SQL> -- the number of sales orders for 3.5" diskettes in 2001
SQL> select count(*)
2 from sales_np
3 where time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
4 and prod_id=31
5 /

COUNT(*)
----------
811

SQL>
SQL> -- Gather Statistics
SQL> exec dbms_stats.gather_table_stats('','SALES_NP',estimate_percent=>100,granularity=>'ALL',degree=>4,-
> method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL>

So I have inserted 811 sales orders for 3.5" diskettes in the year 2001. (In the previous example, with a Partitioned Table, it was 2,352 orders in the year 2001 and 811 in the year 2002).

Let's re-check the Cardinality estimates. Remember : I did NOT change the count of rows in the years 1998 to 2000 ! They are still the same. I have only added new rows in 2001 such that we have much fewer orders for 3.5" diskettes, although the total number of rows for 2001 actually is higher than that for 2000. I have increased the skew for 3.5" diskettes -- representing significantly depressed sales for this product alone.


SQL> -- ReVerify the Cardinality Estimates by year
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-1998','DD-MON-YYYY') and time_id < to_date('01-JAN-2000','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 945744872

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5852 | 70224 | 364 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 5852 | 70224 | 364 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SALES_NP_PROD_NDX | 21567 | | 72 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TIME_ID" "TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("PROD_ID"=31)

16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-1999','DD-MON-YYYY') and time_id < to_date('01-JAN-2001','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 945744872

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5477 | 65724 | 364 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 5477 | 65724 | 364 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SALES_NP_PROD_NDX | 21567 | | 72 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID" 2 - access("PROD_ID"=31)

16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 945744872

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6076 | 72912 | 364 (1)| 00:00:05 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 6076 | 72912 | 364 (1)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | SALES_NP_PROD_NDX | 21567 | | 72 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID" 2 - access("PROD_ID"=31)

16 rows selected.

SQL>
SQL> explain plan for
2 select prod_id
3 from sales_np
4 where prod_id=31
5 and time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3261648362

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 180 | 46 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| SALES_NP | 15 | 180 | 46 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SALES_NP_TIME_NDX | 628 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("PROD_ID"=31)
2 - access("TIME_ID">TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"
16 rows selected.

SQL>


Notice how the discrepancy between Actual Row Counts and Estimated Row Counts has significantly increased -- even for earlier years were there was absolutely no change in the data. Before inserting year 2001 data, the discrepancy between Actual and Estimated for the year 2000 was only 229 (7568-7339). With 2001 data, the discrepancy increased to 2191 (7568-5477). The discrepancy for the year 2001 is now very significant --- against an actual count of 811 rows, the estimated count is 6076 rows !


Actual versus Estimated Cardinality for PROD_ID=31


Year Actual Estimated Estimated
before inserting after inserting
Year 2001 rows Year 2001 rows
1999 6586 7829 5852
2000 7568 7339 5477
2001 0 19 na
2001 inserted 811 na 6076
2002 0 19 15



Why do we see such discrepancies ?
A. Estimated Row Counts (Cardinality Estimates) are based on Column Statistics.
B. Column Statistics are at the Table level (across all the years, not for specific years).
C. As data skew changes, the estimated counts get "distributed" across the years because the estimates are averaged out across the whole table.

.
.
.

01 October, 2010

Most Popular Posts

Blogger's "Stats" feature shows that the 3 most popular posts in the past 30 days have been :
3. NLS_DATE_FORMAT : 144 pageviews

.
.
.

26 September, 2010

Data Skew changing over time --- and the Cardinality Estimate as well !

What happens if the nature of "data skew" changes over time ? For example, in the first few years of operations, you manufacture and sell 4 types of widgets ("A", "B", "C" and "D") where the volume of sales is approximately equally distributed amongst the 4 (Sales may be increasing over the years, but the distribution -- i.e. proportions -- remains the same). After say, 5 years, you add new widgets ("X", "Y" and "Z") to your product line. You notice that sales for "A" and "B" are now actually declining while sales for "Z" over the next 3 years are growing much faster than "C", "D", "X" and "Y".
What you have is changing data skew.
How does the optimizer estimate cardinality ?

Column Statistics used for a Histogram are at the *Table* level. A Histogram of the frequency of occurrences of these widgets in your sales would be computed merely as the number of occurrences across the table -- without accounting for dates. At the end of year 4, Oracle may say that "A" accounted for 25% of sales. At the end of year 8, "A" may now account for only 5% of total sales across all 8 years. And that is why the histogram would say. However, the histogram would not now (at the end of 8 years) say that "A" still accounts for 25% of sales in the first 4 years ! The information content is now "normalised" -- the metadata loses some vital information. (Of course, actually querying the table still shows you that "A" accounts for 25% of sales in the first 4 years, but the Optimizer's statistics can no longer reflect this knowledge). The Optimizer's cardinality estimates after 8 years are actually "weaker" because it has "normalised" (or "averaged out") the skew.


I've takan the "SH" schema's SALES table (this is available as one of the EXAMPLE schemas) from an Oracle 10.2 installation to demonstrate this. I take the example of 3.5inch diskettes whose sales decline. To simplify the case, I count the number of Sales Orders, not the quantities across all orders (for example, we could assume that each Sales Order is for exactly 1000 diskettes).

First, I start with SALES information for the years 1998 to 2002 :
SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL',degree=>4,-
> method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> -- What is PROD_ID 31 ?
SQL> select prod_id, prod_name
  2  from products
  3  where prod_id=31
  4  /

   PROD_ID PROD_NAME
---------- --------------------------------------------------
        31 1.44MB External 3.5" Diskette

SQL>
SQL> -- What are the range of dates in the Sales (history) table ?
SQL> select min(time_id), max(time_id)
  2  from times
  3  /

MIN(TIME_ MAX(TIME_
--------- ---------
01-JAN-98 31-DEC-02

SQL>
SQL> -- What are the total number of Sales records by year ?
SQL> select /*+ FULL(s) PARALLEL (s 4) */ to_char(time_id,'YYYY'), count(*)
  2  from sales s
  3  group by to_char(time_id,'YYYY')
  4  order by 1
  5  /

TO_C   COUNT(*)
---- ----------
1998     178834
1999     247945
2000     232646
2001     259418

SQL>
SQL> -- How many sales exist for the range 01-Jan-1998 to 31-Dec-2001 ?
SQL> select count(*)
  2  from sales
  3  where time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
  4  /

  COUNT(*)
----------
    918843

SQL>
SQL> -- How many sales of 3.5" diskettes were made in the range 01-Jan-1998 to 31-Dec-2001 ?
SQL> select prod_id, to_char(time_id,'YYYY'), count(*)
  2  from sales
  3  where
  4  prod_id=31
  5  and time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
  6  group by prod_id,to_char(time_id,'YYYY')
  7  order by 1,2
  8  /

   PROD_ID TO_C   COUNT(*)
---------- ---- ----------
        31 1998       6602
        31 1999       6586
        31 2000       7568
        31 2001       2352

SQL>
SQL> -- How many sales 3.5" diskettes were made in  2002 ?
SQL> select prod_id, to_char(time_id,'YYYY'), count(*)
  2  from sales
  3  where
  4  prod_id=31
  5  and time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
  6  group by prod_id,to_char(time_id,'YYYY')
  7  order by 1,2
  8  /

no rows selected

SQL> 

We know that gross Sales (for all products) have (generally) been increasing over the years -- from 179thousand orders in 1998 to 259thousand in 2001 (with a slight dip in the year 2000).
However, sales of 3.5" diskettes declined in 2001.
Sales for 2002 are not recorded.

We look at the Optimizer's cardinality estimates based on available statistics :
SQL> -- Verify the Cardinality Estimates by year
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-1999','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  4478 | 53736 |    22  (10)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  4478 | 53736 |    22  (10)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  4478 | 53736 |     6   (0)| 00:00:01 |     4 |     8 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  4478 | 53736 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |     4 |     8 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  4478 | 53736 |    15   (7)| 00:00:01 |     4 |     8 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  4478 | 53736 |    15   (7)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |     4 |     8 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID"              "TIME_ID">TO_DATE(' 1997-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1997-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1998','DD-MON-YYYY') and time_id < to_date('01-JAN-2000','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  6275 | 75300 |    27   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  6275 | 75300 |    27   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  6275 | 75300 |     6   (0)| 00:00:01 |     8 |    12 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  6275 | 75300 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |     8 |    12 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  6275 | 75300 |    20   (5)| 00:00:01 |     8 |    12 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  6275 | 75300 |    20   (5)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |     8 |    12 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID"              "TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1999','DD-MON-YYYY') and time_id < to_date('01-JAN-2001','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  5859 | 70308 |    25   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  5859 | 70308 |    25   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  5859 | 70308 |     6   (0)| 00:00:01 |    12 |    16 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  5859 | 70308 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |    12 |    16 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  5859 | 70308 |    18   (6)| 00:00:01 |    12 |    16 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  5859 | 70308 |    18   (6)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |    12 |    16 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  6528 | 78336 |    27   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  6528 | 78336 |    27   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  6528 | 78336 |     6   (0)| 00:00:01 |    16 |    20 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  6528 | 78336 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |    16 |    20 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  6528 | 78336 |    20   (5)| 00:00:01 |    16 |    20 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  6528 | 78336 |    20   (5)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |    16 |    20 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1295169200

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |    16 |   192 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR    |                |    16 |   192 |     4   (0)| 00:00:01 |    20 |    24 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                |    16 |   192 |     4   (0)| 00:00:01 |       |       |
|   3 |    BITMAP AND                |                |       |       |            |          |       |       |
|   4 |     BITMAP MERGE             |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX RANGE SCAN | SALES_TIME_BIX |       |       |            |          |    20 |    24 |
|*  6 |     BITMAP INDEX SINGLE VALUE| SALES_PROD_BIX |       |       |            |          |    20 |    24 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("TIME_ID">TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   6 - access("PROD_ID"=31)

20 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2002','DD-MON-YYYY') and time_id < to_date('01-JAN-2004','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2294783259

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |    16 |   192 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR          |                |    16 |   192 |     2   (0)| 00:00:01 |    24 |    28 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    16 |   192 |     2   (0)| 00:00:01 |    24 |    28 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  4 |     BITMAP INDEX RANGE SCAN        | SALES_TIME_BIX |       |       |            |          |    24 |    28 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PROD_ID"=31)
   4 - access("TIME_ID">TO_DATE(' 2002-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2004-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

18 rows selected.
SQL>
SQL> 


We can see that the estimates for PROD_ID=31 do not match reality. Particularly for 2001.

Let's now create 2002 data :
SQL> -- let's create  2002 sales data
SQL> alter index sales_time_bix modify partition sales_q1_2002 unusable;

Index altered.

SQL> alter index sales_time_bix modify partition sales_q2_2002 unusable;

Index altered.

SQL> alter index sales_time_bix modify partition sales_q3_2002 unusable;

Index altered.

SQL> alter index sales_time_bix modify partition sales_q4_2002 unusable;

Index altered.

SQL> alter index sales_prod_bix modify partition sales_q1_2002 unusable;

Index altered.

SQL> alter index sales_prod_bix modify partition sales_q2_2002 unusable;

Index altered.

SQL> alter index sales_prod_bix modify partition sales_q3_2002 unusable;

Index altered.

SQL> alter index sales_prod_bix modify partition sales_q4_2002 unusable;

Index altered.

SQL>
SQL>
SQL> insert /*+ APPEND */ into sales
  2  select prod_id, cust_id, time_id+365, channel_id, promo_id, quantity_sold, amount_sold
  3  from sales
  4  where 1=1
  5  and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
  6  and decode(prod_id,31,mod(cust_id,3),0)=0
  7  /

257877 rows created.

SQL> alter index sales_time_bix rebuild partition sales_q1_2002 ;

Index altered.

SQL> alter index sales_time_bix rebuild partition sales_q2_2002 ;

Index altered.

SQL> alter index sales_time_bix rebuild partition sales_q3_2002 ;

Index altered.

SQL> alter index sales_time_bix rebuild partition sales_q4_2002 ;

Index altered.

SQL> alter index sales_prod_bix rebuild partition sales_q1_2002 ;

Index altered.

SQL> alter index sales_prod_bix rebuild partition sales_q2_2002 ;

Index altered.

SQL> alter index sales_prod_bix rebuild partition sales_q3_2002 ;

Index altered.

SQL> alter index sales_prod_bix rebuild partition sales_q4_2002 ;

Index altered.

SQL>
SQL>
SQL> -- the number of total sales orders in 2002
SQL> select count(*)
  2  from sales
  3  where time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
  4  /

  COUNT(*)
----------
    257877

SQL>
SQL> -- the number of sales orders for 3.5" diskettes in 2002
SQL> select count(*)
  2  from sales
  3  where time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
  4  and prod_id=31
  5  /

  COUNT(*)
----------
       811

SQL> -- so we had only 811 sales orders for 3.5" diskettes
SQL>
SQL> -- Gather Statistics
SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL',degree=>4,-
> method_opt=>'FOR ALL COLUMNS SIZE 250',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed.

SQL> 


Thus, although gross sales for all products did not decline significantly in 2002, sales for 3.5" diskettes, at 811 orders, were only 1/3rd the number in 2001.

Let's revisit the Optimizer's cardinality estimates :
SQL> -- ReVerify the Cardinality Estimates by year
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1997','DD-MON-YYYY') and time_id < to_date('01-JAN-1999','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  3636 | 43632 |    22  (10)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  3636 | 43632 |    22  (10)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  3636 | 43632 |     6   (0)| 00:00:01 |     4 |     8 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  3636 | 43632 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |     4 |     8 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  3636 | 43632 |    15   (7)| 00:00:01 |     4 |     8 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  3636 | 43632 |    15   (7)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |     4 |     8 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID"              "TIME_ID">TO_DATE(' 1997-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1997-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1998','DD-MON-YYYY') and time_id < to_date('01-JAN-2000','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  5047 | 60564 |    27   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  5047 | 60564 |    27   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  5047 | 60564 |     6   (0)| 00:00:01 |     8 |    12 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  5047 | 60564 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |     8 |    12 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  5047 | 60564 |    20   (5)| 00:00:01 |     8 |    12 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  5047 | 60564 |    20   (5)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |     8 |    12 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID"              "TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1998-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2000-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-1999','DD-MON-YYYY') and time_id < to_date('01-JAN-2001','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  4755 | 57060 |    26   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  4755 | 57060 |    26   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  4755 | 57060 |     6   (0)| 00:00:01 |    12 |    16 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  4755 | 57060 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |    12 |    16 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  4755 | 57060 |    19   (6)| 00:00:01 |    12 |    16 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  4755 | 57060 |    19   (6)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |    12 |    16 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID"              "TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2000','DD-MON-YYYY') and time_id < to_date('01-JAN-2002','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  5277 | 63324 |    28   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  5277 | 63324 |    28   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  5277 | 63324 |     6   (0)| 00:00:01 |    16 |    20 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  5277 | 63324 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |    16 |    20 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  5277 | 63324 |    21   (5)| 00:00:01 |    16 |    20 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  5277 | 63324 |    21   (5)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |    16 |    20 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2001','DD-MON-YYYY') and time_id < to_date('01-JAN-2003','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2009851720

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |  5250 | 63000 |    28   (8)| 00:00:01 |       |       |
|*  1 |  VIEW                          | index$_join$_001 |  5250 | 63000 |    28   (8)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |                  |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR    |                  |  5250 | 63000 |     6   (0)| 00:00:01 |    20 |    24 |
|   4 |     BITMAP CONVERSION TO ROWIDS|                  |  5250 | 63000 |     6   (0)| 00:00:01 |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX   |       |       |            |          |    20 |    24 |
|   6 |    PARTITION RANGE ITERATOR    |                  |  5250 | 63000 |    21   (5)| 00:00:01 |    20 |    24 |
|   7 |     BITMAP CONVERSION TO ROWIDS|                  |  5250 | 63000 |    21   (5)| 00:00:01 |       |       |
|*  8 |      BITMAP INDEX RANGE SCAN   | SALES_TIME_BIX   |       |       |            |          |    20 |    24 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PROD_ID"=31 AND "TIME_ID">TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   2 - access(ROWID=ROWID)
   5 - access("PROD_ID"=31)
   8 - access("TIME_ID">TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"              2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

25 rows selected.

SQL>
SQL>
SQL> explain plan for
  2  select prod_id
  3  from sales
  4  where prod_id=31
  5  and time_id > to_date('31-DEC-2002','DD-MON-YYYY') and time_id < to_date('01-JAN-2004','DD-MON-YYYY')
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1295169200

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |    13 |   156 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR    |                |    13 |   156 |     4   (0)| 00:00:01 |    24 |    28 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                |    13 |   156 |     4   (0)| 00:00:01 |       |       |
|   3 |    BITMAP AND                |                |       |       |            |          |       |       |
|   4 |     BITMAP MERGE             |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX RANGE SCAN | SALES_TIME_BIX |       |       |            |          |    24 |    28 |
|*  6 |     BITMAP INDEX SINGLE VALUE| SALES_PROD_BIX |       |       |            |          |    24 |    28 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("TIME_ID">TO_DATE(' 2002-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TIME_ID"   6 - access("PROD_ID"=31)

20 rows selected.

SQL> 


Oracle now seems to have continued "normalising" (or "averaging out") the distribution across all the years. The discrepancy between the actual count of rows and the estimated count of rows has increased significantly.
Actual versus Estimated Cardinality for PROD_ID=31


Year            Actual  Estimated  Estimated
                before inserting   after inserting
                Year 2002 rows     Year 2002 rows

1998              6602       4478       3636   
1999              6586       6275       5047
2000              7568       5859       4755
2001              2352       6528       5277
2002                 0         16         na
2002 inserted      811         na       5250
2003                 0         16         13
                                                               


Although the number of occurrences of Sales Orders for PROD_ID=31 has declined considerably in 2001 and 2002, the Optimizer cannot adjust for the decline. The discrepancy between Actual and Estimated row counts increases over time as the skew changes.
The Cardinality estimate is computed from Column statistics (number of distinct values, selectivity, histogram) at the Table level but combined with Partition row counts without any knowledge of the occurrences of PRODUCT_ID=31 in each of the target Partitions for the year 2002.


SUGGESTED TESTS For Readers :
1. What if we had a BTree Index ?
2. What if the SALES table wasn't Partitioned ?


UPDATE : See the next post, with a Non-Partitioned Table
.
.
.