Thanks for the question, sharib.

Asked: October 06, 2024 - 7:31 pm UTC

Last updated: October 21, 2024 - 12:48 pm UTC

Version: 19c

Viewed 50K+ times! This question is

You Asked

Dear Tom,

I am trying to create a SQL_MACRO, which will accept the subpartition name and return the SELECT statement with that particular subpartition. Idea is to use this SQL_MACRO despite the view, where we cannot dynamically build the subpartition clause on the base table, and hence get the performance boost.

I tried something like:-

CREATE OR REPLACE FUNCTION test_subp(i_subpart VARCHAR2)
RETURN VARCHAR2 SQL_MACRO IS
RETURN 'SELECT * FROM trade SUBPARTITION(i_subpart)';

END;

and when I fire something like below, I get the message, subpartition doesn't exist(although the subpartition is there). Seems like SQL_MACRO is not able to produce the desired string, or this kind of feature is not supported by SQL_MACRO !

SELECT * from test_subp('p_2024_10_01_jan');

Kindly assist to resolve my issue with your expert suggestion.

Thank you,
Sharib

and Connor said...

Closest I could come up with is

SQL> create table t ( x int , y int )
  2  partition by list ( x )
  3  ( partition p1 values (1), partition p2 values (2)
  4  );

Table created.

SQL>
SQL> insert into t values (1,10);

1 row created.

SQL> insert into t values (1,11);

1 row created.

SQL> insert into t values (2,20);

1 row created.

SQL> insert into t values (2,21);

1 row created.

SQL>
SQL> CREATE OR REPLACE FUNCTION test_subp(i_val int)
  2  RETURN VARCHAR2 SQL_MACRO IS
  3    pname varchar2(100);
  4  begin
  5    select partition_name
  6    into   pname
  7    from   user_tab_partitions
  8    where  table_name = 'T'
  9    and   partition_position = i_val;
 10
 11    RETURN 'SELECT * FROM t PARTITION ('||pname||')';
 12  END;
 13  /

Function created.

SQL> sho err
No errors.
SQL>
SQL> SELECT * from test_subp(1);

         X          Y
---------- ----------
         1         10
         1         11

which is not fantastic. I will ask around internally to see what other options are possible

There is the WITH variant, but I'm sure that really progresses you any further

SQL> CREATE OR REPLACE FUNCTION test_subp(p_tab dbms_tf.table_t)
  2  RETURN VARCHAR2 SQL_MACRO IS
  3  begin
  4    RETURN 'SELECT * FROM p_tab';
  5  END;
  6  /

Function created.

SQL>
SQL>
SQL> with xx as
  2  ( select * from t partition (p1 ))
  3  select * from test_subp(xx);

         X          Y
---------- ----------
         1         10
         1         11

Addenda: Chris also came up with this variant

create or replace function get_partition ( pname dbms_tf.columns_t )
return clob sql_macro as
begin
  return ' select * from t
    partition ( ' || pname(1) || ' ) ';
end;
/

select count(*)
from   get_partition ( columns ( p1 ) );

  COUNT(*)
----------
        10

Rating

(7 ratings)

Comments

partitioned extend sytax

Rajeshwaran, Jeyabal, October 07, 2024 - 8:23 am UTC

Just to make the things bit more intresting, checked the feasibility of partition extend syntax, which doesn't work with macros.

demo@FREEPDB1> create table t
  2  partition by range( created )
  3  interval ( numtoyminterval(1,'month') )
  4  subpartition by hash( object_id )
  5  subpartition template(
  6     subpartition sp1
  7    , subpartition sp2 )
  8  ( partition p1 values less than ( to_date('01-jan-2024','dd-mon-yyyy')) )
  9  as
 10  select * from all_objects ;

Table created.

demo@FREEPDB1> create or replace function foo
  2  ( p1 date
  3    , p2 number )
  4  return varchar2
  5  sql_macro as
  6  begin
  7     return ' select count(*) from t SUBPARTITION for (p1,p2) ';
  8  end;
  9  /

Function created.

demo@FREEPDB1>
demo@FREEPDB1> create or replace function foo2
  2  ( p1 date )
  3  return varchar2
  4  sql_macro as
  5  begin
  6     return ' select count(*) from t PARTITION for (p1) ';
  7  end;
  8  /

Function created.

demo@FREEPDB1> select count(*) from t SUBPARTITION for ( to_date('15-jul-2024','dd-mon-yyyy'),55);

  COUNT(*)
----------
     29139

demo@FREEPDB1> select count(*) from t PARTITION for ( to_date('15-jul-2024','dd-mon-yyyy'));

  COUNT(*)
----------
     58002

demo@FREEPDB1> select * from foo(  to_date('15-jul-2024','dd-mon-yyyy'),55 );
select * from foo(  to_date('15-jul-2024','dd-mon-yyyy'),55 )
                                                            *
ERROR at line 1:
ORA-14173: illegal subpartition-extended table name syntax
Help: https://docs.oracle.com/error-help/db/ora-14173/


demo@FREEPDB1> select * from foo2(  to_date('15-jul-2024','dd-mon-yyyy') );
select * from foo2(  to_date('15-jul-2024','dd-mon-yyyy') )
                                                          *
ERROR at line 1:
ORA-14108: illegal partition-extended table name syntax
Help: https://docs.oracle.com/error-help/db/ora-14108/


demo@FREEPDB1>

October 09, 2024 - 2:16 pm UTC

Yes, this unsupported. I've raised an ER for this.

Sharib, October 07, 2024 - 9:23 am UTC

Thank you for the prompt response. Would be interested in knowing further implementation. Thanks once again Connor!

Sharib, October 07, 2024 - 1:23 pm UTC

Thank you Connor, Chris, Rajeshwaran for the prompt reply.

Applied the solution suggested by Connor, and woww it works !

CREATE OR REPLACE EDITIONABLE FUNCTION v_exp_ap_pos_c (i_part_pos int,i_sub_part_pos int )return varchar2 SQL_MACRO is
subp varchar2(100) ;
begin
select SUBPARTITION_NAME
into subp
from user_tab_subpartitions
where partition_position =i_part_pos
and subpartition_position = i_sub_part_pos
and table_name = 'T_TRADE';

return 'select * from t_trade SUBPARTITION(' ||subp|| ')';

END;
/

select *
from v_exp_ap_pos_c(15,9);

Then I get the output !!

However, instead of passing the parameter as "INT", if I directly try to pass the string(subpartition_name) then it fails with the following error message.

Hence seems like it is not considering the string parameter here !!

CREATE OR REPLACE EDITIONABLE FUNCTION v_exp_ap_pos_c1 (i_subpart varchar2 )return varchar2 SQL_MACRO is
subp varchar2(100) ;
begin
select SUBPARTITION_NAME
into subp
from user_tab_subpartitions
where subpartition_name =i_subpart
and table_name = 'T_TRADE';

return 'select * from t_trade SUBPARTITION(' ||subp|| ')';

END;
/

select *
from v_exp_ap_pos_c1('P_2024_10_03_ABC');

No output.

ORA-62565: The SQL Macro method failed with error(s).
ORA-01403: no data found
ORA-06512: at "V_EXP_AP_POS_C1", line 6
ORA-06512: at line 5
62565. 00000 - "The %s method failed with error(s). %s"
*Cause: This method of polymorphic table function had error(s) during SQL compilation.
*Action: Look at the errors and resolve them.
Error at Line: 33 Column: 6

Would be waiting to hear more on this..

October 09, 2024 - 2:15 pm UTC

The value of string parameters is always null in the body of macros. So in the query the expression subpartition_name = i_subpart becomes subpartition_name = null. This is why you get the NDF.

Running queries within macros is risky in any case. They're only run when the database parses the macro. This can lead to unexpected results if the query result changes and the macro is not parsed again.

Sharib, October 10, 2024 - 6:36 pm UTC

Thanks Connor, for clarifying why SQL_MACRO isn't functioning with String as an input parameter. So is it like SQL_MACRO is designed only to take in INT, DATE ?

October 11, 2024 - 12:38 pm UTC

I'm Chris :)

So is it like SQL_MACRO is designed only to take in INT, DATE ?

No! String parameters are valid. The problem is how you're using these parameters in the macro.

You should use parameters to SQL macros as placeholders in the return string. Keep any logic in the body of a macro to a minimum (ideally none). This applies to parameters of all data types.

The body of the macro is only executed when parsing the query. This means you're not guaranteed to see data changes if you have queries inside it.

For example, this counts rows in T inside the macro and is the return template. Notice that adding more rows does not affect the value of C fetched inside the function:

create table t ( c1 int );

create or replace function f ( p int ) 
return clob sql_macro as 
  c int;
begin 
  select count(*) into c from t
  where  c1 = p;
  return ' 
    select ' || c || ' as c, count(*) 
    from   t where c1 = p '; 
end;
/

insert into t values ( 1 );
commit;

select * from f ( 1 );

         C   COUNT(*)
---------- ----------
         1          1

insert into t values ( 1 );
commit;

select * from f ( 1 );

         C   COUNT(*)
---------- ----------
         1          2

Sharib, October 12, 2024 - 6:33 pm UTC

My bad... I always get confused between you and Connor :)

So, by using the parameters to SQL MACRO as placeholder, why we can't form a return SQL which has the subpartition clause, which accepts subpartition name as the input parameter !

As mentioned previously, the SQL MACRO function takes in the subpartition name and then returns the SQL with the SUBPARTITION clause.

The idea behind using SQL MACRO here is, instead of defining a simple static view(based on the tables which are subparitioned), we make it dynamic, so that the view hits a particular subparition depending upon the input parameter. And hence is more performant.

October 14, 2024 - 6:08 pm UTC

This relates to a more general restriction of the (SUB)PARTITION FOR syntax - it doesn't accept bind variables:

var part_key number;
exec :part_key := 1;
select * from t
  partition for ( :part_key );
-- ORA-14763: Unable to resolve FOR VALUES clause to a partition number

As Connor updated in the post, you can use the COLUMNS pseudo column to pass values to the (SUB)PARTITION clause:

create or replace function get_partition ( pname dbms_tf.columns_t )
return clob sql_macro as
begin
  return ' select * from t
    partition ( ' || pname(1) || ' ) ';
end;
/

select count(*)
from   get_partition ( columns ( p1 ) );

  COUNT(*)
----------
        10

Again, you can't use bind variables in the PARTITION clause - they must be identifiers. This is a general restriction for this clause that affects how you can use it in SQL macros.

Johan, October 14, 2024 - 12:34 pm UTC

In general, you should ideally not provide partition or sub partition names in the query. Just use the partition keys in the where clause and partition elimination will be done automaticall.y

October 14, 2024 - 6:09 pm UTC

Yep - that's another way to approach this.

macro and json

Rajeshwaran, Jeyabal, October 19, 2024 - 2:41 pm UTC

Team - on the other note, can't Macro and JSON go together?

this doesn't work.

demo@FREEPDB1> create or replace function foo(
  2     p_inputs in json )
  3  return varchar2
  4  sql_macro as
  5     l_sql long :=' select * from emp where rownum =1 ';
  6     l_json_arr json_array_t;
  7  begin
  8     l_json_arr := json_array_t.load(p_inputs);
  9     dbms_output.put_line( l_json_arr.get_Size );
 10
 11     return l_sql;
 12  end;
 13  /

Function created.

demo@FREEPDB1>
demo@FREEPDB1> select * from foo(p_inputs=>json(:b1) ) ;
select * from foo(p_inputs=>json(:b1) )
              *
ERROR at line 1:
ORA-62565: The SQL Macro method failed with error(s).
ORA-40834: invalid input to JSON parse or load function
ORA-06512: at "SYS.JDOM_T", line 36
ORA-06512: at "SYS.JSON_ARRAY_T", line 179
ORA-06512: at "DEMO.FOO", line 8
ORA-06512: at line 5
Help: https://docs.oracle.com/error-help/db/ora-62565/


demo@FREEPDB1> print b1

B1
-------------------------------------------------------------------
 [
        {'cname' : 'value', 'operator' : 'value', 'value' : '55'}
        , {'cname' : 'value', 'operator' : 'value', 'value' : '55'}
        , {'cname' : 'value', 'operator' : 'value', 'value' : '55'}
                ]

instead of macro if i got a normal function (in the above) it works.

demo@FREEPDB1> create or replace function foo(
  2     p_inputs in json )
  3  return varchar2
  4  as
  5     l_sql long :=' select * from emp where rownum =1 ';
  6     l_json_arr json_array_t;
  7  begin
  8     l_json_arr := json_array_t.load(p_inputs);
  9     dbms_output.put_line( l_json_arr.get_Size );
 10
 11     return l_sql;
 12  end;
 13  /

Function created.

demo@FREEPDB1> select foo(p_inputs=>json(:b1) ) ;

FOO(P_INPUTS=>JSON(:B1))
---------------------------------------
 select * from emp where rownum =1

3
demo@FREEPDB1>

October 21, 2024 - 12:48 pm UTC

This is nothing to do with JSON. It's because it's a bind variable. This has no value when the macro is resolved.

The same happens with other data types. For example, notice that both the JSON and INT parameters output null:

create or replace function foo(
   p1 in json, p2 int
) return varchar2 sql_macro as
   l_sql long :=' select * from emp where rownum =1 ';

   l_json_arr json_array_t;
begin
   dbms_output.put_line ( 'JSON = ' || json_serialize ( p1 ) );
   dbms_output.put_line ( 'INT = ' || p2 );

   return l_sql;
end;
/

var b1 varchar2(100);
var b2 varchar2(100);

exec :b1 := '{a1 : "v1" }';
exec :b2 := 2;

select * from foo( json(:b1), :b2  ) ;
no rows selected
JSON = 
INT =

You really shouldn't be calling functions like json_array_t.load(p_inputs) inside the macro body.

I'll repeat what I said above:

Keep any logic in the body of a macro to a minimum (ideally none)

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

partitioned extend sytax

macro and json

More to Explore

VLDB

VLDB

Forming a subpartition clause inside SQL_MACRO

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

partitioned extend sytax

macro and json

More to Explore

VLDB

VLDB