Hi all,
When trying to improve the performance of my query i have created an index on a table for the column employee_number with datatype NUMBER(9).
The query that i'm using is using Bind variables, these are necessary because this query will be deployed as a rest service in Oracle Apex.
When building the query in PL/SQL developer(version 12) i declare my bind variables like this:
with
params as
(select to_number('&supplier_number') supplier_number,
to_char('&employee_numbers') employee_numbers
from dual)Since it's possible to supply more employee_numbers i have another with statement to handle those:
(
select to_number(trim(regexp_substr(params.employee_numbers, '[^,]+', 1, level))) as employee_number
from params
connect by regexp_substr(params.employee_numbers, '[^,]+', 1, level) is not null
),Now, when i go to the where statement of the actual query, i do this:
and (employee_numbers is null or xsd.employee_number in employee_numbers.employee_number)
When i execute that query it runs using the index that i created.
However, when executing that exact same query in Toad(and also executing it as a rest service) i have to declare the bind variables like this:
with
params as
(select to_number(:supplier_number) supplier_number,
to_char(:employee_numbers) employee_numbers
from dual)And when executing the exact same query, it uses completely different indexes which make the query incredibly slow.
It seems like PL/SQL developer does something with the '&variable' that toad or other ide's don't seem to do with :variable.
And even though i make sure the datatype's are correct using to_number and to_char, it reacts different.
Does anyone know what the difference is?
Many thanks in advance!
Prefixing a variable with & (ampersand) makes it a substitution variable, not a bind variable!
Clients that support substitution variables swap it for the value you supply (when enabled, usually via set define on). So the actual SQL statement changes based on the variable's value.
Bind variables begin with : (colon). The client passes the value to the variable at runtime. The SQL statement remains the same whatever the variable's value.
e.g. in SQL*Plus,
define sub_var = 'Changed'
var bind_var varchar2(30);
exec :bind_var := 'Same';
select &sub_var, :bind_var
from dual;
old 1: select &sub_var, :bind_var
new 1: select Changed, :bind_var
select Changed, :bind_var
*
ERROR at line 1:
ORA-00904: "CHANGED": invalid identifierNotice that the SQL statement is now:
select Changed, :bind_var from dual
The substitution variable is gone, replaced with the text "Changed". The bind variable remains.
You should use bind variables in your application, not substitution variables. Not all clients support substitution variables, they lead to more parsing (=> slower SQL), and are a security risk. Because the statement changes based on user input, you're at risk of SQL injection.