Define a primary key for each table and foreign keys in tables referencing primary keys. These primary key definitions will implicitly create an internal hash index and speed up joins between 2 tables when joined on their primary and foreign key columns.
Specify NOT NULL for columns which may not be NULL.
When a table is very large and your queries often need only parts of that table consider to partition the table into multiple partition tables (and one merge table to logically combine the partition tables). See: Data Partitioning, Updatable Merge Tables, CREATE MERGE TABLE
If your queries do some complex or expensive computations multiple times (or in multiple queries), it may be faster to first store the computation result in an added column of a table and next query the added column to eliminate the computation overhead in the queries. Use: alter table statements to add an extra column and update statements to update it with the computed values. You may also add triggers to keep the extra column values updated when new rows are added or updated.
Run the ANALYZE command to update the table's
statistics after you have changed a table's data,
see also Table Statistics
For loading large data sets use COPY INTO ... FROM file_name instead of INSERT INTO ... VALUES commands. See also table updates and Loading Bulk Data.
If you need to load large data sets via an API such as ODBC, JDBC, or DBI use prepared insert statements instead of regular insert statements. See the [prepare statement section]../../../user-guide/sql-manual/data-manipulation/prepare-statement).
It is important to execute SET sys.optimizer = 'minimal_pipe'; before loading the data via the prepared statement. Also disable autocommit mode and use manual commit after many (> 10000) or all rows are inserted into the table. Also disable all existing PRIMARY KEY, UNIQUE and FOREIGN KEY table constraints by temporarily dropping them before the data load and adding these constraints back again after the data load via the proper alter table statements.
See: Loading Bulk Data.
To view the relational execution plan of a specific query run it with the PLAN statement modifier explained on this page.
To view the MAL execution program of a specific query run
it with the EXPLAIN statement modifier.
You may change the Optimer pipeline to experiment with different optimizer pipeline steps.