Dynamic Value in SQL Select Partition

Question

As the title say, is it possible to put a dynamic value in Paritition during SELECT?
I've been searching around for the answer in stack and i can't find a solution to this.

Example

SELECT
    TB1.TB_ID,
    TB1.EFF_DATE
FROM
    TABLE TABLE1 PARTITION (D20191230) TB1, TABLE2 PARTITION (D20191230) TB2
WHERE
    TB1.EFF_DATE >= TO_DATE(:p_req_date, 'yyyymmdd') 
AND 
    TB1.EFF_DATE < TO_DATE(:p_req_date, 'yyyymmdd') + 1
AND
    TB1.TB_ID = TB2.TB_ID

Is it possible to turn Partition value into

('D' + :p_req_date)

where :p_req_date will also be in yyyymmdd

I've tried concat and subquery but nothing seems to click.

Any help would be appreciated, Thank You!!

Why *do you need to address the partition explicitely*? Does not *partition pruning* work for you? — Marmite Bomber, Dec 31 '19 at 08:54
How is the table partitioned? Why not simply include the partition key in the WHERE clause and let Oracle do the job? This sounds overly complicated for something that Oracle can do automatically. — , Dec 31 '19 at 10:16
@MarmiteBomber Sorry for the long reply, because this SQL is used for daily jobs on D+(sysdate) partition to improve query speed — Karen, Jan 02 '20 at 02:28
@a_horse_with_no_name english wasnt quite my first language and i had a problem understanding your comment, do you imply that i can actually put the partition key on where? if i can that would be really helpful! — Karen, Jan 02 '20 at 02:29
@a_horse_with_no_name oh also the table is partitioned by EFF_DATE — Karen, Jan 02 '20 at 02:37
@Karen see may new answer, where I address what I thing is the *root cause* of your problem. — Marmite Bomber, Jan 02 '20 at 10:17

score 1 · Accepted Answer · answered Dec 31 '19 at 06:03

1

You can use dynamic query for it.

I have the table stu_data and it has the partition named SYS_P389, the following is the example of how to use the dynamic name of the partition using execute immediate (dynamic SQL)

SQL> SET SERVEROUT ON
SQL>
SQL> DECLARE
  2      LV_ROLL_NO   STU_DATA.ROLL_NO%TYPE;
  3      LV_P3898     VARCHAR2(100) := 'P3898';
  4  BEGIN
  5      EXECUTE IMMEDIATE 'select max(roll_no) from stu_data partition (SYS_'
  6                        || LV_P3898
  7                        || ')'
  8      INTO LV_ROLL_NO;
  9      DBMS_OUTPUT.PUT_LINE('output: ' || LV_ROLL_NO);
 10  END;
 11  /
output: 1

PL/SQL procedure successfully completed.

SQL>

Cheers!!

answered Dec 31 '19 at 06:03

Popeye

35,427
4
10
31

Dear lord and i thought this isnt possible, one more thing, this works with cursor right? @Tejash – Karen Dec 31 '19 at 06:16
And also this might sound like a stretch but is it possible without **PL/SQL**? – Karen Dec 31 '19 at 06:20
@Karen, Yes, This will work with Cursor also and Execute Immediate is part of PL/SQL, So this cannot be run without PL/SQL. – Ankit Bajpai Dec 31 '19 at 06:42
1

@Karen: the proper way to do this "dynamically" is to use the partition key in the WHERE clause and forget about the `partition` keyword, PL/SQL or dynamic SQL – Dec 31 '19 at 10:18

score 1 · Answer 2 · answered Jan 02 '20 at 10:15

You are missing the feature of partition pruning that is crutial for large partitioned tables. Check the concepts in the documentation first.

To demonstrate the partition pruning the following setup will be used

create table TABLE1 
   PARTITION BY RANGE (EFF_DATE)
   INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
( 
   PARTITION p_init VALUES LESS THAN (TO_DATE('01-01-2019', 'DD-MM-YYYY'))
)
as
select rownum tb_id, 
       date'2019-01-01' + (rownum -1) as EFF_DATE
from dual connect by level <= 365;

The table is interval partitioned with one day partitions on the column EFF_DATE. The TABLE2 is created identically.

Now let's look on the execution plan of your query, referencing the tables and not the partitions:

SELECT
    TB1.TB_ID,
    TB1.EFF_DATE
FROM TABLE1 TB1, TABLE2 TB2
WHERE
    TB1.EFF_DATE >= TO_DATE(:p_req_date, 'yyyymmdd')
AND 
    TB1.EFF_DATE < TO_DATE(:p_req_date, 'yyyymmdd') + 1
AND
    TB1.TB_ID = TB2.TB_ID;

See here how to produce the following execution plan.

Plan hash value: 3726328978

-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     2 |    32 |  3724   (1)| 00:00:01 |       |       |
|*  1 |  FILTER                    |        |       |       |            |          |       |       |
|*  2 |   HASH JOIN                |        |     2 |    32 |  3724   (1)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE ITERATOR|        |     2 |    24 |  1849   (1)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | TABLE1 |     2 |    24 |  1849   (1)| 00:00:01 |   KEY |   KEY |
|   5 |    PARTITION RANGE ALL     |        |   365 |  1460 |  1874   (1)| 00:00:01 |     1 |1048575|
|   6 |     TABLE ACCESS FULL      | TABLE2 |   365 |  1460 |  1874   (1)| 00:00:01 |     1 |1048575|
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:P_REQ_DATE,'yyyymmdd')+1>TO_DATE(:P_REQ_DATE,'yyyymmdd'))
   2 - access("TB1"."TB_ID"="TB2"."TB_ID")
   4 - filter("TB1"."EFF_DATE"<TO_DATE(:P_REQ_DATE,'yyyymmdd')+1 AND 
              "TB1"."EFF_DATE">=TO_DATE(:P_REQ_DATE,'yyyymmdd'))

The relevant part is in the columns Pstart and Pstop. You see that in the TABLE1 only one partition is accessed. KEY - KEY means that the partition is not fixed (you used bind variables), but only data from one partition will be scanned.

The problem is on table TABLE2 which is accessed fully (all partitions).

Why?

Oracle does not know that the rows with the same ID in both tables have the same effective date.

This is exect what you want to teach Oracle by passing the identical partition names in the join.

Much better way is to do in the WHERE clause by adding the predicate on EFF_DATE for the TABLE2

SELECT
    TB1.TB_ID,
    TB1.EFF_DATE
FROM TABLE1 TB1, TABLE2 TB2
WHERE
    TB1.EFF_DATE >= TO_DATE(:p_req_date, 'yyyymmdd')
AND 
    TB1.EFF_DATE < TO_DATE(:p_req_date, 'yyyymmdd') + 1
AND
    TB1.TB_ID = TB2.TB_ID
AND 
    TB2.EFF_DATE >= TO_DATE(:p_req_date, 'yyyymmdd')  /* filter for EFF_DATE in TABLE2 added */
AND 
    TB2.EFF_DATE < TO_DATE(:p_req_date, 'yyyymmdd') + 1;

Now Oracle knows that in both tables only one partition should be accessed.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     2 |    48 |  3724   (1)| 00:00:01 |       |       |
|*  1 |  FILTER                    |        |       |       |            |          |       |       |
|*  2 |   HASH JOIN                |        |     2 |    48 |  3724   (1)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE ITERATOR|        |     2 |    24 |  1849   (1)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | TABLE1 |     2 |    24 |  1849   (1)| 00:00:01 |   KEY |   KEY |
|   5 |    PARTITION RANGE ITERATOR|        |     2 |    24 |  1874   (1)| 00:00:01 |   KEY |   KEY |
|*  6 |     TABLE ACCESS FULL      | TABLE2 |     2 |    24 |  1874   (1)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:P_REQ_DATE,'yyyymmdd')+1>TO_DATE(:P_REQ_DATE,'yyyymmdd'))
   2 - access("TB1"."TB_ID"="TB2"."TB_ID")
   4 - filter("TB1"."EFF_DATE"<TO_DATE(:P_REQ_DATE,'yyyymmdd')+1 AND 
              "TB1"."EFF_DATE">=TO_DATE(:P_REQ_DATE,'yyyymmdd'))
   6 - filter("TB2"."EFF_DATE"<TO_DATE(:P_REQ_DATE,'yyyymmdd')+1 AND 
              "TB2"."EFF_DATE">=TO_DATE(:P_REQ_DATE,'yyyymmdd'))

So you solved your task without a need of dynamic SQL, which is always the prefered way to go.

First of all dear lord, i thought this wasnt necessary. But then i implemented your idea and give it a go, it cuts thru 160.000 cost (<- F10 on SQL Developer) all the way to 83.000. While this isnt the answer of my question. This definitely helped with my case. Thank you so much for this!! — Karen, Jan 02 '20 at 10:48

score 0 · Answer 3 · answered Dec 31 '19 at 14:10

In general you should put condition into WHERE clause instead of using the PARTITION clause.

Anyway, in case you really like to specify the partition then I would recommend the PARTITION FOR clause in order to be independent from partition name which is random by default. You should also prefer the ANSI join syntax.

sqlstr := 
'SELECT TB1.TB_ID, TB1.EFF_DATE
FROM TABLE1 PARTITION FOR (DATE '''||TO_CHAR(p_req_date, 'yyyy-mm-dd')||''') TB1
   JOIN TABLE2 PARTITION FOR (DATE '''||TO_CHAR(p_req_date, 'yyyy-mm-dd')||''') TB2 ON TB1.TB_ID = TB2.TB_ID
WHERE TB1.EFF_DATE >= :aDate
   AND TB1.EFF_DATE < :bDate';

OPEN cur FOR sqlstr USING p_req_date, p_req_date + 1;

I hope data type of p_req_date is a DATE (or TIMESTAMP) rather than a string. Otherwise you should convert it to proper DATE or TIMESTAMP value.

Dynamic Value in SQL Select Partition

3 Answers3