First, I create a partition table and I generate random data with the DBMS_RANDOM package (PL/SQL - DBMS_RANDOM to generate random number, string and date).
gerardnico@orcl>create table t
2 (
3 collection_year int,
4 data varchar2(25)
5 )
6 PARTITION BY RANGE (COLLECTION_YEAR) (
7 PARTITION PART_99 VALUES LESS THAN (2000),
8 PARTITION PART_00 VALUES LESS THAN (2001),
9 PARTITION PART_01 VALUES LESS THAN (2002),
10 PARTITION PART_02 VALUES LESS THAN (2003),
11 PARTITION the_rest VALUES LESS THAN (MAXVALUE)
12 );
Table created.
gerardnico@orcl>declare
2 year_random_value VARCHAR2(4) := NULL;
3 begin
4 for i in 1 .. 1000
5 loop
6 year_random_value := ROUND(DBMS_RANDOM.VALUE(1999,2004));
7 insert into t values (year_random_value, 'data ' || year_random_value);
8 end loop;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
gerardnico@orcl>select collection_year, count(*) from t group by collection_year;
COLLECTION_YEAR COUNT(*)
=============== ==========
1999 107
2000 213
2001 183
2002 217
2004 92
2003 188
6 rows selected.
gerardnico@orcl>delete from plan_table;
0 rows deleted.
gerardnico@orcl>explain plan for
2 select * from t where collection_year = 2002;
Explained.
gerardnico@orcl>@?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
=====================================================================================================
Plan hash value: 2931986080
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 217 | 5859 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 217 | 5859 | 3 (0)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | T | 217 | 5859 | 3 (0)| 00:00:01 | 4 | 4 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COLLECTION_YEAR"=2002)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
We see with Oracle Database - Explain Plan :
The optimizer is guessing this information since we did not analyze the table
With Oracle Database - Autotrace, we can see this ouptut :
gerardnico@orcl>set autotrace traceonly explain
gerardnico@orcl>select * from t where collection_year = 2002;
Execution Plan
==========================================================
Plan hash value: 2931986080
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 217 | 5859 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 217 | 5859 | 3 (0)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | T | 217 | 5859 | 3 (0)| 00:00:01 | 4 | 4 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COLLECTION_YEAR"=2002)
Note
-----
- dynamic sampling used for this statement
Identically the same but be careful (I use the database version 10.2) and with a version 9.2, the partition information is missing.