PLS_INTEGER... I don't think so!
Once got called to a meeting where an engineer was going to teach us some revolutionary techniques of programming in PLSQL, that were going to push the performance of our systems to levels never seen before.
Of the many things showed in that meeting, very few made any sense to me at the time, but, when management is keen on taking such demonstrations of "knowledge" at face value, and not listening to other parties, means that everyone had to comply and not complain.
Well... 10 years past and nothing of what was pushed in that meeting ever proved to be any efective.
This is the first of many things that were pushed then, which I now explain why they are not relevant and should not be adopted in development teams.
PLS_INTEGER instead on NUMBER or anchored type variables.
Tom Kite said it all here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5340131040835
Why didn't it make sense then, and why doesn't it make sense now?
several reasons occur to me:
- Format conversion
- Too little gain and only on very few operations.
If you think carefully, a variable holds a value to be used in code. Most of the times, you need it as a parameter to some system function/procedure, which uses other code to perform actions, and so on.
Any SQL function doesn't recognize PLS_INTEGER as it is PLSQL type. As a result, somewhere along the processing, there will be a format conversion, and the operations occur over the converted value.
PLS_INTEGER is very effective when the code using it performs intensive integer arithmetic within the format range, otherwise you are better off using SQL native formats.
For the case of the company where the meeting took place, we are talking about monetary operations, working with decimal numbers, percentages, and so on, where there is no use for integer operations. A little investigation would have saved a lot of time and money in useless changes, that not only did not improve performance, but also made the code harder to maintain.
References:
https://blogs.oracle.com/oraclemagazine/working-with-numbers-in-plsql
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5340131040835
Of the many things showed in that meeting, very few made any sense to me at the time, but, when management is keen on taking such demonstrations of "knowledge" at face value, and not listening to other parties, means that everyone had to comply and not complain.
Well... 10 years past and nothing of what was pushed in that meeting ever proved to be any efective.
This is the first of many things that were pushed then, which I now explain why they are not relevant and should not be adopted in development teams.
PLS_INTEGER instead on NUMBER or anchored type variables.
Tom Kite said it all here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5340131040835
Why didn't it make sense then, and why doesn't it make sense now?
several reasons occur to me:
- Format conversion
- Too little gain and only on very few operations.
If you think carefully, a variable holds a value to be used in code. Most of the times, you need it as a parameter to some system function/procedure, which uses other code to perform actions, and so on.
Any SQL function doesn't recognize PLS_INTEGER as it is PLSQL type. As a result, somewhere along the processing, there will be a format conversion, and the operations occur over the converted value.
PLS_INTEGER is very effective when the code using it performs intensive integer arithmetic within the format range, otherwise you are better off using SQL native formats.
For the case of the company where the meeting took place, we are talking about monetary operations, working with decimal numbers, percentages, and so on, where there is no use for integer operations. A little investigation would have saved a lot of time and money in useless changes, that not only did not improve performance, but also made the code harder to maintain.
References:
https://blogs.oracle.com/oraclemagazine/working-with-numbers-in-plsql
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5340131040835
Comments
Post a Comment