Performance best practices for database customizations | Index design
12.2.1 Table design
When defining a new table, you will need to decide what type of information will be stored in the table, the data types of the attributes, and whether an attribute can have a null value, and determine which attributes require a default value, define the primary key, define additional unique indexes or indexes required for performance reasons, and determine any relationships with other tables.
Design your table with application access in mind (that is, pay attention to the way that the application accesses data). This will help you place the columns in the correct order and decide on what indexes you may need.
- If you need to repeat the same data many times in the table, then consider creating a separate lookup table with the repeated data with a primary key. Then you can reference the new table in your master table (for example, normalize your data).
- Place columns in the order that makes sense upon retrieving. For example, if you always select col1, col2, and col3 in your where clause, then order the columns the same way in the table. This will make it easier when creating indexes on them.
- Order the column with the primary key in mind. The order of the columns in the table must match the order of the columns in the primary key.
- Place variable length columns (Varchar, CLOBS) at end of your tables to avoid fragmentation.