SQL/MP Programming Manual for C

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for C—429847-008

4-7

Simple Example

In this example, the search is performed on one column, which is the primary key of

the table. For example, a cursor SELECT to retrieve all the columns in the EMPLOYEE

table by primary key.

The WHERE clause in this example selects on a primary key value. This means that

the SQL compiler can choose the primary index as the access path so that each

FETCH statement returns the next row in primary key sequence. This code is simple

and efficient:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY

FROM =EMPLOYEE

WHERE EMPNUM > :LASTEMPNUM

ORDER BY EMPNUM

A More Complex Example

In a slightly more complex example, suppose that the search uses a column that is not

the primary key (for example, the column LAST_NAME). In this case, the query should

be faster if there is an index on LAST_NAME. Suppose that there is an index on

LAST_NAME in this example:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY

FROM =EMPLOYEE

WHERE LAST_NAME> :LAST-LNAME

ORDER BY LAST_NAME

When an index on a nonkey column is efficient and available, the SQL compiler

probably chooses that index.

The Most Complex Example

A more complex problem occurs when the key is composed of multiple columns. In this

case, you should generally use a multivalue predicate for the comparison. This type of

predicate compares multiple columns with multiple values.

Suppose that you want to retrieve the next row in sequence by last name and first

name, and an index exists on the two columns containing the last name and the first

name. Code this type of request by using a multivalue predicate. A multivalue

predicate allows you to concatenate two or more columns and compare them with two

or more concatenated values. This type of predicate retrieves the next name in

sequence. For example:

SELECT EMPNUM, FIRST_NAME, LAST_NAME, DEPTNUM, SALARY

FROM =EMPLOYEE

WHERE (LAST_NAME, FIRST_NAME) > :LAST_NAME, :FIRST_NAME

ORDER BY LAST_NAME, FIRST_NAME

If there is an index on the two columns LAST_NAME and FIRST_NAME in that order,

this query is probably as efficient as it can be.