Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


13 - Understand Primary Key Column Placement

The PRIMARY KEY clause only specifies which columns form part of the primary key, not their placement in the special primary key index. That's given by the order of the column definitions in the CREATE TABLE statement.

The placement of columns in a multi-column index is very important for optimizing queries. Figure 13 shows that even though primary1 is listed as the first column in the PRIMARY KEY clause, a SELECT involving primary1 uses a sequential table scan just like the query using the ordinary column data1. On the other hand, the SELECT on primary2 successfully makes use of the special primary key index because that column appears before primary1 in the CREATE TABLE.

Figure 13 - Primary Key Column Placement 
CREATE TABLE parent1

   ( primary2 INTEGER NOT NULL,

     primary1 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1, primary2 ) );


SELECT * FROM parent1 WHERE primary1 = 44;

   Estimated 3 rows in query (I/O estimate 182)

   PLAN> parent1 (seq)


SELECT * FROM parent1 WHERE primary2 = 44;

   Estimated 8 rows in query (I/O estimate 2)

   PLAN> parent1 (parent1)


SELECT * FROM parent1 WHERE data1 = 44;

   Estimated 3 rows in query (I/O estimate 182)

   PLAN> parent1 (seq)

If you need to change the placement of columns in a primary key index you must run the CREATE TABLE again with the column definitions in the right order. Just changing the PRIMARY KEY clause won't be enough.

[Home] [Table of Contents] [Previous Section] [Next Section]