MySQL, PostgreSQL, and Oracle are relational database management systems. The major difference between these three databases is that MySQL is open source whereas PostgreSQL is an open-source database management system and Oracle database is developed by Oracle corporation. These three databases support various programming languages. For instance, MySQL supports Java, C++, TCL, Perl, and Haskel. Oracle and PostgreSQL to support various programming languages.
Many database administrators think that the performance of MySQL is better than PostgreSQL. On the other hand, Oracle has more reliable recovery methods than MySQL. Oracle too supports various programming languages and it has robust recovery methods. The most significant thing is MySQL supports both UNIX and Linux. Oracle too supports Unix and Linux. The thing to remember is that all these three databases are active and robust and are widely used in the market. This post will explain the technical difference between these three databases.
A trigger is a named block in any RDBMS. It is one of the most important database objects. For instance, Trigger is stored in the Database and fired implicitly when a triggering event occurs. In Oracle, MySQL, or PostgreSQL, the event can be DML, DDL, system event, or user event.
In Oracle, Trigger can be used to implement multiple business practices that cannot be implemented using integrity constraints such as UNIQUE, NOT NULL, and CHECK. The following example is displaying the Trigger:
CREATE OR REPLACE TRIGGER clients_audit_tg AFTER UPDATE OR DELETE ON clients FOR EACH ROW DECLARE first_transaction VARCHAR2(20); BEGIN -- find the transaction type first_transaction := CASE WHEN UPDATING THEN 'UPDATE' WHEN DELETING THEN 'DELETE' END;
The above Trigger will be fired after a row in the table client is updated or deleted.
MySQL Trigger will be activated when a specified event is executed for the table. The trigger can be executed for INSERT, UPDATE, and DELETE and it can be called before or after the event. The following example is displaying the Trigger.
CREATE TRIGGER before_emp_update BEFORE UPDATE ON emp FOR EACH ROW INSERT INTO emp_audit SET action = 'update', empno = OLD.empno, firstname = OLD.firstname, changedat = NOW();
In PostgreSQL, the trigger is also one special function. This special function is called automatically whenever an insert, update, and delete events occur. These are user-defined triggers. PostgreSQL gives two main triggers: row and statement-level triggers. The following example is displaying the Trigger.
First, create a new function called log_emp_name_changes:
CREATE OR REPLACE FUNCTION log_emp_name_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF NEW.emp_name <> OLD.emp_name THEN INSERT INTO emp_audits(emp_id,emp_name,changed_on) VALUES(OLD.id,OLD.emp_name,now()); END IF; RETURN NEW; END; $$
Now, we will create a Trigger:
CREATE TRIGGER emp_name_changes BEFORE UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE log_empt_name_changes();
In databases, an Index is a schema object that includes a record for each substance that arrives in the indexed column(s) of the table or cluster and gives immediate, quick access to rows. Oracle Database maintains several kinds of indexes: Normal indexes. (By default, Oracle Database creates B-tree indexes.) The concepts of Index in Oracle, MySQL, and PostgreSQL are the same but the syntaxes are different.
In Oracle, to create an index in another schema, it is necessary to have the CREATE ANY INDEX system privilege. The following example is displaying how to create a simple index:
CREATE INDEX ord_client_ix_sample ON sales (client_id, sales_id) COMPRESS 1;
The above command is displaying that the index will compress duplicated incidents of client_id column values.
MySQL utilizes indexes to promptly get rows with particular column values. Without an index, MySQL browses the entire table to find the appropriate rows. The more extensive table, the slower it explores. The following example is displaying the MySQL index:
CREATE INDEX jobindex ON emp(jobTitle);
In PostgreSQL, An index is an ordered data structure that expedites the data retrieval on storage to support it. The following example is displaying the PostgreSQL index:
CREATE INDEX idx_phonebook_phone ON phonebook(phoneno);
Simply addressing, a database backup is a representational image of data. When the primary data is suffered, one can employ the backup to restore lost data (the real files that establish the database). This image copy covers significant parts of the database, such as the control file, archive logs, and datafiles-structures. In the case of a media fiasco, the database backup is the solution to favorably retrieving the data.
The backup is the most important utility of Oracle. It belongs to the database administrator. One can utilize the BACKUP command to back up the main or a copy of a database, tablespace, or control file. It can also be used to take a backup of the server parameter file, archived redo log file, or backup set.
BACKUP DATABASE FORMAT '/disk5/arch_bps/db_%U.bck' TAG monthly KEEP UNTIL TIME 'SYSDATE + 365' RESTORE POINT Q3FY09;
In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the mysqldump utility using a particular flag “:–single-transaction”. The following example is displaying the MySQL backup command.
# mysqldump -u root -ptecmint --all-databases > all-databases.sql
On the other hand, the PostgreSQL database allows users to take backup using a utility. It contains the pg_dump utility to analyze backing up a separate database. This command is fired as a user with reading permissions. The backup utility comes under the database administrator command. The following command is displaying the use of a backup command.
pg_dump testdb > testdb.bak
Just like procedure, a package is also a schema object that gathers rationally associated variables, constants, subprograms, cursors, and exceptions. A package is collected and saved in the database. This is the main difference between Oracle, MySQL, and PostgreSQL. The package utility is only available in the Oracle database while it is not available in the MySQL or PostgreSQL databases.
A package always has a stipulation, which represents the public objects that can be called from outside the package. The following example is displaying how to create a package in Oracle.
CREATE OR REPLACE PACKAGE emppkg AS -- get employee's name FUNCTION emp_ename(empno NUMBER) RETURN VARCHAR2; -- get employee's salary FUNCTION emp_sal(empno NUMBER) RETURN NUMBER; END emppkg; CREATE OR REPLACE PACKAGE BODY emppkg AS -- get employee's fullname FUNCTION emp_ename(empno NUMBER) RETURN VARCHAR2 IS e_name VARCHAR2(60); BEGIN SELECT ename INTO e_name FROM emp WHERE emp_id = empno; RETURN e_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RETURN NULL; END; FUNCTION emp_sal(empno NUMBER) RETURN NUMBER IS emp_salary NUMBER(8,2); BEGIN SELECT sal INTO emp_salary FROM emp WHERE emp_id = empno; RETURN emp_salary; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RETURN NULL; END; END emppkg;
Just like backup, the restore is one of the most valuable commands of any relational database management system. If you are working as a database administrator then backup and restore are two commands that are widely used in the database environment. If you have taken a backup of the database and now if you don’t know how to restore it then it is not a sign of a good database administrator
In Oracle, the RESTORE command is used to restore, verify, or test and preview RMAN backups. Usually, database administrators restore backups when a hardware/software malfunction has broken a prevailing datafile, control file, or archived redo log or before implementing a point-in-time restoration. This is not the proper time to be harsh, but recovery without backups is not recommended.
You understand that you should have examined your restoration plan and that you should regularly back up a damaged database before trying to restore it. In oracle, to restore a dynamic backup of a file is to build it and make it accessible to the Oracle database server. To redeem a reestablished datafile is to renew it by utilizing redo logs, that is, documents of modifications performed to the database after the backup was taken. The following example is displaying the use of a restore command.
RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;
The above command is the official example available in the Oracle documentation. The important thing here to note is that the RESTORE command restores complete backups with various parameters and database administrators can restore files to their default location or a separate location.
In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the “mysqldump utility using a particular flag “:–single-transaction“.
In PostgreSQL, it is essential to terminate all connections to that particular database before restoring and making the backup file. The psq utility enables users to restore SQL script files created by the pg_dump, pg_dumpall utilities. These utilities are responsible for generating suitable backed up files. The following is the example of Restore in PostgreSQL.