Queries with one comparison operation may be improved with either a primary key or
functional key index, depending on whether the attribute being compared is also the
SELECT DISTINCT * FROM /exampleRegion portfolio WHERE portfolio.pkid = '123'
If pkid is the key in the /exampleRegion region, creating a primary key index on
pkid is the best choice as a primary key index does not have maintenance overhead.
If pkid is not the key, a functional index on pkid should improve performance.
With multiple comparison operations, you can create a functional index on one or more
of the attributes. Try the following:
- Create a single index on the
condition you expect to have the smallest result set size. Check performance
with this index.
- Keeping the first index, add an
index on a second condition. Adding the second index may degrade
performance. If it does, remove it and keep only the first index. The order
of the two comparisons in the query can also impact performance. Generally
speaking, in OQL queries, as in SQL queries, you should order your
comparisons so the earlier ones give you the fewest results on which to run
For this query, you would try a functional index on name, age, or on both:
SELECT DISTINCT * FROM /exampleRegion portfolio
WHERE portfolio.status = 'active' AND portfolio.ID > 45
For queries with nested levels,
you may get better performance by drilling into the lower levels in the index as
well as in the query.
This query drills down one level:
SELECT DISTINCT * FROM /exampleRegion portfolio, portfolio.positions.values positions
WHERE positions.secId = 'AOL' and positions.MktValue > 1