Several things you must know from mysql to oracle

Welcome to the Oracle Community Forum, interaction with 2 million technical staff is actually the same day, I saw a few people at Robert treat "50 things I need to know before migrating Mysql to Oracle" Required Before migrating Oracle to Mysql "to BaronSchwartz 50 things I know, it's just that this list of BaronSchwartz may be more

Welcome to the Oracle Community Forum, interaction with 2 million technical staff> On the same day, I saw Robert Treat's "50 things I need to know before migrating Mysql to Oracle" and "Oracle migration" of Baron Schwartz. 50 things you need to know before arriving at Mysql, it's just that this list of Baron Schwartz may be more

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

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 round 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, in general, the list of Baron Schwartz can help you understand the similarities and differences between Oracle and MySQL ..

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 "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)