Requires Free Membership to View
Yes, there is a cost in execution time when you call functions like NVL and UPPER, but the cost of fetching the data in the first place is likely to be much greater. So if you want to make your application faster, work on how the data is accessed, and use functions like NVL and UPPER with a clear conscience.
The main cause of slow applications in my experience is reading data from disk. Make sure there are indexes in the right places and that statistics are up to date. If your view uses "UPPER (full_name)," and especially if this computed value is later used in WHERE or ORDER BY clauses, consider making a function-based index on UPPER (full_name) in the base table, or adding an upper_full_name column (which can be populated by a trigger), or even making the view a materialized view.
Those last suggestions show how the cost at SELECT time can be reduced by increasing the cost in other areas (such as the disk space needed to store additional columns and indexes and the time needed to maintain them whenever you INSERT or modify data). Views often help to minimize another cost: developer time. If you're writing a query that involves joining tables A, B and C, you might save time by using an existing view that already joins them. The trouble is, that view may also include data from tables D, E and F. All those extra joins involve costly disk access. Create a new view that includes only the tables you need.
Another major bottleneck is network transmission time. Server-side processing can greatly reduce this. If there is any data processing or selecting
being done on the client, see if some of it can be shifted back to the database through user-defined functions or row-level-security policies. (This has nothing to do with views and NVL, but it's important enough to mention here.)
This was first published in December 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation