Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In language like C or Java, there are things like functions/methods to modularize and abstract stuff.

What is there in SQL, specifically for Oracle?

I've seen people talking about using views to reduce the complexity in various blogs but there is only so much that can be done with a view. (For example, a bind variable cannot be passed as a runtime parameter to a view). In tools like Oracle Reports the query sometimes gets very awkward running several hundreds of lines.

I was wondering what sort of techniques people use to manage the complexity of their SQL? What are the equivalents of functions and methods in SQL to keep SQL from becoming extremely complex?

share|improve this question
    
What would you bind into a view that you couldn't accomplish with the constructs a SELECT from that view would make available? –  Blrfl Oct 10 '11 at 11:24
    
@Blrfl - When you create a view from query it indexes that view so that you can query off of it more efficiently than reruning the query every time. –  Chad Oct 10 '11 at 13:59
    
@Chad: My point was that even if you were able to bind a parameter at query time, the database still has to do the same things as if you used the expression to SELECT something (e.g., SELECT a+bound_value, b+bound_value ... or in a WHERE clause (e.g., ... WHERE a = bound_value). Whether you're using plain, indexed or materialized views, the database can't index against an expression that can't be calculated when the index needs to be updated. –  Blrfl Oct 10 '11 at 15:10
    
@blrfl but you can create the view with the aggregate results and joins already completed. If you have a query that takes 30 seconds or more to run try putting it into a view It may run quite abit faster. –  Chad Oct 10 '11 at 15:16
    
Having a materialized view doesn't change the fact that the database can't evaluate unknowns (i.e., things that would be bound) until the query which provides them actually happens. The OP asked about making bound variables part of views; I just asked what benefit binding a value into a view would provide that a SELECT from that view can't. –  Blrfl Oct 10 '11 at 15:56
add comment

migrated from stackoverflow.com Oct 10 '11 at 10:13

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

I have found common table expressions (cte's) helpful for both reducing the size and complexity as well as making the query more readable. Temporary results (temp tables and so forth) can be a mixed blessing, as can user defined functions. They can certainly make the code more readable, but may have a performance impact (not that the impact is always negative, but it is something to watch out for).

share|improve this answer
    
Thanks. FWIW, I found that CTEs are under a different name in Oracle viz., WITH clause. –  toddlermenot Oct 11 '11 at 7:49
add comment

PL-SQL supports user defined functions. You can use this to re-use some common logic with queries. However, there are significant performance penalties for doing this, as UDFs hide indexes from the query. Most of the time, you're better off writing out your query logic again.

Another option is to use a computed column. This let's you do some (not all) of the same things as a scalar user defined function, but is more index-friendly.

share|improve this answer
    
Thanks for the tip about computed column. Haven't heard that one before. –  toddlermenot Oct 11 '11 at 7:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.