Shulou(Shulou.com)05/31 Report--

This article mainly explains "what you must know from mysql to oracle". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what you must know from mysql to oracle.

Robert Treat

1. Some people say that Oracle has no restrictions, but in fact they may be saying that Oracle does not support LIMIT syntax. However, it can be realized by rownum virtual column, but it will be more troublesome.

2. Oracle does not support offset syntax.

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

4. For multiple Master-type systems, most users want you to use the 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-in, and you need to pay for it (partitioning is built-in in Enterprise Edition).

6. On Linux/Unix, Oracle is not as convenient as Mysql, and many Linux/Unix distributions come with Mysql by default. (there may be a deviation between my understanding of this point and my translation.)

7. INSERT... ON DUPLICATE KEY UPDATE syntax will no longer be available, but you will need to learn the more complex (but SQL-compliant) MERGE syntax.

8. Oracle's ROLE architecture is so different from MySQL that it is no longer possible to use root roles to complete all work. However, the permissions of Sys users are still the same as those of root, which is not recommended for security reasons.

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

10. In fact, discard all known settings for connection access. Oracle uses a whole new system to handle connection access.

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

12. Oracle has a wealth of documentation, but if you need professional services from Oracle, you need access to Metalink.

13. It is difficult to find relevant support information in the non-database community (for example, PHP or the blog of website developers)

14. The self-adding function can be realized through Trigger and sequence objects.

15. A large number of daily familiar SHOW commands will no longer be available. In order to obtain system information, you need to learn the data dictionary (or information Schema) of Oracle, and in depth, you need to learn the dynamic performance of Oracle.

16. MySQL adds several non-standard extensions to the information Schema, which will be difficult to find in Oracle.

17. In order to manage the transfer of production system and non-production system, you need to have an in-depth understanding of Oracle authorization rules.

18. Generally speaking, the numerical type of Oracle is simpler, and if you really need a multi-granularity numerical type similar to MySQL, you need to implement it yourself, or by setting different precision (Number (NMagnex)).

19. In Oracle, tables can grow indefinitely, but in most cases, table spaces (tablespace) are recommended for fine-grained management.

20. Oracle does not support ALTER TABLE ADD COLUMN BEFORE | AFTER, that is, it does not support the location of fields. (some of our applications have strict requirements on the order of fields, but in theory, this is a problem of business design, mainly because they use select * or insert table values to process data in most cases, rather than specifying a list of field names to be processed in select,insert.)

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 the Mysql command line, you may be disappointed with the client tool sql*plus (it takes a habitual process, sql*plus is quite easy to use).

twenty-two。 Oracle's data checking is stricter than Mysql's, and fuzzy rules that depend on MySQL may cause applications not to run. MySQL accepts "0000-00-00" as a date type value is a typical example.

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

24. Oracle does not support ENUM data types, which can only be achieved by using text-based check constraints or creating foreign key association tables.

25. Some of the more mysterious table types of Mysql (for example, blackhole,csv) cannot be found in Oracle. It is not clear what type of blockhole is, but Oracle's external table (external table) does support csv format, as well as compression / encryption of this file and other processing in 11g version.

twenty-six。 The Group By statement in Oracle must be deterministic, and it requires that all columns that appear in select list must be included in the group by clause.

twenty-seven。 The dmp file output by Oracle's exp command cannot be manually modified like Mysql's dump file.

twenty-eight。 The underlying implementation of Oracle has changed greatly, requiring you to learn UNDO and REDO segment, archiving, and DBWR processes. Personally, I think the main advantage of Oracle over other databases may be the design of its UNDO/REDO.

twenty-nine。 Oracle is not open source software, so you can't patch / optimize / fix / implement your own things on it.

Robert Hodges

thirty。 Query optimization of Oracle is a work that requires the intervention of experts. Its optimizer is much more mature than MySQL's, which means that query plans are correspondingly more difficult to interpret. If you have a big app, be prepared to hire someone who knows how to do the job effectively.

thirty-one。 The effective operation of Oracle's cost-based optimizer requires accurate statistics. For tables that have changed, statistics need to be collected regularly. The bulk data loading process also needs to execute estimate/compute statistics commands from time to time to collect statistics to achieve satisfactory performance.

thirty-two。 In Oracle, creating a connection is an expensive operation. Oracle applications with good performance tend to use connection pooling to minimize login overhead.

thirty-three。 Oracle applications need to apply prepared statement to achieve considerable performance. If your application does not use prepared statement, you need to adjust the application. This is the standard practice of applying Oracle database. The prepared statement here should refer more to the use of bound variables.

thirty-four。 The application needs to close the result set cursor, or it will soon encounter the famous "ORA-01000 Too many open cursors" error. In, this is considered a user error that requires you to adjust your code.

thirty-five。 Long queries on busy systems may encounter "ORA-01555 Snapshot too old" errors. You can eliminate this error by resizing the redo segment (sometimes by adjusting the application), but you still need to pay attention to this. There is something wrong with the expression of redo segments here. Here, it should be Undo tablespace and the corresponding Undo Retention,Oracle. There is no so-called redo segment.

thirty-six。 Oracle does not have the concept of a non-transactional table. Most Oracle users agree with this.

thirty-seven。 Oracle's temporary table definition is a persistent SQL object and is visible to all users (here it should be understood as a session logged in by this user, Session). This differs from lightweight tables used in MySQL, where temporary tables are created and destroyed within a single session in MySQL.

Denish Patel

thirty-eight。 In Oracle, multiple Alter Table operations cannot be performed in the same SQL statement, such as alter table emp modify name varchar (64) not null, add gender char (1) not null

thirty-nine。 By default, Oracle does not commit automatically.

forty。 The KILL command is not valid in Oracle, it uses the alter system kill command. I don't know what the first kill is, but kill,Oracle at the operating system level is still supported, which I often use.

forty-one。 Oracle does not support the use of a minus sign (-) in Order by statements. It is not clear what this specific minus sign is.

forty-two。 Oracle's sqlplus command line interface does not support highlighting.

Roland Bouman

forty-three。 Oracle does not support group_concat or similar grouping functions. You need to loop through the cursor to achieve, or through the combination of XMLAGG and XMLQUERY to achieve the query you need. After Oracle 9R2, you can use a custom aggregate function to implement this function, and after Oracle 11gR1, Oracle itself provides a new listagg grouping function to implement this function.

forty-four。 For the count (distinct expression) function, Oracle supports only one expression (either a column name or *), while Mysql supports a set of expressions. To implement a set of expressions in Oracle, you can do so by using subqueries.

forty-five。 Oracle supports subqueries very well. Don't stop using it because of the habit in Mysql.

forty-six。 Oracle does not support user variables (@ num). If you need to use it to calculate the runtime sum, you can use the analysis function (window function) to do so. If you use user variables to implement specific summary functions, you will find that Oracle already has built-in functions to support these functions. In addition, in Oracle, you can set context to implement the user variable by applying dbms_session to package.

forty-seven。 Oracle does not distinguish between TIME and DATE types, and Oracle's Date type is actually a DATETIME type (but supports a larger date range than MySQL).

forty-eight。 Compared with MySQL, the function and performance of stored procedures and triggers of Oracle are much better. Don't get used to not using them.

forty-nine。 If you need to write stored procedures in Oracle, remember to take some time to find out if Oracle has a built-in package before you start coding. You will find that most of the problems have been solved, or at least a lot of the basic code is already there.

If you use BLOB or TEXT types in MySQL, you may migrate them to Oracle BLOB and CLOB. However, unlike MySQL, Oracle does not materialize this data transparently. In most cases, this is a good thing, but it also means that if you just want to treat BLOB/CLOB as text, you will need to spend a lot of trivial time using the LOB function, which is quite discouraging at the beginning. (LOB related functions are really annoying, and LOB is very inefficient, even with Oracle 11g SecureFile).

At this point, I believe you have a deeper understanding of "what you must know from mysql to oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.