50 things to know about converting MySQL to Oracle _oracle

In addition, some of the differences they have listed are quite meaningful, some may be due to their own MySQL DBA identity, the understanding of Oracle some deviation, some are dine suspicion. But the overall combination of the Baron Schwartz List gives you a general idea of what the similarities and differences between Oracle and MySQL are all about.
Robert Treat
1. Some people say there is no limit to Oracle, in fact they may be saying that Oracle does not support limit syntax. However, it can be implemented rownum virtual columns, but it will be even more troublesome.
2. Oracle does not support offsets (offset) syntax.
3. Oracle's replication capabilities may be more robust than MySQL, but are also more difficult to configure.
4. For multiple master-type systems, most users want you to use a more complex and expensive Oracle RAC (although NDB may be more robust).
5. Oracle's partitioning function is very robust, but it is not built and needs to be paid specifically for (the partitioning feature is built into the Enterprise Edition).
6. On Linux/unix, Oracle is not as convenient as MySQL, and many Linux/unix distributions will default to their own MySQL. (My understanding and translation of this point may be biased)
7. INSERT ... The on DUPLICATE KEY update syntax is no longer available, but you need to learn the merge syntax that is more complex (but conforms to the SQL standard).
8. Oracle's role architecture differs from MySQL in that it is no longer possible to use root roles to complete all work. (The SYS user still has the same permissions as root, but it is not recommended for security purposes).
9. Role accounts are associated with specific schemas (and vice versa), similar to the database concepts in MySQL databases. (role is not entirely associated with schemas, and the system has some fixed role, which contains part of the defined permission set (privilege set), and can then customize some of the new role).
10. In fact, discarding all known settings about connection Access, Oracle uses a new set of systems to handle connection access.
11. Full-Text search is supported, but the syntax is completely different.
Oracle's documentation is very rich, but if Oracle's professional services are required, you need to have metalink access rights.
13. It is difficult to find relevant support information in a non-database community (for example, a PHP forum or web site Developer's blog)
14. Self-augmentation can be achieved by trigger and sequence objects.
15. A large number of daily familiar show commands will no longer be available, and to gain system information requires you to learn Oracle's data dictionary (or information Schema) and to learn more about Oracle Dynamic performance.
MySQL adds several non-standard extensions to the information schema that will be difficult to find in Oracle.
17. In order to manage the transfer of production systems and non production systems, you need to understand Oracle's licensing rules in depth.
18. In general, Oracle's numeric type is simpler, and if you do need a n,x numeric type similar to MySQL, you need to implement it yourself, or by making different precision (number ()).
19. In Oracle, tables can grow indefinitely, but in most cases this is recommended for fine-grained management through Tablespace (tablespace).
Oracle does not support alter TABLE ADD COLUMN before| After, that is, do not support the location of the field you set. (Some of our previous applications have strict requirements for field order, but theoretically this is a problem with business design, mostly because it uses SELECT * or Insert table values to work with data, not in Select, Specifies the list of field names to be processed in the insert.
21. If you are accustomed to managing the database through a graphical interface (GUI), you will certainly like Oracle, but if you prefer to use a client similar to the MySQL command line, you may be disappointed with the Sql*plus Client tool (a custom process is needed sql* Plus is good for you.
Oracle's data checksum is more stringent than MySQL, and a fuzzy rule that relies on MySQL may cause the application to run. MySQL accepts "0000-00-00″" as a date type value is a typical example.
23. Although Oracle's Pl/sql function is stronger, it does not support the standard PSM language to write stored procedures, so you may have to learn its non-standard syntax.
Oracle does not support the enum data type, only by using a text-based check constraint or by creating a Foreign key association table.
Some of the more cryptic table types for MySQL (for example, blackhole,csv) do not find the corresponding functionality in Oracle. (It is not clear what type of Blockhole is, but Oracle's external table (external table) does support CSV format, which also supports compression/encryption and other processing of this file in the 11g version.)
The GROUP BY statement in Oracle must be determined, and it requires that all columns appearing in the select list must be included in the GROUP BY clause.
The DMP file exported by Oracle's EXP command cannot be manually modified as a MySQL dump file.
Oracle's underlying implementation has changed significantly, requiring you to learn about Undo and redo segment, archiving, and DBWR processes. (Personally, the main advantage of Oracle relative to other databases may be its Undo/redo design.)
Oracle is not an open-source software, so you can't patch/optimize/fix/implement your own stuff on it.
Robert Hodges
. Oracle's query optimization is a job that requires expert intervention. Its optimizer is much more mature than MySQL, which means that the query plan is also more difficult to explain. If you have a big application, be prepared to hire a man who knows how to do it effectively.
The efficient operation of Oracle's cost-based optimizer requires accurate statistics. For a changed table, you need to collect statistics for it periodically. The bulk data loading process also needs to be performed from time to estimate/compute during processing Statistics command to collect statistics to achieve satisfactory performance.
32. In Oracle, creating a connection is a costly operation. Well-performing Oracle applications tend to use connection pooling to minimize the overhead of landing.
Oracle applications need to apply prepared statement for considerable performance. If your application does not use prepared statement, you need to adjust the application. This is standard programming practice for applying Oracle databases. (The prepared statement here should be more about using binding variables).
34. Application needs to close the result set cursor, otherwise it will soon encounter the famous "ORA-01000 Too many open cursors" error. In Oracle, this is considered a user error and requires you to adjust your code.
35. Long queries on busy systems may encounter "ORA-01555 Snapshot too old" errors. You can eliminate this error by adjusting the size of the redo segment (sometimes by adjusting the application), but you need to focus on that. (There is a problem with the expression of redo segments here, which should be the undo Tablespace and the corresponding undo retention,oracle without the so-called redo segment).
Oracle does not have a concept similar to a non-transaction table. Most Oracle users recognize this.
Oracle's temporary table definition is a persistent SQL object. and is visible to all users (this should be interpreted as a session that is logged in by this user). This differs from the lightweight table used in MySQL, where temporary tables are created and destroyed within a single session.
Denish Patel
38. In Oracle, multiple ALTER TABLE operations cannot be performed in the same SQL statement, such as ALTER TABLE EMP modify name varchar () not NULL, add gender char (1) is not NULL ;
39. By default, Oracle does not automatically commit.
The KILL command is not valid in Oracle, and it uses the ALTER system KILL command. (It's not clear what the first kill concept is, operating system level kill,oracle or supported, I often use).
Oracle does not support the use of a minus sign (-) in the order by statement. (It's not clear what the specific minus sign is).
The Sqlplus command line interface for Oracle does not support highlighting.
Roland Bouman
Oracle does not support GROUP_CONCAT or similar grouping functions. You need to do this by looping through the cursor, or by combining Xmlagg and xmlquery to implement the query you need. (After Oracle 9R2, you can use custom aggregate functions to implement this functionality, and Oracle 11GR1 a new Listagg grouping function to implement this functionality after Oracle).
44. For the count (distinct expression) function, Oracle supports only one expression (either a column name or *), while MySQL supports a set of expressions. (You can implement a set of expressions in Oracle by using subqueries.)
Oracle Support for subqueries is excellent. Do not use it because of the habits in MySQL.
Oracle does not support user variables (@num). If you need to use it to compute the runtime totals, you can use the Parse Function (window function) to implement it. If you implement a specific summary function using user variables, you will find that Oracle already has built-in functionality to support these features. (In addition, in Oracle, you can set the context to implement user variables by applying dbms_session in package).
Oracle does not distinguish between time and date types, and Oracle's date type is actually a datetime type (but a larger date range than MySQL supports).
48. Oracle's stored procedures and trigger functions and performance are much better than MySQL. Do not habitually do not use them.
49. If you need to write stored procedures in Oracle, remember to take a moment to see if Oracle has the appropriate built-in package before you start coding. You'll find that most of the problems have been solved, or at least a lot of the underlying code is already there.
50 if you have a BLOB type or text type in MySQL, you may migrate them to Oracle BLOBs and CLOB. However, unlike MySQL, Oracle does not transparently manifest the data. In most cases, this is a good thing, But it also means that if you just want to treat Blob/clob as text, you'll need to spend a lot of trivial time using LOB functions, and at the beginning of use, LOB functions are pretty daunting. (LOB-related functions are really annoying, and lob processing is very inefficient, even with Oracle 11g Securefile).