1

I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle sql for their efficiency.

I am curious that Are sql tuning ways same for different DB engine,like oracle,postgresql,mysql and so on?if yes or not,why?Any suggestion are welcomed!

closed as too broad by SqlWorldWide, ypercubeᵀᴹ, Erik Darling, hot2use, miracle173 Mar 18 '18 at 20:47

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    There are many topics which are valid for (almost) every RDBMS, e.g. using prepared statements and bind variables or using indexed columns instead of derived functions from it. I know these point are rather basic and very common, however often you have poor performance because you missed such basics. But if you like to squeeze "the last 10%" performance out of an DB it becomes vendor specific. – Wernfried Domscheit Mar 19 '18 at 12:44
2

The "tricks" are to convince the DBMS to behave the way you want it to. These are large, complex pieces of software. No two behave exactly the same. So tricks that affect one may or may not work with another.

Good practice, such as only retrieving columns that a actually needed, is applicable everywhere.

1

(Caveat: I mostly know MySQL.)

One of the most basic things varies:

  • Many vendors have a ROWNUM through which a row can be fetched.
  • MySQL's MyISAM uses a byte offset into a file for both PRIMARY and secondary keys.
  • MySQL's InnoDB 'clusters' the PRIMARY KEY with the data, thereby avoiding going through a ROWNUM. Secondary keys are another matter.

A side effect of that is that "index merge intersect" (the use of two indexes in one query) is less useful than building a composite index (for MySQL).

Another thing:

  • Many vendors have lots of index types (BTree, Hash, Bit, ...)
  • MySQL has only (with a few exceptions) BTree -- it is so versatile and efficient that the implementers decided that other index types are not worth supporting.

Not the answer you're looking for? Browse other questions tagged or ask your own question.