Skip to main content

Database - Queries & Indexes

Execution Order Control​

In rare cases, the optimizer’s default execution order is suboptimal. Use STRAIGHT_JOIN to override:

SELECT *
FROM PERSON
STRAIGHT_JOIN CONTACT ON (CONTACT.PERSON_ID = PERSON.PERSONID)
STRAIGHT_JOIN ORGANISATION ON (ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID)
LEFT JOIN ADDRESS ON (ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID)

Common SQL Pitfalls​

  • Loading large CLOB or BLOB fields unnecessarily (e.g., PICTURE)
  • Running queries inside loops — always load in bulk and process in JDito

Working with Indexes​

Proper indexing is essential for fast and efficient query execution.

Use indexes when:

  • Columns are structured and sortable (e.g., timestamps, foreign keys, keywords)
  • Tables are large or queried frequently

Avoid indexing:

  • Columns that store mostly NULL values
  • Fields with inconsistent or unstructured content

When choosing what to index, consider:

  • Table size
  • Access frequency and modification rate
  • Filter and sort conditions in your queries
tip

In ADITO, foreign key fields like CONTACT_ID are not indexed automatically. You must add indexes manually, as foreign key constraints are not enforced by default.

Common issues and fixes​

  • Functions on indexed columns (e.g., UPPER, IFNULL) prevent index use. Replace: 
    (ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID OR
     (ORGANISATION.ORGANISATIONID IS NULL AND CONTACT.ORGANISATION_ID IS NULL))
  • Different collations on joined or compared fields can prevent index usage. Ensure that all columns involved in comparisons use the same collation to maintain index effectiveness.

Ensure all foreign key columns have indexes, even if not defined as foreign keys in the DB schema.

Descending Indexes​

Default indexes are ascending. Use descending indexes if queries typically return the newest data first.

<createIndex indexName="IDX_ACTIVITY_ENTRYDATE" tableName="ACTIVITY">
  <column name="ENTRYDATE" descending="true"/>
</createIndex>

This is especially useful for timestamp or sequential ID fields.

Multi-Column Indexes​

If filtering or joining on multiple fields, create a composite index:

CREATE INDEX IDX_AB_ATTRIBUTERELATION_OBJECT ON ab_attributerelation (OBJECT_ROWID, OBJECT_TYPE)

If the database doesn’t use the expected index, you can force it with USE INDEX:

SELECT *
FROM contact
STRAIGHT_JOIN ab_attributerelation USE INDEX (IDX_AB_ATTRREL_OBJECT)
  ON OBJECT_ROWID = CONTACT.CONTACTID AND OBJECT_TYPE = 'Person'
warning

Execution plans may suggest creating indexes across multiple columns, but indexes on 3, 4, or more fields are often ineffective in real-world queries and can negatively impact performance if unused. Always try to optimize the SQL statement first. Only introduce multi-column indexes when simpler alternatives fail to resolve the performance issue.

Oracle: Index Name Length​

Oracle databases limit index names to 30 characters. Use this regex to detect violations:

indexName="\w{31,}"

find oversized indexes