How do I Identify Indexes Created with their Pages in Table Extents?
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?
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.
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
WHERE t.tabid > 99
AND i.tabid = t.tabid
AND i.idxname NOT IN (SELECT tabname FROM sysmaster:systabnames
WHERE dbsname = "database_name");
More support for:
Software version: 11.5, 11.7, 12.1
Operating system(s): AIX, HP-UX, Linux, OS X, Solaris, Windows
Reference #: 1444570
Modified date: 25 June 2014