I have a table that is partitioned daily. The column used to range partition is defined as "timestamp with local time zone".
I have a huge query that joins various other partitioned tables with the same column being partitioned and non-partitioned tables. The join condidtions are various key values. When I saw the explain plan on this query it basically used all the partitions the Pstart was 1 and Pstop 428, for all partitioned tables.
So to figure out why this was happening I tried to focus the study on the main big table that was partitioned.
The first query explained is the normal way that we run the query. Since the queries can come from different timezone, we convert them to GMT and then send the query to the DB. Hence we convert and cast to the column datatype being timestamp with local time zone.
I'm not sure if these are using partition elimination since only "KEY" is displaed on the Pstop, but Pstart says 1 which means I assume that all partitons are still being accessed. My partition starts from November 1.
Could someone help me understand this and help me figure out why these queries and probably I can debug my other main query why its not doing the partition elimination. Thanks.
Using 10.2.0.1 and Redhat 5
SQL> select min(optime), max(optime) from part_test;
MIN(OPTIME) MAX(OPTIME)
--------------------------------------------------------------------------- --------------------------------------
30-Nov-2007 00:15:18 06-Dec-2007 22:54:15
SQL> explain plan for select * from part_test where optime <=
2 CAST
3 (TO_TIMESTAMP_TZ
4 ('01-Dec-2007 00:00:00 +00:00',
5 'DD-Mon-YYYY HH24:MI:SS TZH:TZM'
6 ) AS TIMESTAMP WITH LOCAL TIME ZONE)
7 ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 773178615
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 702K| 108M| 17054 (14)| 00:01:14 | | |
| 1 | PARTITION RANGE ITERATOR| | 702K| 108M| 17054 (14)| 00:01:14 | 1 | KEY |
|* 2 | TABLE ACCESS FULL | part_test | 702K| 108M| 17054 (14)| 00:01:14 | 1 | KEY |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
2 - filter("OPTIME"<=CAST(TO_TIMESTAMP_TZ('01-Dec-2007 00:00:00 +00:00',:B1) AS TIMESTAMP WITH
LOCAL TIME ZONE))
15 rows selected.
SQL> explain plan for select * from part_test where optime <= to_date('01-Dec-2007 00:00:00','DD-Mon-YYYY HH24:MI:SS')
2 ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 773178615
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 702K| 108M| 16279 (9)| 00:01:10 | | |
| 1 | PARTITION RANGE ITERATOR| | 702K| 108M| 16279 (9)| 00:01:10 | 1 | KEY |
|* 2 | TABLE ACCESS FULL | part_test | 702K| 108M| 16279 (9)| 00:01:10 | 1 | KEY |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
2 - filter("OPTIME"<=TIMESTAMP'2007-12-01 00:00:00')
14 rows selected.
Message was edited by:
user484402
null