Example usage of indexes with SQL queries

These examples show how indexes can be applied to SQL queries.

Example 1

If the Severity or Serial field is indexed, the index on the Severity field (providing it is a tree index) can be used in the following SQL query because all the rows will have to meet the expression Severity > 3. The index on the Serial field is not used because an OR operator is used to connect two predicates.

select Summary from alerts.status where 
   Severity > 3 and (Serial = 102 or ServerName = 'NCOMS');

Example 2

If a tree index is created on the Severity field, the index on the Severity field can be used in the following SQL query. However, an index on LastOccurrence cannot be used because of the OR operator between the

LastOccurrence
> getdate() - 360

predicate and the

Summary like
'LinkUp'

predicate. Note, however, that the expression

getdate()
- 360

is considered constant for the duration of the query.

select Summary, Severity, Node from alerts.status where 
   Severity > 1 and (LastOccurrence > getdate() - 360 or Summary like 'LinkUp')

Example 3

Consider the following query:

select Summary from alerts.status where Severity > 0;

For a comparison operator like >, >=, <, or <= to be used with an index, a tree index (which is an ordered index) is required. If only 100 rows out of 20,000 have Severity 0, such an index will reduce the number of rows examined by only 0.5%, and will not provide a significant performance benefit. Therefore, the actual row data must be taken into account to decide which column to index.

Example 4

If a hash index is created on the Node column, when the following SQL query runs, only three hash lookups are performed for tool, bar, and toolbar instead of examining each row for equality of Node and one of the three values.

select Identifier from alerts.status where Node in ('tool', 'bar', 'toobar');

Example 5

If a tree index is created on the Severity column, when the following SELECT statement is processed, only Severity values 2 and 3 are searched for and returned.

select * from alerts.status where Severity > 1 and Severity < 4;

If an ORDER BY clause includes more than one column, an index is used for the first column, if available.

select Identifier, Serial from alerts.status order by Severity;

Example 6

If there are 20,000 rows in the alerts.status table, and an index on the ServerSerial field is applied to the following query, only two rows are examined instead of 20,000:

select Summary from alerts.status where ServerSerial in (102,103);

Example 7

Tivoli Netcool/OMNIbus supports the use of indexes by sub-select clauses in SQL queries, such as in the following examples:

select * from alerts.status where Identifier in (select key from alerts.bar);

select * from alerts.status where Serial in (select serial from alerts.broken_events);