How do I Identify Indexes Created with their Pages in Table Extents?

Technote (FAQ)


Question

You suspect that index pages are located in table extents. This is the old definition of attached index. How do you determine that they are attached? You can use various oncheck commands to find the answer, but you may have to go through a lot of data. Is there a simpler method?

Cause

Your database originated from an older engine were index pages were by default stored in table extents with data pages, or your index was created using the DEFAULT_ATTACH environment variable.

Answer

A detached index will have a record in the systabnames table of the sysmaster database. The SQL query that follows returns indexes that do not have a record in systabnames. They are attached. Edit the query, substituting your database name for database_name. Open your database and execute the query. The query returns a list of tables and their attached indexes.

SELECT t.tabname, i.idxname FROM
  sysindexes i,
  systables t
WHERE t.tabid > 99
  AND i.tabid = t.tabid
  AND i.idxname NOT IN (SELECT tabname FROM sysmaster:systabnames
      WHERE dbsname = "database_name");

Related information

Attached Indexes
Knowledge Center

Rate this page:

(0 users)Average rating

Document information


More support for:

Informix Servers

Software version:

11.5, 11.7, 12.1

Operating system(s):

AIX, HP-UX, Linux, Mac OS X, Solaris, Windows

Reference #:

1444570

Modified date:

2014-06-25

Translate my page

Machine Translation

Content navigation