I want to know the differences between different databases in particular between oracle and MYSQL. Does oracle provides more security features than MYSQL? If I am wrong some highlight would help me in this context.
Printable View
I want to know the differences between different databases in particular between oracle and MYSQL. Does oracle provides more security features than MYSQL? If I am wrong some highlight would help me in this context.
1. Oracle ensures date validation
2. Oracle doesnt support time only and year only data type
3. Oracle supprots sub-sec precision
4. Oracle9i Database supports the use of temporary tables for an individual session, or global to all users. In addition, Oracle9i Database supports external tables to allow users to query data in flat files as if the data was in a database table.
5. MySQL supports six different types of tables, four of which do not support transactions (MyISAM, MERGE, ISAM, and HEAP) and two of which support transactions (InnoDB and BDB). All tables have a file in the database directory with a ‘FRM’ extension that contains the table and column layout.
6. MySQL does not support user-defined data types.
7. the absence of inline view support in a database is a strong factor in not using MySQL for serious application development.
8. MySQL supports left and right outer joins, but not full outer joins.
9. Sequences and Auto-Increment: The manner in which Oracle9i Database allows the developer to retrieve the next value as needed provides a high degree of flexibility in application development. On the other hand, the ability to start and increment sequence numbers values at the initial value within a group is a useful feature provided by MySQL that is not available in Oracle9i Database.
10. Oracle9i Database supports hierarchical queries using CONNECT BY and START WITH clauses, and provides a LEVEL pseudo-column showing the hierarchy level for each row in the result set. MySQL does not support hierarchical queries. Because MySQL does not support hierarchical queries, queries requiring this functionality must call programs using recursive function calls to process all of the rows in the result set.
11. The conditional insert capabilities included in Oracle9i Database are not thr in Mysql(INSERT WHEN). On the other hand, the capability within MySQL to insert multiple rows using a single VALUES clause replaces the need for multiple INSERT statements, resulting in smaller data loading scripts.
12. The ability to update/delete a limited number of sorted rows is a useful feature available in MySQL that is not available in Oracle9i Database.
13. release 5.0 includes some level of trigger support.Missing trigger capability is another major shortcoming of MySQL. Database administrators rely heavily on database-level triggers to monitor database events. Application developers utilize table-level triggers extensively to ensure that data meets the business requirements, and to maintain values for de-normalized columns.
14. The inability for developers to code and test userdefined functions within MySQL without restarting the database is another major shortcoming of MySQL.
15. Because PL/SQL is a proprietary programming language specific to Oracle products, Oracle9i Database includes a Java compiler and JVM within the database engine. This allows developers to write stored procedures, triggers, and functions in the standard Java programming language instead of the proprietary PL/SQL programming language. Developers compile Java programs directly into the database, or load an existing class file using the Oracle-supplied utility LOADJAVA. MySQL does not support storing or executing Java programs in the database. As pointed out in the Stored Procedures section, the lack of an embedded programming language makes MySQL an unsuitable alternative for applications that require programs within the database.
16. MySQL only supports the COMMIT and ROLLBACK statements; it does not support the use of save points, another major shortcoming of MySQL.
17. MySQL does not support data partitions, and requires a server for each set of data files. Scalability is limited to the size of a single server. The limitations within MySQL of not supporting distributed databases is a serious roadblock in creating a high-performance, scalable database solution. Without the benefits of clustering, a database is only as scalable as the hardware on which the database resides.
18. The absence of database roles is another major shortcoming of MySQL. Without the ability to group users into roles, the database administrator will have to rely on other methods to track which users should have which permissions to each database object.
19. MySQL; hot backups are not part of the free distribution.
20. Why would we use MySQL over Oracle?
Companies should consider MySQL if they do not want to pay a licensing fee to Oracle, they do not need Oracle’s functionality that is not available in
MySQL, and they do not need the advanced security and scalability capabilities of Oracle. Keep in mind that the free version does not include technical support or hot backup functionality, and applications that rely on the Open Source license of MySQL become Open Source.
21. When would we use Oracle over MySQL?
Companies use Oracle for application development requiring the capabilities that MySQL does not offer. For example, Oracle, but not MySQL, includes a procedural language to develop stored procedures, triggers, and functions; views and inline views; subqueries; hierarchical queries, advanced replication; dynamic role-based security, bitmap and reverse key indexes; and native Internet-based computing support.