You Asked
Hi,
I've created a simple example of what I'm trying to do (below). Basically, I'm wondering if I can tell Oracle to treat &var1 as a bind variable so that a profile can be attached to this SQL statement. Is that possible? If so, can you guide me on how to do that?
I'm assuming that the optimizer will see this statement as "select count(*) from schema.table partition (P1)" if the partition is P1. But, let me know if I'm incorrect on that as well.
declare
n_row_count number;
begin
execute immediate'
select count(*)
from schema.table partition (&var1)'
into n_row_count;
dbms_output.put_line(n_row_count);
end;
Thanks!
Brian
and Connor said...
Yes, "&" is not SQL as such, but a *SQLPlus* facility, and its a straight text substituion, so by the time the database sees it, it is already replace with "P1".
I don't think a profile really is appropriate here because
select count(*) from tab partition (p1)
is inherently different to
select count(*) from tab partition (p2)
because they are physically different segemnts. It is not much different to
select count(*) from my_table
select count(*) from some_other_table
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment