This content has been marked as final. Show 5 replies
2) I could hard code this query in the procedure, but there are other tables where the partition name will change daily, therefore I will need something dynamic to change theIf you add a filter predicate on the column the partitioning is based on then Oracle will automatically targets the correct partition, and you won't need dynamic SQL, only a bind variable.
partition name in the query.
Assuming "p_20130101" contains rows with trunc(DT) = 01/01/2013, then :
SELECT /*+ PARALLEL(big_tab,4) */ ABC||','||XYZ FROM big_table big_tab WHERE big_tab.dt >= trunc(p_date) AND big_tab.dt < trunc(p_date) + 1
Thanks for the feedback.
As you higlighted, "Assuming "p_20130101" contains rows with trunc(DT) = 01/01/2013" -- unfortunately, the data looks as follows "01/01/2013 19:52:52".
Is there any other way to overcome this issue, cause I noticed that as soon as I add a filter predicate to the "date" field, the optimizer chooses a "PARTITION RANGE ITERATOR" or "PARTITION RANGE ALL" depending if I use ">" or "=" comparison operator.
I created another procedure to address the dynamic sql requirement - which works, but it fails to run in parallel, unlike how the explicit "CURSOR(Select....)" does.
v_sql:='SELECT /*+ PARALLEL(big_tab,4) */ ABC||'',''||XYZ
FROM big_table partition(p_20130101) big_tab';
open l_cursor for v_sql;
for rowsx in (Select * from table(parallel_dump(l_cursor, 'utl_file_mots60', 'CEMS_UTL_DUMP', 14)))
I'm open for any ideas and would even a consider taking a new approach, if needed, without sacrificing performance.
Thanks for the feedback. I'm not experiencing compilation errors, but trying to build a dynamic query and pass it to the ref cursor.
I'm considering building a procedure at runtime with the "SQL" query, as a workaround. The procedure "create syntax" including the "SQL query excluding the partition name" will be stored in a table, and I will change the partition name as I build the procedure.
SELECT * from TABLE(parallel_export(CURSOR(SELECT /*+ PARALLEL(big_tab,4) */ ABC||','||XYZ
FROM big_table partition(p_20130101) big_tab),
where (p_20130101) will change to (p_sysdate).
This way the procedure will contain the "hard coded" partition name in the CURSOR.
If anyone has other suggestion, please share your thoughts.