In the Oracle 12c documentation SQL Language Reference, for the SQL SELECT statement syntax, under the partition_extension_clause section, there is this statement:
For PARTITION or SUBPARTITION, specify the name or key value of the partition or subpartition within table from which you want to retrieve data.
For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the WHERE clause that restricts the retrieval to one or more partitions of table. Oracle Database will interpret the condition and fetch data from only those partitions. It is not possible to formulate such a WHERE condition for hash-partitioned data.I have searched high and low for more information on the syntax for using the partition_extension_clause in the WHERE clause but I have not been able to find any. I've also tried it in SQL and can't figure it out there either.
If I create a table like this:
CREATE TABLE PART_TABLE (THEPK NUMBER, THEDATA NUMBER)
PARTITION BY RANGE (THEDATA) INTERVAL (1)
(PARTITION P1 VALUES LESS THAN (1))
then insert data into it
INSERT INTO PART_TABLE VALUES (1,1);
INSERT INTO PART_TABLE VALUES (2,2);
INSERT INTO PART_TABLE VALUES (3,3);
COMMIT;
I then want to retrieve the data in partition 2. I don't know the name of the partition. I can do it like this:
select * from part_table partition for (2);
And I get the right data.
But according to the documentation there is a way to write a where clause condition to achieve the same thing. What is the syntax for doing that?
Thanks!
Mark
You're overthinking this ;)
All that means is if you include the partition column(s) in your where clause, the database only can filter out the unneeded partitions.
For example, if you search for those rows where thedata = 2, you only need to hit the last partition.
Which is exactly what happens:
set serveroutput off
select * from part_table
where thedata = 2;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +PARTITION'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from part_table where thedata = 2
Plan hash value: 2596771497
-------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE| | 3 | 3 |
| 2 | TABLE ACCESS FULL | PART_TABLE | 3 | 3 |
-------------------------------------------------------------Notice that the Pstart and Pstop columns have the same value. This tells you Oracle Database only accessed one partition.