Going to Oracle from MySQL

Question

I'm starting a new semester and for my class "relational databases" they are using Oracle. Now I'm experienced in using MySQL and was wondering what the main differences are between Oracle and MySQL.

So I have a couple of questions.

1. What are the fundamental differences in using the database?
2. What are some of the differences in SQL syntax?
3. What tools would you recommend to use with Oracle?
4. Are there any caveats that I should be aware of coming from MySQL?
5. Where can you find good resources on Oracle?
6. Is Oracle tied to a platform? (windows, red hat)

Your assistance is appreciated.

Answer 1

In order:

1. Oracle is "industrial grade" in every respect. A bit of a beast, really. It makes MySql look like a toy. Not hating, just comparison.

2. Oracle in general sticks with ANSI style syntax. Most things are the same, at least for beginners. There is more than ANSI, but for a typical college course it is functionality which likely would not be covered anyway.

3. SQL Developer (by Oracle!). It's easy, free, and runs on any Java-capable platform.

4. Not much. Just the typical things like "don't accidentally run a delete statement without a where clause."

5. It depends how deep you want to go. Oracle actually has pretty good/readable documentation.

6. Oracle is on MANY platforms. They're basically a platform themselves. Windows, OS X, Linux, AIX,... I think there's even a Solaris version.

Check out www.oracle.com/technology to get to the good stuff and avoid the marketing crap.

Answer 2

1.) Depends on what you mean by using. If you mean more at a DBA level, then it will somewhat depend on what storage engine you're using with MySQL.

If you've mainly used MyISAM, then you'll have things like redo and undo storage segments to get used to when using Oracle as it is a fully transactional database.

Oracle also has a different and IMHO a more mature stored procedure language (PL/SQL) than that of MySQL. Oracle also incorporates more features than MySQL. Some examples are: function based indexes, index organized tables, better partitioning options, etc. (Some of these are on the table as future enhancements for MySQL)

2.) General SQL syntax for the most part is the same. Though the There are some differences in the way you directly call stored procedures. (i.e. call vs exec/execute)

3.) Oracle SQL Developer isn't bad and it's free. If you have a budget, Toad for Oracle is really nice. A web based application (PHP) that is useful is PHP MyAdmin, and the out of the box Oracle Enterprise Manager service is very useful.

4.) ---

5.) I would recommend highly the Oracle documentation itself and My Oracle Support (formely MetaLink) http://metalink.oracle.com The Oracle Technology Network is also a good resource

6.) Oracle runs on all if not most major platforms including Linux, Windows, and AIX.

Answer 3

4) mysql syntax on some things is forgiving, whereas oracle won't let you do it. things like quoting numbers and omitting columns from GROUP BY clauses. in general, nothing that will lead to incorrect results, just annoying errors that you might not understand at first.

Answer 4

Point Two It may have changed since I last used it, but you wouldn't put select 6 * 7; as a statement - you'd use the pseudo-table dual ie select 6 * 7 from dual; Also, if you're used to using the Limit clause in Mysql you may need to be careful in using rownum in Oracle. You should also investigate date formats and functions in Oracle, which differ to those in Mysql.