How to Write “Good” SQL
January 17th, 2006 By David Aldridge
The opinion of one person (me) here.
9 Responses to “How to Write “Good” SQL”
Leave a Reply
You must be logged in to post a comment.
OraQA
Oracle Question and Answer
- Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...
Oracle News
Comments RSS feed
January 17th, 2006 at 11:17 am
I agree to your views on this topic. I do believe that SQL should be able to perform at it’s best. But also, there is a thin line between writing a top-notch efficient SQL and compromizing a lil bit on performance.
Can we also talk about that?
January 17th, 2006 at 12:03 pm
Of course …
January 17th, 2006 at 2:44 pm
I think we can go to an extent on optimizing sql. following are the steps that i try to follow. Apart from the following, query optimization has a lot to do with client requirement especially in case of transactional databases. (believe it or not)
[Not in any particular order]
1. A reasonable normalization of the logical database design.
2. Use Efficient Index Design
3. some queries are resource-intensive by nature. keep in mind that they r not in-efficient. SQL, by nature, is also set-oriented.
4. Make changes to optimizer_mode, optimizer_index_cost_adj and optimizer_index_caching
5. Use the dbms_stats package to import specialized statistics. Ones which are geared to the current processing mode.
6. Use Oracle Materialized Views to pre-aggregate and pre-summarize data to reduce the amount of run-time table joins. For low-update databases, you can also pre-join tables together to improve processing speed.
well…gotta go.. at work…will add some more to the list whenever possible.
ANY THOUGHTS DAVID?
KD
January 17th, 2006 at 3:06 pm
I’d agree that there are some prerequisites to good SQL performance — for example that the design of the database be competently implemented, that appropriate indexes are in place, that you understand and make the best use of the optimizer.
I guess that point 3 is “Know when to stop tuning”.
I’d be wary of directing people to materialized views as a performance enhancer at an early stage in their development experience. IMHO SQL proficiency comes first.
January 17th, 2006 at 4:55 pm
I agree,
SQL proficiency comes first. but one must know or find out a perfect time to shift gears from step 3 to step 6.
waht sya yuo?
Kd
January 17th, 2006 at 5:43 pm
I’d say that you would have to know that, but it isn’t within the intended scope of the article.
January 18th, 2006 at 5:15 am
True…
A question for u..
_________________________
SELECT *
FROM
Budget_Table
WHERE
SCEN_ID IN (’FIN’, ‘ACT’) AND STAT_CD = ‘ACT’ AND LED_ID ‘EB_RC’
AND
Budget_Table.ROW_TMSTMP
January 18th, 2006 at 6:02 am
I don’t know what he question is here, KD.
January 18th, 2006 at 6:17 am
KP,
I think that this thread should be about general advice, not specific problems — I’d suggest you go to a forum with the prob. Sorry,
DA.