Fantastic! As useful to DBA as programmers. Thank you!
Tom
· 4 years ago
It drives me crazy the way people write SQL code. I think a lot of people let the tools write the code for them, then they don't bother to clean it up and format it. I did an article on the subject a few years ago that may be helpful to some: http://www.communitymx.com/content/article.cfm?...>
Patrick Barel
· 4 years ago
The good thing about formatting your sql is that the Oracle parser will hash a lot of statements to the same value, which results in faster execution. An even better thing than correctly formatting your SQL is writing no SQL at all. This can be accomplished by encapsulating all the needed SQL into packages which the other programs use. I know this is not always possible, especially with 'legacy' code, but if you are writing new programs it's always good to think about it.
Eddie Awad
· 4 years ago
Patrick, just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes, unless you (or someone else) have a proof otherwise.
Moreover, if you encapsulate all the needed SQL into packages, you still need to write SQL (in the package), don't you?
Sandy Mamoli
· 4 years ago
I can't believe I didn't know those tools existed :-) Great!
I prefer sqlReview as it allows for command line use though.
Wilfred
· 4 years ago
Formatting a SQL statement can have an impact on performance. If two statements are the same but formatted diferently, they will produce a different hash value. This means they won't be able to share the same shared SQL area.
The exception to the rule is when the paremeter CURSOR_SHARING is set to SIMILAR or FORCE. CURSOR_SHARING=FORCE was introduced in Oracle 8.1.6, whereas FORCE was introduced in version 9.
Just to be clear, when I commented above that "just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes" I meant that, given the following:
select * from employees where first_name = 'Joe'
Does formatting the above to:
SELECT * FROM employees WHERE first_name = 'Joe'
Make it run faster or impact performance?
It may or it may not. But adding an index on first_name will (if the optimizer chooses so).
My point was to not to rely on formatting to "tune" your SQL.
However, as Wilfred said, the two queries may be different as far as Oracle's shared SQL area is concerned.
Sharpe
· 3 years ago
It seems unnecessary to remove the hard line breaks from the unformatted SQL before formatting it, this free online formatter
I put my cursor at the beginning of the second line (just before the "y") and hit the backspace on my keyboard. I did this for every line until the whole query is on one line before I applied the formatting. That's what I meant by "remove the hard line breaks".
guidomarcel
· 3 years ago
Hi Eddie, thank you very much for this nice review of the sqlinform site.
Normally, the hard-coded linebreaks should be removed automatically. Which operating system are you using?
Regards GuidoMarcel
Eddie Awad
· 3 years ago
Hi Guido,
You've done a great job with SQLinform. Thank you.
I have just tried formatting the SQL text mentioned in the post above. It does indeed remove the hard line breaks and format the SQL beautifully, but it looks like the line breaks are being replaced with blanks. For example:
<pre> mi n(cols) </pre>
becomes
<pre>mi n(cols)</pre>
which of course is not valid SQL.
By the way, I'm currently using Win XP Pro.
Thanks again for this helpful utility.
DBA
· 2 years ago
To: Eddie Awad
I used SQL Review from www.dbainfopower.com and it it formats broken words properly (I formatted output from v$sqltext )
Weston Clark
· 2 years ago
Has anyone posted some open source Java to format SQL statements? I am building a tool that supports our commercial product, and I am extracting queries from the database and would like to format them. I've written something rudimentary, but I'd like to avoid the trouble of writing a good one.
GuidoMarcel
· 2 years ago
Hi Eddie, finally i put into SQLinForm an option to remove linebreaks before formatting as you suggested. Thanks for this suggestion GuidoMarcel
All Comments
Fantastic! As useful to DBA as programmers. Thank you!
It drives me crazy the way people write SQL code. I think a lot of people let the tools write the code for them, then they don't bother to clean it up and format it. I did an article on the subject a few years ago that may be helpful to some: http://www.communitymx.com/content/article.cfm?...>
The good thing about formatting your sql is that the Oracle parser will hash a lot of statements to the same value, which results in faster execution.
An even better thing than correctly formatting your SQL is writing no SQL at all. This can be accomplished by encapsulating all the needed SQL into packages which the other programs use.
I know this is not always possible, especially with 'legacy' code, but if you are writing new programs it's always good to think about it.
Patrick, just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes, unless you (or someone else) have a proof otherwise.
Moreover, if you encapsulate all the needed SQL into packages, you still need to write SQL (in the package), don't you?
I can't believe I didn't know those tools existed :-) Great!
I prefer sqlReview as it allows for command line use though.
Formatting a SQL statement can have an impact on performance. If two statements are the same but formatted diferently, they will produce a different hash value. This means they won't be able to share the same shared SQL area.
The exception to the rule is when the paremeter CURSOR_SHARING is set to SIMILAR or FORCE. CURSOR_SHARING=FORCE was introduced in Oracle 8.1.6, whereas FORCE was introduced in version 9.
See http://download-uk.oracle.com/docs/cd/B19306_01... for more info.
Just to be clear, when I commented above that "just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes" I meant that, given the following:
select * from employees where first_name = 'Joe'
Does formatting the above to:
SELECT *
FROM employees
WHERE first_name = 'Joe'
Make it run faster or impact performance?
It may or it may not. But adding an index on first_name will (if the optimizer chooses so).
My point was to not to rely on formatting to "tune" your SQL.
However, as Wilfred said, the two queries may be different as far as Oracle's shared SQL area is concerned.
It seems unnecessary to remove the hard line breaks from the unformatted SQL before formatting it, this free online formatter
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
it has a option to remove linebreak before beautify automatically, it works fine.
"Note that I had to remove the hard line breaks from the unformatted SQL before formatting it."
How?
Allen,
for example, in the following:
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.t
ype#,i.flags,i.property,i.pctfree$,i.initrans,i.ma
I put my cursor at the beginning of the second line (just before the "y") and hit the backspace on my keyboard. I did this for every line until the whole query is on one line before I applied the formatting. That's what I meant by "remove the hard line breaks".
Hi Eddie,
thank you very much for this nice review of the sqlinform site.
Normally, the hard-coded linebreaks should be removed automatically. Which operating system are you using?
Regards
GuidoMarcel
Hi Guido,
You've done a great job with SQLinform. Thank you.
I have just tried formatting the SQL text mentioned in the post above. It does indeed remove the hard line breaks and format the SQL beautifully, but it looks like the line breaks are being replaced with blanks. For example:
<pre>
mi
n(cols)
</pre>
becomes
<pre>mi n(cols)</pre>
which of course is not valid SQL.
By the way, I'm currently using Win XP Pro.
Thanks again for this helpful utility.
To: Eddie Awad
I used SQL Review from www.dbainfopower.com and it it formats broken words properly (I formatted output from v$sqltext )
Has anyone posted some open source Java to format SQL statements? I am building a tool that supports our commercial product, and I am extracting queries from the database and would like to format them. I've written something rudimentary, but I'd like to avoid the trouble of writing a good one.
Hi Eddie,
finally i put into SQLinForm an option to remove linebreaks before formatting as you suggested.
Thanks for this suggestion
GuidoMarcel
from some time there is new formatting project on the web . You can find it on
(beta). The goals are make multi platform sql formatter with editable database of "keywords".
K Rosebrook