comparison of dbms
TRANSCRIPT
![Page 1: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/1.jpg)
COMPARISON OF DBMS
![Page 2: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/2.jpg)
CONTENTS
ORACLEMySQL
Apr 12, 2023 2
![Page 3: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/3.jpg)
PRODUCTS FROM MySQL
• License:GPL or Proprietory
Apr 12, 2023 3
Edition Price (1 year)
Community edition Free
Standard edition $2000
Enteprise edition $5000
Cluster CG Edition $10000
![Page 4: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/4.jpg)
PRODUCTS FROM ORACLE
• License:Proprietory
Apr 12, 2023 4
Edition Price
Standard Edition $70
Enterprise edition $950
Express edition Free
![Page 5: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/5.jpg)
ORACLE vs MySQLMax rows and columns• MySQL
Rows = 65534Columns = 3398
• OracleRows = UnlimitedColumns = 1000
Apr 12, 2023 5
![Page 6: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/6.jpg)
ORACLE vs MySQLDedicated web server• Database servers are dedicated computers
that hold the actual databases and run only the DBMS and related software
• MySQL = No• Oracle =Oracle WebDB, for simple Web
applications Oracle Application Server (OAS) for professional,
scalable Web applications Oracle Internet Server (IAS) for specialized
version of Apache http serverApr 12, 2023 6
![Page 7: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/7.jpg)
ORACLE vs MySQLPrivileages• MySQL : Table level , UPDATE,INSERT on
selected columnsOracle: Table level,
UPDATE,INSERT,REFERENCES on columns
Grouping the access:MySQL : NoOracle: Privileages can be grouped into
rowsApr 12, 2023 7
![Page 8: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/8.jpg)
ORACLE vs MySQLPlatform available• Both are available on major platforms
such as Linux,Windows, OS/2Portability• MySQL: Copy MySQL file between
platforms but with same floating point formats.
• Oracle: Portable with import and export utilities
Apr 12, 2023 8
![Page 9: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/9.jpg)
ORACLE vs MySQLAuthorization• MySQL : 3 parameters, User name,
Password, Location• Oracle: 2 parameters , Username, Password.Stored procedures• MySQL: Available only from 5.x versions• Oracle: PL/SQL, JavaRollback and transaction• MySql = YES• Oracle = YESApr 12, 2023 9
![Page 10: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/10.jpg)
ORACLE vs MySQL• Oracle database supports all three types of outer
join and supports left and right outer joins.
• MySQL supports left and right outer joins, but not full outer joins.
• Without the support of full outer joins, MySQL queries requiring this functionality must use a UNION statement to concatenate the result set of query using a left outer join with the same query using a right outer join.
• Coding the same query twice, changing only the outer join from left to right in the two queries, is inefficient and redundant.
Apr 12, 2023 10
![Page 11: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/11.jpg)
ORACLE vs MySQLSequence• Sequence automatically generates unique numbers.• Sequences is to create a primary key value, which
must be unique for each row. The sequence is generated and incremented by an internal Oracle routine.
Auto-increment• Auto-increment allows a unique number to be
generated when a new record is inserted into a table.
• Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.Apr 12, 2023 11
![Page 12: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/12.jpg)
ORACLE vs MySQLXML:• Extensible Markup Language (XML) is a
standard file format used to transfer data between systems.
• Oracle provides native support for XML with a custom XML data type and tools to query the data.
• MySQL does not provide native XML support.
Apr 12, 2023 12
![Page 13: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/13.jpg)
ORACLE vs MySQL
• Both Oracle and MySQL have memory leaks problem.
• Oracle is better with memory leaks and handles multiple threads.
Apr 12, 2023 13
![Page 14: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/14.jpg)
ORACLE vs MySQLElementary Features:• Basic data types• SQL language features• Declarative integrity constraints• Programming abstractions
Apr 12, 2023 14
![Page 15: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/15.jpg)
ORACLE vs MySQLBasic Data Types
Assessment:
Product GradeMySQL GoodOracle Average
Apr 12, 2023 15
![Page 16: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/16.jpg)
ORACLE vs MySQLCharacter, numeric and date/time
data types:• MySQL:Broad subset of SQL'92
types, including all SQL'92 numeric types.MySQL supports the CHAR and VARCHAR type for character type with a length that is less than 65,535 bytes.
• The CHAR type can have a maximum length of 255 bytes.
• int,double,numberic,datetime,date.
Apr 12, 2023 16
![Page 17: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/17.jpg)
ORACLE vs MySQLCharacter, numeric and date/time
data types:• Oracle:Subset of SQL'92 types plus
specific types. Some SQL'92 types are mapped into Oracle types.
• No boolean type nor equivalent.• Oracle supports four character types:
CHAR, NCHAR, NVARCHAR2 and VARCHAR2.CHAR and NCHAR is 2,000 bytes, and for NVARCHAR2 and VARCHAR2 it is 4,000 bytes….number(10,0),float(24), number, date.
Apr 12, 2023 17
![Page 18: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/18.jpg)
ORACLE vs MySQLSQL Language Features
Assessment:Product GradeMySQL AverageOracle Good
Apr 12, 2023 18
![Page 19: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/19.jpg)
ORACLE vs MySQLSQL Language Features
User-defined data types• MySQL: No.• Oracle: User can define new
complex data types.• Subqueries in SQL queryPossibility of
using subqueries (nested queries) anywhere in SQL query.
• MySQL:No. starting from 4.1 release.
• Oracle8:Uncorrelated and correlated subqueries allowed. Queries can be nested up to 255 levels.
Apr 12, 2023 19
![Page 20: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/20.jpg)
ORACLE vs MySQLDeclarative Integrity Constraints• Integrity constraints defined
declaratively in SQL (e.g. in CREATE TABLE statement) and executed by DBMS.
AssessmentProduct GradeMySQL AverageOracle Very good
Apr 12, 2023 20
![Page 21: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/21.jpg)
ORACLE vs MySQLDeclarative Integrity Constraints
Primary key• MySQL :Yes.• Oracle :Yes.Unique key• MySQL :Yes.• Oracle :Yes.
Apr 12, 2023 21
![Page 22: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/22.jpg)
ORACLE vs MySQLDeclarative Integrity Constraints
Foreign key• MySQL :No. FOREIGN KEY clause is
allowed for compatibility only and has no effect on database operation.
• Oracle :Yes. ON DELETE CASCADE supported
Check• MySQL :No. CHECK clause is
allowed for compatibility only and has no effect on database operation.
• Oracle :Yes.
Apr 12, 2023 22
![Page 23: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/23.jpg)
ORACLE vs MySQLProgramming Abstractions
Virtual SQL language structures as views and synonyms.
AssessmentProduct GradeMySQL PoorOracle Very good
Apr 12, 2023 23
![Page 24: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/24.jpg)
ORACLE vs MySQLProgramming Abstractions
Views:A view is a tailored presentation of the
data contained in one or more tables (or other views). A view takes the output of a query and treats it as a table; therefore, a view can be thought of as a "stored query" or a "virtual table". It should be possible to use views in most places where a table can be used.
MySQL :No.But Yes in version 5 and higher
Oracle :Yes.
Apr 12, 2023 24
![Page 25: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/25.jpg)
ORACLE vs MySQLProgramming Abstractions
Updateable views:Updateable view is a view which can
be used in DML (Data Manipulation Language) statements for modification of the data.
MySQL :No.But updateable VIEWs based on single table or other updateable VIEWs available from MySql 5.0.1 release.
Oracle :Yes.
Apr 12, 2023 25
![Page 26: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/26.jpg)
ORACLE vs MySQLProgramming Abstractions
SynonymsA synonym is an alias for any table,
view or other object in database.MySQL :No.Oracle :Yes.
Apr 12, 2023 26
![Page 27: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/27.jpg)
ORACLE vs MySQLDefault Values For Columns:• In MySQL, for a column that does not allow
NULL value and for which no data is provided for the column when data is inserted into the table, MySQL determines a default value for the column.
• This default value is the implicit default value for the column data type.
• However, if the strict mode is enabled, MySQL generates errors, and for transactional tables it rolls back the insert statement.
Apr 12, 2023 27
![Page 28: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/28.jpg)
ORACLE vs MySQLDefault Values For Columns:• In Oracle, when data is inserted into
a table, data must be provided for all columns that do not allow NULL value.
• Oracle does not generate a default value for columns that have the NOT NULL constraint.
Apr 12, 2023 28
![Page 29: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/29.jpg)
ORACLE vs MySQLTriggers:• Triggers are stored procedures that
automatically execute when a database event occurs or a table event occurs.
• Database events that fire triggers include system startup and shutdown, object creation, and user logins and logouts.
• Table events: insertion, update, or deletion of one or more rows.
• For tables, the triggers can fire before or after the operations.
Apr 12, 2023 29
![Page 30: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/30.jpg)
ORACLE vs MySQLTriggers:• Oracle fires triggers for both
database-level and table-level events.
• MySQL does not support stored procedures, it also does not support triggers.
Apr 12, 2023 30
![Page 31: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/31.jpg)
ORACLE vs MySQLTriggers:• Missing trigger capability is a 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.
• However, release 5.0 included triggers.Apr 12, 2023 31
![Page 32: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/32.jpg)
ORACLE vs MySQLSecurity:• Database security is a very important
aspect of any database management system to protect access to the database operations and the data.
Apr 12, 2023 32
![Page 33: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/33.jpg)
ORACLE vs MySQLSecurity:• Oracle implements security for both
users and roles.• Roles provide a method of granting
privileges to many users with a single GRANT statement, or revoking privileges from many users with a single REVOKE statement.
Apr 12, 2023 33
![Page 34: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/34.jpg)
ORACLE vs MySQLSecurity:• MySQL uses the user name and host to
lookup the user’s privileges in the system tables.
• The user table stores database-level privileges to the user, and other tables maintain object-level privileges.
• An administrator creates users by issuing GRANT statements, or by inserting values directly into the user table.
Apr 12, 2023 34
![Page 35: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/35.jpg)
ORACLE vs MySQLSecurity:• MySQL does not use roles or groups to
grant and revoke privileges to multiple users in individual statements.
• The absence of database roles is another major drawback of MySQL.
• Without the ability to group users into roles, the database administrator will have do on individual or rely on other methods.
Apr 12, 2023 35
![Page 36: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/36.jpg)
ORACLE vs MySQLReplication:• Replicating data is the process of
copying data, synchronously or asynchronously, from one database into another database.
• Oracle supports two-way replication whereas MySQL supports only one-way replication from the master to the replica by applying transaction log files to the replica database.Apr 12, 2023 36
![Page 37: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/37.jpg)
ORACLE vs MySQLAuditing:• Oracle provides an auditing facility to
track individual users, database statements, and object statements.
• MySQL does not provide auditing capabilities.
Apr 12, 2023 37
![Page 38: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/38.jpg)
ORACLE vs MySQLAdministration:• Oracle provides for both hot and cold
backups, and includes the Recovery Manager (RMAN) utility to facilitate the backup process.
• Since MySQL stores data in operating system files, administrators perform cold backups by simply copying the files.
Apr 12, 2023 38
![Page 39: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/39.jpg)
CONCLUSION
• Oracle and MySQL perform at similar levels in tests of response times and throughput.
• But Oracle provides database clustering to achieve improved scalability and throughput, and MySQL does not provide clustering.
Apr 12, 2023 39
![Page 40: Comparison of dbms](https://reader036.vdocuments.us/reader036/viewer/2022062312/5558759fd8b42aaa7e8b53eb/html5/thumbnails/40.jpg)
Thank you…