I have partitioned my table on daily basis.
TABLE NAME MY_TABLE
COLUMN NAME IN_TIME TIMESTAMP
I want to fetch the rows for the last 2 days partition.
I am using the below query.
SELECT * FROM MY_TABLE PARTITION FOR (TO_DATE('17-DEC-2017','DD-MON-YYYY'))
UNION
SELECT * FROM MY_TABLE PARTITION FOR (TO_DATE('18-DEC-2017','DD-MON-YYYY'))
I am trying to set date using prepared statement
preparedStatement.setString(1, "17-DEC-2017");
preparedStatement.setString(2, "18-DEC-2017");
But I get the exception "Caused by: java.sql.SQLException: ORA-14763: Unable to resolve FOR VALUES clause to a partition number"
Please suggest any better ways to do this.
Bind variables work for values but cannot be used to dynamically select an object, such as a table, view, or partition. Use the bind variable in a condition, and hope for partition pruning, or create a string for each query.
The bind variable limitation makes sense when you think about it. Parsing a query can be hard work - checking security, building an execution plan, etc. Using bind variables lets Oracle re-use most of the work when the next query comes in with only a different literal. But if the table name is different then it has to throw out all that work and completely re-parse the statement. So there's no benefit to bind variables in that situation.
With partitions it seems like there could be a benefit. Partitions are just parts of tables. The privileges don't change, so it seems like Oracle could potentially save some work by allowing bind variables there. However there are some objects that could be different for partitions. For example, it's possible to create an index that only exists on some of the partitions. In that case, an execution plan for one partition may not work for another.
(You could still make a case that bind variable partition names could be useful. Oracle has some dynamic execution plan features, like FILTER operations and adaptive re-optimization. So it could create a smart plan that adapts to different partitions. But it doesn't.)
Luckily partition pruning usually works just as well as specifying the partition value. Changing a query to this should run just as fast:
select * from my_table where some_date > trunc(:date_2_days_ago);
But using partitions implies retrieving a large percent of rows from the table. In that case, the query parse time might be irrelevant. If a query takes a minute to process, does it really matter if query parsing time takes 0.02 seconds instead of 0.01 seconds? If that's the case then the hard-coded SQL statement will work just as well.
Expanding GurV's comment. If you analyze your table and just use a regular where clause, you will achieve your desired result and your code will not be dependent on the physical partition structure and you'll reduce complexity.
Here is an example. Start by setting up your situation:
SQL> create table my_table
2 ( id integer
3 , in_time timestamp
4 )
5 partition by range (in_time) interval (interval '1' day)
6 ( partition empty values less than (timestamp '2017-01-01 00:00:00')
7 )
8 /
Tabel is aangemaakt.
SQL> insert into my_table
2 select rownum
3 , timestamp '2017-12-16 00:00:00' + numtodsinterval(dbms_random.value * 4,'day')
4 from dual
5 connect by level <= 20
6 /
20 rijen zijn aangemaakt.
SQL> exec dbms_stats.gather_table_stats(user,'my_table')
PL/SQL-procedure is geslaagd.
SQL> select * from my_table
2 /
ID IN_TIME
---------- ---------------------------------------------------------------------------
1 16-12-2017 16:01:58,394131
3 16-12-2017 11:20:52,900366
13 16-12-2017 05:09:02,822579
17 16-12-2017 22:25:01,376019
19 16-12-2017 05:04:57,256665
6 17-12-2017 00:03:17,346513
9 17-12-2017 03:30:38,802184
10 17-12-2017 22:38:55,227404
16 17-12-2017 04:24:45,611941
4 18-12-2017 14:58:42,373178
5 18-12-2017 05:53:20,329375
7 18-12-2017 19:45:22,642099
8 18-12-2017 22:08:19,232150
20 18-12-2017 07:48:00,259104
2 19-12-2017 02:00:59,745124
11 19-12-2017 16:35:24,682363
12 19-12-2017 19:51:38,389568
14 19-12-2017 10:09:45,821531
15 19-12-2017 23:22:56,745163
18 19-12-2017 20:21:31,664647
20 rijen zijn geselecteerd.
So a dummy first partition and four "real" partition"s:
SQL> select partition_name
2 , high_value
3 , num_rows
4 , last_analyzed
5 from user_tab_partitions
6 where table_name = 'MY_TABLE'
7 /
PARTITION_NAME HIGH_VALUE NUM_ROWS LAST_ANALYZED
----------------- --------------------------------- ---------- -------------------
EMPTY TIMESTAMP' 2017-01-01 00:00:00' 0 19-12-2017 09:46:23
SYS_P2752 TIMESTAMP' 2017-12-17 00:00:00' 5 19-12-2017 09:46:23
SYS_P2753 TIMESTAMP' 2017-12-20 00:00:00' 6 19-12-2017 09:46:23
SYS_P2754 TIMESTAMP' 2017-12-19 00:00:00' 5 19-12-2017 09:46:23
SYS_P2755 TIMESTAMP' 2017-12-18 00:00:00' 4 19-12-2017 09:46:23
5 rijen zijn geselecteerd.
Now your query explained (PS: do you really need your result set sorted? if not, use UNION ALL instead of UNION):
SQL> set serveroutput off
SQL> alter session set statistics_level = all
2 /
Sessie is gewijzigd.
SQL> SELECT * FROM MY_TABLE PARTITION FOR (TO_DATE('17-DEC-2017','DD-MON-YYYY'))
2 UNION
3 SELECT * FROM MY_TABLE PARTITION FOR (TO_DATE('18-DEC-2017','DD-MON-YYYY'))
4 /
ID IN_TIME
---------- ---------------------------------------------------------------------------
4 18-12-2017 14:58:42,373178
5 18-12-2017 05:53:20,329375
6 17-12-2017 00:03:17,346513
7 18-12-2017 19:45:22,642099
8 18-12-2017 22:08:19,232150
9 17-12-2017 03:30:38,802184
10 17-12-2017 22:38:55,227404
16 17-12-2017 04:24:45,611941
20 18-12-2017 07:48:00,259104
9 rijen zijn geselecteerd.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID 41102hfzfq92x, child number 0
-------------------------------------
SELECT * FROM MY_TABLE PARTITION FOR
(TO_DATE('17-DEC-2017','DD-MON-YYYY')) UNION SELECT * FROM MY_TABLE
PARTITION FOR (TO_DATE('18-DEC-2017','DD-MON-YYYY'))
Plan hash value: 3775556890
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 74 | | | |
| 1 | SORT UNIQUE | | 1 | 9 | 9 |00:00:00.01 | 74 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | 9 |00:00:00.01 | 74 | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 4 | 4 |00:00:00.01 | 37 | | | |
| 4 | TABLE ACCESS FULL | MY_TABLE | 1 | 4 | 4 |00:00:00.01 | 37 | | | |
| 5 | PARTITION RANGE SINGLE| | 1 | 5 | 5 |00:00:00.01 | 37 | | | |
| 6 | TABLE ACCESS FULL | MY_TABLE | 1 | 5 | 5 |00:00:00.01 | 37 | | | |
--------------------------------------------------------------------------------------------------------------------------
20 rijen zijn geselecteerd.
And here is the alternative. Please note that this one also used approximately the same amount of buffer gets, and that it also uses two full partition scans (see Starts: 2)
SQL> select *
2 from my_table
3 where in_time >= timestamp '2017-12-17 00:00:00'
4 and in_time < timestamp '2017-12-19 00:00:00'
5 /
ID IN_TIME
---------- ---------------------------------------------------------------------------
6 17-12-2017 00:03:17,346513
9 17-12-2017 03:30:38,802184
10 17-12-2017 22:38:55,227404
16 17-12-2017 04:24:45,611941
4 18-12-2017 14:58:42,373178
5 18-12-2017 05:53:20,329375
7 18-12-2017 19:45:22,642099
8 18-12-2017 22:08:19,232150
20 18-12-2017 07:48:00,259104
9 rijen zijn geselecteerd.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 9bbraqwrk3pb2, child number 0
-------------------------------------
select * from my_table where in_time >= timestamp '2017-12-17
00:00:00' and in_time < timestamp '2017-12-19 00:00:00'
Plan hash value: 3786094972
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 75 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 12 | 9 |00:00:00.01 | 75 |
|* 2 | TABLE ACCESS FULL | MY_TABLE | 2 | 12 | 9 |00:00:00.01 | 75 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("IN_TIME"<TIMESTAMP' 2017-12-19 00:00:00.000000000' AND
"IN_TIME">=TIMESTAMP' 2017-12-17 00:00:00.000000000'))
21 rijen zijn geselecteerd.
