DB Design tips:
- Define a primary key (pk) for each table and foreign keys (fk) in tables referencing pk's.
These pk definitions will implicitly create an internal hash index and speed up joins between 2 tables when joined on their pk - fk 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 and CREATE MERGE TABLE
- If your queries do some complex/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 ... ADD COLUMN column_name ... to add an extra column and UPDATE ... SET column_name = computation expression to update it with the computed values.
You may also add triggers to keep the extra column values updated when new rows are added (INSERT) 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: Loading Bulk Data
- If you need to load large data sets via an API (such as ODBC or JDBC or DBI) use Prepared Insert statements instead of INSERT INTO ... VALUES commands.
For Prepared Insert statements it is important to call
SET sys.optimizer = 'minimal_pipe';
before loading the data via the prepare 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 KEYs table constraints by dropping them (via ALTER TABLE ... DROP CONSTRAINT ...) before loading the data via the prepare statement and add them (via ALTER TABLE ... ADD CONSTRAINT ... ... (...)) again afterwards.
See: Loading Bulk Data
- To view the relational execution plan of a specific query run it with the PLAN statement modifier.
- 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.