Porting SQL Syntax, Function from Oracle to PostgreSQL

When We use PostgreSQL as a choice for subtitude/migrate Oracle, we will face there are difference syntax.  Oracle and PostgreSQL both conform to standard SQL. However, they contain several extensions and implementation details that differentiate one from the other. The most important differences are listed in this table below.

4 Tools Database Conversion from Oracle to PostgreSQL

ORACLE	POSTGRESQL	Explain
select sysdate from dual	select ‘now’::datetime	There is no “dual” table Unlike other RDBMS, PostgreSQL allows a “select” without the ”from” clause. This use does not affect portability because the syntax to get current time is already DBMS specific
CREATE SEQUENCE seqname [ INCREMENT BY integer ] [ MINVALUE integer ] [ MAXVALUE integer ] [ START WITH integer ] [ CACHE integer ] [ CYCLE | NOCYCLE ] To return the current value and increment the counter: sequence_name.nextval; Possible usage in a select statement: select sequence_name.nextval from dual;	CREATE SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ] To return the current value and increment the counter: nextval(‘sequence_name’); Possible usage in a select statement select nextval(‘sequence_name’);	If you don’t specify MAXVALUE, then the maximum value is 2147483647 for ascending sequences. Note that unlike other RDBMS, PostgreSQL allows a select without the ‘from’ clause. This use does not affect portability because the sequence syntax is already DBMS specific
SELECT product_id, DECODE (warehouse_id, 1, ’Southlake’, 2, ’San Francisco’, 3, ’New Jersey’, 4, ’Seattle’, ’Non-domestic’) quantity_on_hand FROM inventories	SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test	Converting DECODE to CASE WHEN
select employeeid, NVL(hire_date, sysdate) from employee where employeeid = 10;	select employeeid, coalesce(hire_date, 'now'::datetime) from employee where employeeid = 10;	Oracle also has a “coalesce” function that is a generalization of the commonly used NVL function

For next sintax will be continued...
Consulting

4 Tools Database Conversion from Oracle to PostgreSQL

There are some external tools to help migrate Oracle database to PostgreSQL, paid or free. Here, 4 external tool   that can you try.

• Full Convert 

Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.

• Oracle to Postgres data migration and sync

Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently. 

• ESF Database Migration Toolkit

A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.

• ora2pg

Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.

How to Understand SQL Index and Where should I use an index

An index is used to speed up searching in the database or An index makes the query fast” is the most basic explanation of an index I have ever seen. Although it describes the most important aspect of an index very well, it is—unfortunately—not sufficient for this book.

OK, Let's Understand the SQL Index. 

Searching in a database index is like searching in a printed telephone directory. The key concept is that all entries are arranged in a well-defined order. Finding data in an ordered data set is fast and easy because the sort order determines each entry's position.

A database index is, however, more complex than a printed directory because it undergoes constant change. Updating a printed directory for every change is impossible for the simple reason that there is no space between existing entries to add new ones. A printed directory bypasses this problem by only handling the accumulated updates with the next printing. An SQL database cannot wait that long. It must process insert, delete and update statements immediately, keeping the index order without moving large amounts of data.

• Basic Configuration Parameter for Tuning Your PostgreSQL Server
• Configuration parameter Tunables that to avoid
• Tuning Case : Tuning postgresql for large amounts of ram 

The database combines two data structures to meet the challenge: a doubly linked list and a search tree. These two structures explain most of the database's performance characteristics

And Where should I use an Index ?

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.


The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

In POSTGRES  you can use EXPLAIN in front of your SELECT statement to see if your query will make use of any index. This is a good start for troubleshooting performance problems.

#