Using Semicolons in Oracle SQL Statements

Question

Disclaimer: I just started work on a new contract, which forced me to switch from MSSQL to Oracle. So I am a complete newcomer to Oracle and probably bring many bad MSSQL practices with me. Okay, on to the question...

According to an answer in this thread, semicolons are bad and should be avoided. I realized this firsthand after spending hours trying to figure out why my queries were running so terribly slow in SQL Developer. After removing the semicolon from the end of a complex query, it finished in a matter of seconds (compared to me just giving up on it after several minutes, which was the case before removing the semicolon). This behavior startled me, and a Google search didn't turn up much as to when and why I should or should not use semicolons in my SQL statements. Can anyone clear this up with a brief explanation?

And if you have any links to online resources for poor developers moving from MSSQL to Oracle, then I'd greatly appreciate it!

Thanks!

Answer 1

It's not the semicolon. Rerunning the same query meant that the rows were already cached, so you got them back much faster.

Answer 2

Having worked with Oracle as a developer from PowerBuilder, Java, .NET and TOAD for 10+ years this sounds strange.

I would suggest using explain plan to find what is happening and making sure the database is healthy, with proper statistics and indexes.

From earlier versions of Oracle I remember that using rule based optimization could lead to these kind of performance problems, but lately cost based optimization and statistics has made these kind of problems a thing of the past.

Answer 3

Here is a book that will help tremendously SQL in a Nutshell by Kevin and Daniel Kline

This has the syntax for both Oracle and SQl server so you can easily see how to convert things to Oracle syntax if you know how to write in t-SQL.

Answer 4

I am comming from 5+ years MS SQL Experience and 4+ years of Oracle Development. I know you will hate lot ORACLE features specially in SQL ;) but take it easy ORACLE is really powerfull DBMS. Eventough from lot of perspectives I prefer MSSQL over ORACLE but that's different topic.

As for your issue :

Semicolon is just a statement separator.

SQL developer is using java and OCI so you might have different issues ( I am just gessing something can be wrong).

If you feal something is not running right I advice you to get that query and run it in SQLPLUS instead of Visual Query Tools because it will give you the right feeling.

Good luck with Oracle Development.

Visit this: