Comparison and Selection of Oracle, MySQL, and PostgreSQL

This article focuses on the following aspects:Oracle,MySQLAndPostgreSQLFor comparison, we will start to introduce them one by one.

Hybrid SQL (Optimization engine)

SQL is the basis and most critical measure for your interaction with your database, no matter which one you choose. These three platforms are also starting from their separation. Oracle supports extremely mixed queries, almost unlimited number of tables, and connection and Union of all types. Although Oracle has many functions, it is a cost-based optimizer, it can analyze SQL, rewrite and simplify it if possible, choose indexes based on costs, decide the monopoly of a table, and all other functions of it.

Read the MySQL documentation and you will be aware of the descriptions of features that are biased towards and the details about the provision of definitions, so that the optimizer and function adjustment are very mixed on any platform. The legal maximum scale of MySQL is that the maximum number of tables in any connection or in a view is 61. In sequence, I personally feel that it is difficult to use one query of so many tables in any way. As mentioned above, now it is more practical to use the optimizer instead of querying the largest table type.

Postgresql of version 8. x supports all SQL92 specifications and has almost no limitations. In sequence, I feel that you will see a database that is better than other databases, even in terms of optimization. Hybrid queries will become messy, and the query plan is your good friend when diagnosing functional bottlenecks.

Index type

Indexing skills are vital to database functions, and Oracle has many options to choose from. There are many different index types, including standard binary trees, conversion keys, functional-based Bitmap indexes that are often used incorrectly, and even index tables. As the attach Technique progresses, the database administrator has available Oracle Text to supply the index, which approves you to search CLOB (large character object ), oracle Spatial provides location-based data indexes.

In MySQL, we are aware of Binary Tree, hash, plain text, and GIS indexes (for location-based data ). There is also a cluster index, but in case my Oracle experience gives me any dial-up, even if most of the usage is idle, it is irrelevant. Therefore, in most cases, I only see binary tree indexes in Oracle, MySQL, or ipvs. In addition, although functional-based indexes in MySQL are not available, they can simulate the creation of another column that retains data using this function, then add an initiator to install it.

Postgresql provides the binary tree and hash, as well as the R tree, and its own customized GiST index types. GiST index types approve the creation of function-based indexes zhengduan.com using user-defined types and approval. Oracle provides a similar functional category. Its function-based indexes can be used for pl/SQL-based functions, but not standardized predefined system functions, for example, you may have used trunc and UPPER. Be careful that indexes like this may be very slow. You may not even want to hear the word "slow" when talking about entering or removing data.

It does end, And the optimizer chooses an index that is better than the Oraclesh-ra.org.

Audit

Oracle enables you to audit a table or file and review the index tool. Once approved, you will be able to review all the activities that may be logged on to a table, or even a specific user. It has many options and is extremely easy to set to available.

Postgresql also has this functionality, and it looks like it can be used together with Oracle.

On the other hand, MySQL does not seem to provide this function. However, you can certainly create your own stored procedures and triggers to do what you want and input relevant messages to the data table, this only requires a little extra work.

Data Type

Oracle, MySQL, and Postgresql support up to 4 GB of large binary and text data. All the data types we know and are infatuated with are extremely useful, such as numbers, characters, and dates. Each type of custom data is provided to a certain extent, although I rarely see using it.

One thing I want to talk about at present is that Postgresql and MySQL have surpassed the previous Foundation, so there is no need to worry that they end up using a good self-growth column type that we often use. Oracle's answer is more useful in order to do this, but it is static. Oracle has never SET a data type, which is very important. It also has no time to work on data types, both Postgresql and MySQL. However, you will be aware that you will be able to monopolize all the data and time you want on these three database product platforms, from the monopoly on the time zone to the interval processing.

Another reason I am infatuated with Postgresql and MySQL is that they support a variety of good mathematical numbers, from smallint to decimal, real, double, and so on. These took advantage of the approximate architecture and are commensurate with the types of data available in programming languages, such as C.

Support for transactions

In the database territory, the monopoly of appropriate transactions complies with acronym to ACID, which means atomicity, uniformity, isolation and permanent. Atomicity means that a transaction is a comprehensive unit, with all committed or rolled back. Uniformity means you migrate from one * VALID * event to another. For example, you must strictly limit your business logic. Isolation means that one transaction cannot see what another transaction is doing until it ends (committed ). The permanent meaning is that once submitted, this change is always, and is a * crucial * to stop your hard drive from losing *.

I have something to say about this question, and I am eager to avoid the answer. For example, the functional view of Oracle in its data dictionary is not transactional. Second, they are not required in that environment. There are many advantages. I have seen an airline ticket system that requires regular upgrades and a second server to be added for Oracle placement. They looked at all the associated approval costs for the software and the hardware costs for large equipment. Then they looked back at the exploitation. Some people accurately realize that 90% of the monopoly on the airline website is Expo flights (read-only), and only 10% is the sincere purchase of air tickets. As a result, they had a set of low-cost MySQL servers used for Expo flights, and the change in the journey request was submitted to large Oracle servers for a strong ticket monopoly. What a good combination processing plan!

Yes, MySQL has gone a long way in transaction InnoDB tables. This is also approved to explain why Oracle purchased Innobase. Some people still feel that MySQL is only an SQL interface for LDAP or NFS. However, MySQL has gone a long way and will continue.

Postgresql is more comprehensive at this point, so I will say that the important thing you can see is that it is different from the functional aspects of Oracle, even if it is a problem.

Induction

As you can see in multiple parts of our database platform, there are many things to consider when choosing a database platform. From comprehensive functions to support and support from suppliers, to functions and optimization. Do not invest too much before you know what you are using and what it really requires. At the end of the day, you may feel that these are general and difficult to determine. However, with a little invention, you can think about the theme carefully and have a good development environment, you should be able to come up with a cost-efficient and scalable processing plan.

For more information about Oracle, MySQL, and PostgreSQL, if you have different opinions, please share them with us.