Unused Indices
Question: on IDS 11.7 (AIX 6.1), how to find which indexes are never used. Art Kagel suggested 'onstat -g ppf' — index partitions whose read counts are roughly equal to (only) write counts are being maintained but not read. Asked how to map the partnum back to table/index names, the poster got two answers: Richard Harnden pointed to the 'partn' utility on the IIUG site, and Doug Lawry posted a complete solution — an f_index_type() SPL function plus a v_unused_indexes view over sysmaster (systabnames, sysptntab, sysptnhdr) that lists unused duplicate indexes with their sizes, skipping unique/constraint indexes and databases with differing locale or logging. Resolved with working code.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are on version 11.7.FC5 on AIX 6.1 Is there a clear way to display what indices are unused? Thanks
Run onstat -g ppf. Any index partitions that have about yhe same number of
writes as reads is not being accessed except to update it.
Art
On Jan 18, 2013 3:10 PM, <tomcaml@gmail.com> wrote:
> We are on version 11.7.FC5 on AIX 6.1
> Is there a clear way to display what indices are unused?
> Thanks
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Thank you
and how do i relate back the partnum in the ppf output to a regular table/index name?
Hi Tom.
See my solution below. This will return duplicate indexes unused since the last restart or "onstat -z", excluding unique indexes or those required for constraints (dropping those might affect applications). To prevent failure, databases with a different locale or logging status to the current database are also excluded.
Regards,
Doug Lawry
DROP FUNCTION f_index_type;
CREATE FUNCTION f_index_type (dbsname VARCHAR(128), idxname VARCHAR(128))
RETURNING CHAR AS idxtype;/*
Get an index type within a sysmaster query
Doug Lawry, 30th June 2012
Possible return values:
P - primary key
R - referential (foreign key)
U - unique index
D - duplicate index
Example for testing:
SELECT idxname, oninit:f_index_type(DBINFO('dbname'),idxname)
FROM sysindexes
WHERE tabid > 99;*/
DEFINE sqltext VARCHAR(255);
DEFINE idxtype CHAR;
ON EXCEPTION IN (-329)
RAISE EXCEPTION -746, 0, 'Error -329: ' || dbsname || ', ' || idxname;
END EXCEPTION
LET dbsname = TRIM(dbsname);
LET idxname = TRIM(idxname);
LET sqltext =
'SELECT NVL(constrtype,idxtype) ' ||
'FROM ' || dbsname || ':sysindices AS i, ' ||
'OUTER ' || dbsname || ':sysconstraints AS c ' ||
'WHERE i.idxname = ? ' ||
'AND c.tabid = i.tabid ' ||
'AND c.idxname = i.idxname';
LET idxtype = NULL;
PREPARE stmt1 FROM sqltext;
DECLARE curs1 CURSOR FOR stmt1;
OPEN curs1 USING idxname;
FETCH curs1 INTO idxtype;
CLOSE curs1;
FREE curs1;
FREE stmt1;
RETURN idxtype;
END FUNCTION;
DROP VIEW v_unused_indexes;
CREATE VIEW v_unused_indexes
(
dbsname, tabname, idxname, size_kb
)AS
SELECT
x1.dbsname, x3.tabname, x1.tabname AS idxname,
( x4.npused * x4.pagesize / 1024 ) :: INT AS size_kb
FROM
sysmaster:systabnames AS x1, sysmaster:sysptntab AS x2,
sysmaster:systabnames AS x3, sysmaster:sysptnhdr AS x4
WHERE x1.dbsname IN
(
SELECT name
FROM sysmaster:sysdatabases
WHERE is_logging IN
(
SELECT is_logging
FROM sysmaster:sysdatabases
WHERE name = DBINFO('dbname')
)
)
AND x1.dbsname IN
(
SELECT dbs_dbsname
FROM sysmaster:sysdbslocale
WHERE dbs_collate IN
(
SELECT dbs_collate
FROM sysmaster:sysdbslocale
WHERE dbs_dbsname = DBINFO('dbname')
)
)
AND x2.partnum = x1.partnum
AND x2.partnum != x2.tablock
AND x3.partnum = x2.tablock
AND x4.partnum = x1.partnum
AND x2.pf_isread = 0
AND x2.pf_bfcwrite > 0
AND f_index_type(x3.dbsname,x1.tabname) = 'D';
GRANT SELECT ON v_unused_indexes TO PUBLIC;
SELECT * FROM v_unused_indexes ORDER BY 4 DESC;
Thanks!
tomcaml@gmail.com wrote: > and how do i relate back the partnum in the ppf output to a regular table/index name? You could use 'partn' ... <http://www.iiug.org/software/index_all.html#partn>;