Variables, constants vs of hard-coded values?

[DinoVaught]

In an Oracle stored procedure. . . Which of these would execute faster? Or does it even matter?



UPDATE worklog wrklog
SET flag = 'N',
WHERE ((wrklog.id = 'A123456') AND (wrklog.flag = 'Y'));

or. . .

p_id IN VARCHAR2

FLAG_N CONSTANT VARCHAR2(1) := 'N';
FLAG_Y CONSTANT VARCHAR2(1) := 'Y';

UPDATE worklog wrklog
SET flag = FLAG_N,
WHERE ((wrklog.id = p_id) AND (wrklog.flag = FLAG_Y));

Where the latter uses variables and constants instead of hard-coded values

.

[bashish]

I suppose it the first one which will run faster than second.

That is because, in second the executor of the stored procedure has to fetch the value from variables and then bind it and execute it.

Though if you see the time diff, u won;t find any - I think so.


Ashish

[davide++]

Hi all,

I'm not sure, but I think the first statement is faster than the second, because Oracle haven't to manage the constant values into UPDATE (as Ashish said). But probably the improvement of performance is very small, so in my opinion is better a well-written statement as the first (using constant) rather than a hard-coded statement as the second.