and when they are used by Optimizer.
We can collect stats for a particular partition or for complete table.
Depending on the query the optimizer uses either one of the statistics.
Lets see some scenario.
1. When Partition level statistics are used?
When we query a partition table which would hit a particular partition.
Or In other words Partition level statistics are used when explain plan of the query would have explicit Start and Stop keys AND Start Key = Stop Keys.
Only in this scenario Partition level stats are used.
explain plan for
select * from PART_TAB
where hire_date = to_date('1-Jan-2013','dd-Mon-yyyy');
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 11 | 3 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | PART_TAB | 1 | 11 | 3 (0)| 00:00:01 | 2 | 2 |
2. When Table level statistics are used?
When we query a partition which span over a range of partition (more than one partition), then
table level stats are used.
In explain plan if you see the partition start and stop keys which are not same OR if its says KEY then table partitioned are used.
Example:
select * from PART_TAB
where hire_date >= to_date('1-Jan-2013','dd-Mon-yyyy');
| 0 | SELECT STATEMENT | | 9 | 99 | 6 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 9 | 99 | 6 (0)| 00:00:01 | 2 | 4 |
| 2 | TABLE ACCESS FULL | PART_TAB | 9 | 99 | 6 (0)| 00:00:01 | 2 | 4 |
In the above scenario table level stats are used.
More Info below:
For large tables we can make use of Incremental Statistics.
More information below:
No comments:
Post a Comment