Hello everyone,
I have some tables which are created long before me and I don't have chance to change them. basically those tables are partitioned on a YEAR column (and sub partitioned on MONTH). now while I was testing some codes, I realized that to get MAX Year data has a huge cost. here is an example:
create table tmp1 ( order_year number(4), order_id number(10), order_data varchar2(800))
partition by range(order_year) interval (1)
(partition p1 values less than (2000)) ;
insert into tmp1
select 2010 + trunc(level/10000), level, 'test data' from dual connect by level < 80000;
commit;
here is simple select:
select max(order_year) from tmp1;
result: 2017
when we check execution plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
EXPLAIN PLAN FOR
select max(order_year) from tmp1;
Plan hash value: 2051121752
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2074 (1)| 00:00:25 | | |
| 1 | PARTITION RANGE ALL | | 1 | 13 | | |1048575| 1 |
| 2 | SORT AGGREGATE | | 1 | 13 | | | | |
| 3 | TABLE ACCESS STORAGE FULL| TMP1 | 83313 | 1057K| 2074 (1)| 00:00:25 |1048575| 1 |
----------------------------------------------------------------------------------------------------
here is 2074 cost which is very high for this table. Only thing that I realized, starting partition is the highest partition and end partition is 1 but cost is too high don't you think? Oracle reads whole table to find that data. Also getting MAX(ORDER_DATA) which is not a part of partition key or any index has same cost. why?
also after refreshing statistics, it is even higher:
exec dbms_Stats.gather_Table_stats(user,'TMP1');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
EXPLAIN PLAN FOR
select max(order_year) from tmp1;
Plan hash value: 2051121752
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2184 (1)| 00:00:27 | | |
| 1 | PARTITION RANGE ALL | | 1 | 4 | | |1048575| 1 |
| 2 | SORT AGGREGATE | | 1 | 4 | | | | |
| 3 | TABLE ACCESS STORAGE FULL| TMP1 | 79999 | 312K| 2184 (1)| 00:00:27 |1048575| 1 |
----------------------------------------------------------------------------------------------------
thanks.
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production