For the web development, usually, there will be a condition to let user to select the opition, e.g.
option '1': case when the variable is 1
option '2': case when the variable is 2
option '3': case when the variable is 'any' - including 1,2 and others
and in the backside,the sql could be
.... and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i');
Although this sql runs perfect as I imaged, our DBA is not happy with it. Because in that situation, he think adding filter ' and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i');'. is just wasting the oracle resource.
So, What shall I do?
Here is the solution,
replacing . and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i'); with:
... AND (:RESET_TYPE IS NULL OR DR.RESET_TYPE = :RESET_TYPE);
which means when user select 'any' , the value of parameter reset_type will be null ,so oracle
won't execute the filter 'DR.RESET_TYPE = :RESET_TYPE)' .
option '1': case when the variable is 1
option '2': case when the variable is 2
option '3': case when the variable is 'any' - including 1,2 and others
and in the backside,the sql could be
.... and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i');
Although this sql runs perfect as I imaged, our DBA is not happy with it. Because in that situation, he think adding filter ' and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i');'. is just wasting the oracle resource.
So, What shall I do?
Here is the solution,
replacing . and regexp_like(DR.RESET_TYPE,NVL(:RESET_TYPE,'[12]'),'i'); with:
... AND (:RESET_TYPE IS NULL OR DR.RESET_TYPE = :RESET_TYPE);
which means when user select 'any' , the value of parameter reset_type will be null ,so oracle
won't execute the filter 'DR.RESET_TYPE = :RESET_TYPE)' .
Comments
Post a Comment