Hello there,
I have a range-partitioned table PART_TABLE which has 10 Million records and 10 partitions having 1 million records each. Partition is done based on a Column named ID which is a sequence from 1 to 10 million.
I created another table P2_BKP (doing a select * from part_table) which has the same dataset as that of PART_TABLE except that this table is not partitioned.
Now, I run a same query on both the tables to retrieve a range of data. Precisely I am trying to read only the data present in 5 partitions of the partitioned tables which theoretically requires less reads than when done on unpartitioned tables.
Yet, the query seems to take extra time on partitioned table than when run on unpartitioned table.Any specific reason why is this the case?
Below is the query I am trying to run on both the tables and their corresponding Explain Plans.
QUERY A
=========
select * from P2_BKP where id<5000000;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6573K| 720M| 12152 (2)| 00:02:26 |
|* 1 | TABLE ACCESS FULL| P2_BKP | 6573K| 720M| 12152 (2)| 00:02:26 |
----------------------------------------------------------------------------
QUERY B
========
select * from part_table where id<5000000;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3983K| 436M| 22181 (73)| 00:04:27 | | |
| 1 | PARTITION RANGE ITERATOR| | 3983K| 436M| 22181 (73)| 00:04:27 | 1 | 5 |
|* 2 | TABLE ACCESS FULL | PART_TABLE | 3983K| 436M| 22181 (73)| 00:04:27 | 1 | 5 |
-------------------------------------------------------------------------------------------------------