请教分区的问题：分区在查询语句中需要指定吗？

lovelypp

有一个表，因数据量巨大，遂按月分区，将每个月的数据hash到一个分区中。
问题是：
　　在查询指定时间的数据时，查询语句可以不指定分区吗？oracle 会自动到指定的分区中提取数据吗？

例如：
select name, invoice from tbl_trade where trade_time >= '2005-05-13 00:00:00' and trade_time <= '2005-09-03 23:59:59'orace 会足够智能吗？(自动从5、6、7、8、9这5个表分区中提取数据)
还是要在该select语句中明确指定从这5个分区中提取数据？

numenhuang

在查询语句中不需要指定特定分区，当然也可以指定。

Nengbing Tao

The focument says : "Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns."  

So if you partitioned your table use range partitions, Oracle should prune the partitions.


By the way, you may use "between  ... and " in your SQL "select name, invoice from tbl_trade where trade_time >= '2005-05-13 00:00:00' and trade_time <= '2005-09-03 23:59:59'"


Hope this helps!

Nengbing



Quote from : http://download-east.oracle.com/ ... rpart.htm#sthref207


Oracle&reg; Database Data Warehousing Guide
10g Release 2 (10.2)



Partition Pruning
Partition pruning is an essential performance feature for data warehouses. In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. This enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement. Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns.

Partition pruning dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and resource utilization. If you partition the index and table on different columns (with a global partitioned index), partition pruning also eliminates index partitions even when the partitions of the underlying table cannot be eliminated.

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile-time, with the information about the partitions accessed beforehand while dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand. A sample scenario for static pruning would be a SQL statement containing a WHERE condition with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE condition.

Partition pruning affects the statistics of the objects where pruning will occur and will therefore also affect the execution plan of a statement.

Information that can be Used for Partition Pruning
Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns.

On composite partitioned objects, Oracle can prune at both the range partition level and at the hash or list subpartition level using the relevant predicates. Examine the table sales_range_hash created earlier in this chapter, which is partitioned by range on the column s_salesdate and subpartitioned by hash on the column s_productid, and consider the following example:

SELECT * FROM sales_range_hash
WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))
  AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;

Oracle uses the predicate on the partitioning columns to perform partition pruning as follows:

When using range partitioning, Oracle accesses only partitions sal99q2 and sal99q3, representing the partitions for the third and fourth quarters of 1999.

When using hash subpartitioning, Oracle accesses only the one subpartition in each partition that stores the rows with s_productid=1200. The mapping between the subpartition and the predicate is calculated based on Oracle's internal hash distribution function.

How to Identify Whether Partition Pruning has been Used
Whether Oracle uses partition pruning or not is reflected in the execution plan of a statement, either in the plan table for the EXPLAIN PLAN statement or in the shared SQL area.

The partition pruning information is reflected in the plan columns PARTITION_START and PARTITION_STOP. In the case of serial statements, the pruning information is also reflected in the OPERATION and OPTIONS columns.

See Oracle Database Performance Tuning Guide for more information about EXPLAIN PLAN and how to interpret it.

Static Partition Pruning
For a number of cases, Oracle determines the partitions to be accessed at compile time. This happens when the predicates on the partitioning columns use a range predicate. In addition, the predicates must only use constants so that Oracle can determine the start and stop partition numbers at compile time. When this happens, the actual partition numbers show up in the partition start partition stop columns of the explain plan. The following is an example:

SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3971874201
----------------------------------------------------------------------------------------------
| Id | Operation              | Name  | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |       | 673  | 19517 | 27      (| 00:00:01 |       |       |
|  1 |  PARTITION RANGE SINGLE|       | 673  | 19517 | 27      (| 00:00:01 | 17    | 17    |
|* 2 |   TABLE ACCESS FULL    | SALES | 673  | 19517 | 27      (| 00:00:01 | 17    | 17    |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

This plan shows that Oracle accesses partition number 17, as shown in the PSTART and PSTOP columns.