50 things you need to know before migrating MySQL to Oracle

Link: http://www.admin10000.com/document/2550.html

I actually saw the 50 things that Robert treat had sorted out on the same day, "50 things that I need to know before migrating MySQL to Oracle" and 50 things I need to know before migrating Oracle to MySQL., it's just that this list of Baron Schwartz may be more useful to everyone, so we will first sort it out. In addition, some of the differences they listed are quite meaningful. Some may be due to their own identity as a MySQL dBA, and some may be confused about oracle. however, the list of Baron Schwartz can give you a general idea of what the similarities and differences between Oracle and MySQL are reflected in.

　　Robert treat

1. Some people say that Oracle has no restrictions. In fact, they may say that Oracle does not support the limit syntax, but it can be implemented using the rownum virtual column, but it will be more troublesome.

2. Oracle does not support the offset syntax.

3. Oracle replication may be more robust than MySQL, but it is more difficult to configure.

4. For systems of multiple master types, most users want you to use more complex and expensive Oracle RAC (although NDB may be more robust ).

5. The partitioning function of Oracle is very robust, but it is not built-in and requires special payment for this (the partitioning function is built-in in Enterprise Edition ).

6. on Linux/Unix, Oracle is not as convenient as MySQL. By default, many Linux/Unix releases bring their own MySQL. (my understanding of this and translation may be different)

7. Insert... On duplicate key update syntax will no longer be available, but you need to learn more complex (but compliant with SQL standards) Merge syntax.

8. oracle's role architecture is very different from that of MySQL, so you cannot use the root role to complete all the work. (However, the permissions of SYS users are still the same as those of root users, but they are not recommended for security purposes ).

9. the role account is associated with a specific schema (and vice versa), similar to the Database concept in the MySQL database. (The role is not completely associated with the schema. The system has some fixed role, which contains some of the defined permission sets (privilege set ), you can also customize some new role ).

10. In fact, discard all known connection access settings. Oracle uses a brand new system to process connection access.

11. Full-text search is supported, but the syntax is completely different.

12. Oracle documentation is very rich. However, if you need professional Oracle services, you need to have Metalink access permissions.

13. It is difficult to find related support information in non-database communities (such as the blog of PHP forums or website developers)

14. The auto-increment function can be implemented through trigger and sequence objects.

15. A large number of common show commands will no longer be available. To obtain system information, you need to learn the Oracle Data Dictionary (or information schema). To learn more deeply, you also need to learn about the dynamic performance of oracle.

16. MySQL adds multiple non-standard extensions to the Information schema, which is hard to find in Oracle.

17. To manage the transfer and transfer of production and non-production systems, you need to have a thorough understanding of Oracle authorization rules.

18. in general, the Oracle numeric type is simpler. If you really need a multi-granularity numeric type similar to MySQL, You need to implement it yourself, you can also set different precision values (number (n, x.

19. In Oracle, tables can grow without limit, but in most cases, we recommend that you use table space for fine-grained management.

20. oracle does not support alter table add column before | after, that is, it does not support field location. (Some of our applications have strict requirements on field order. However, theoretically, this is a problem in business design, in most cases, select * or insert Table values is used to process data, instead of specifying the field name list to be processed in select ).

21. if you are used to managing databases through a graphical interface (GUI), you will certainly like Oracle, but if you prefer to use a client similar to MySQL command line, you may be disappointed with the SQL * Plus client tool (SQL * Plus is still quite useful when you need a habit ).

22. oracle data verification is more rigorous than MySQL, and dependency on MySQL Fuzzy Rules may cause applications to fail. mySQL accepts "2017-00-00" as the date type value, which is a typical example.

23. Although Oracle's PL/SQL functions are more powerful, it does not support writing stored procedures in the standard PSM language, so you may have to learn its non-standard syntax.

24. Oracle does not support the enum data type. It can only be implemented by using the text-based check constraint or creating a foreign key Association table.

25. some of the more mysterious table types of MySQL (such as blackhole and CSV) cannot find the corresponding functions in Oracle. (It is not clear what type of blockhole is, But Oracle External table does support CSV format, this file can be compressed, encrypted, and processed in version 11g ).

26. The group by statement in Oracle must be definite, and all columns in the select list must be included in the group by clause.

27. The DMP file output by the Oracle exp command cannot be manually modified like the MySQL dump file.

28. the underlying implementation of Oracle has changed significantly. You need to learn about undo and redo segment, archiving, and dbwr processes. (I personally think that the main advantage of Oracle over other databases may be its Undo/Redo Design .)

29. Oracle is not an open source software, so you cannot fix, optimize, fix, or implement your own things on it.

　　Robert Holland

30. oracle query optimization is a task that requires expert intervention. its optimizer is more mature than MySQL, which means the query plan is more difficult to explain. if you have a large application, be prepared to hire a guy who knows how to do this effectively.

31. the cost-based Optimizer of Oracle requires accurate statistics for effective operation. for tables with changes, statistical information must be collected regularly. the batch data loading process also needs to execute the estimate/compute statistics command from time to time during the processing to collect statistics for satisfactory performance.

32. In Oracle, creating a connection is costly. Oracle Applications with good performance tend to use a connection pool to minimize the login overhead.

33. oracle applications require the application of prepared statement for considerable performance. if your application does not use prepared statement, you need to adjust the application. this is a standard Programming Practice for Oracle databases. (The prepared statement here should be more about binding variables ).

34. the application needs to close the result set cursor, otherwise it will soon encounter the famous "ORA-01000 too open cursors" error. in Oracle, this is considered a user error and you need to adjust your code.

35. long queries on busy systems may experience the ORA-01555 snapshot too old error. you can adjust the size of the redo segment (sometimes by adjusting the application) to eliminate this error, but you still need to pay attention to this. (The redo segments statement here is a bit problematic. Here it should be undo tablespace and the corresponding undo retention. Oracle does not have the so-called redo segment statement ).

36. Oracle does not have a concept similar to a non-transaction table. Most oracle users agree on this.

37. oracle's temporary table definition is a persistent SQL object that is visible to all users (this should be understood as the session logged on to this user ). this is different from the lightweight tables used in MySQL. in MySQL, temporary tables are created and destroyed in a single session.

　　Denish Patel

38. in Oracle, multiple alter table operations cannot be performed in the same SQL statement. For example, ALTER TABLE EMP modify name varchar (64) not null, add gender char (1) not null;

39. Oracle does not automatically submit data by default.

40. the kill command is invalid in Oracle. It uses the alter system kill command. (I don't know what the first kill concept is. The kill at the operating system level is supported by Oracle. I often use it ).

41. Oracle does not support minus signs (-) in the order by statement (it is unclear what this specific minus sign is ).

42. The sqlplus command line interface of Oracle does not support highlighted display.

　　Roland bouman

43. oracle does not support group_concat or similar grouping functions. you need to implement the query by looping the cursor, or by combining xmlhandler and xmlquery. (After Oracle 9r2, you can use a custom aggregate function to implement this function. After Oracle 11gr1, Oracle itself provides a new listparts grouping function to implement this function ).

44. for the count (distinct expression) function, Oracle only supports one expression (either a column name or *), and MySQL supports a group of expressions. (to implement a set of expressions in Oracle, you can use subqueries ).

45. Oracle supports subqueries very well. Do not use it because of MySQL's habits.

46. oracle does not support user variables (@ num ). if you need to use it to calculate the sum of runtime, you can use the analysis function (window function. if you use user variables to implement specific Aggregate functions, you will find that Oracle has built-in functions to support these functions. (in Oracle, you can apply dbms_session in the package to set context to Implement User variables ).

47. Oracle does not distinguish the time and date types. Oracle's date type is actually a datetime type (but it supports a larger date range than MySQL ).

48. Compared with MySQL, Oracle has much better stored procedures and trigger functions and performance. Do not use them habitually.

49. if you need to write a stored procedure in Oracle, remember to spend some time before coding to see if Oracle has a corresponding built-in package. you will find that most of the problems have been solved, or at least a large amount of basic code is already there.

50 if you use blob or text in MySQL, you may migrate them to blob and clob in Oracle. however, unlike MySQL, Oracle does not transparently materialized the data. in most cases, this is indeed a good thing, but it also means that if you just want to process blob/clob as text, you will need to spend a lot of time using the lob function. At the beginning, the lob function is quite frustrating. (lob-related functions are really annoying, and LOB processing efficiency is also very poor, even if the Oracle 11g securefile is used ).