Create index on ROW TYPE Columns

Morning all... IDS 9.40.TC4 Windows 2000 Advanced Server (Dell PowerEdge 2650 multithreaded procs) I currently have an issue which I am trying to resolve... The following row type/table is being created and we need to have quick access to the audit information in the a_prsu_prd_subtype table. CREATE ROW TYPE mis_audit_info ( account VARCHAR(30), updated DATETIMEYEAR TO SECOND, action CHAR(1), operation_id INTEGER, ip_address VARCHAR(15), username VARCHAR(30)) CREATE TABLE a_prsu_prd_subtype ( audit_info mis_audit_info NOT NULL, prsu_id INT, prty_id INT, name VARCHAR(25), long_name VARCHAR(255), last_updated DATETIME YEAR TO SECOND ) CREATE INDEX a_prsu_x_1 on a_prsu_prd_subtype (prsu_id); However, we have found that the more information thats put into the table a_prsu_prd_subtype the longer the queries take... Question: What I'd effectively LIKE to do is put an index on the audit_info.updated and audit_info.action columns. Can this be achieved??? All help very much appreciated... Having put the following SQL code through the query optimizer I get: QUERY: ------ SELECT * FROM a_prsu_prd_subtype audit1 WHERE audit_info.updated = (SELECT MAX(audit_info.updated) FROM a_prsu_prd_subtype audit2 WHERE audit_info.updated < "2005-04-03 00:00:00.00000"::DATETIME YEAR TO FRACTION(5) AND (audit_info.action='I' OR audit_info.action='U') AND audit1.prty_id = audit2.prty_id AND 0 == (SELECT count(*) FROM a_prsu_prd_subtype audit3 WHERE audit_info.action='D' AND audit2.prty_id =audit3.prty_id) ); Estimated Cost: 35 Estimated # of Rows Returned: 2 1) informix.audit1: SEQUENTIAL SCAN Filters: informix.audit1.audit_info .updated= <subquery> Subquery: --------- Estimated Cost: 3 Estimated # of Rows Returned: 1 1) informix.audit2: SEQUENTIAL SCAN Filters: (((informix.audit2.prty_id = informix.audit1.prty_id AND (informix.audit2.audit_info .action= 'I' OR informix.audit2.audit_info .action= 'U' ) ) AND informix.audit2.audit_info .updated< datetime(2005-04-03 00:00:00.00000) year to fraction(5) ) AND <subquery> = 0 ) Subquery: --------- Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.audit3: SEQUENTIAL SCAN Filters: (informix.audit3.audit_info .action= 'D' AND informix.audit3.prty_id = informix.audit2.prty_id ) QUERY: ------ SELECT description,seqno FROM 'informix'.sysxtddesc WHERE extended_id=2060 order by seqno Estimated Cost: 2 Estimated # of Rows Returned: 1 1) informix.sysxtddesc: INDEX PATH (1) Index Keys: extended_id seqno Lower Index Filter: informix.sysxtddesc.extended_id = 2060 QUERY: ------ select extended_id from 'informix'.sysxtdtypes where upper(name) ='MIS_AUDIT_INFO' Estimated Cost: 160 Estimated # of Rows Returned: 235 1) informix.sysxtdtypes: SEQUENTIAL SCAN Filters: UPPER(informix.sysxtdtypes.name ) = 'MIS_AUDIT_INFO' QUERY: ------ select fieldname,type,length,xtd_type_id,fieldno from'informix'.sysattrtypes where extended_id = 2060 and parent_no = 1 order by fieldno Estimated Cost: 2 Estimated # of Rows Returned: 5 Temporary Files Required For: Order By 1) informix.sysattrtypes: INDEX PATH Filters: informix.sysattrtypes.parent_no = 1 (1) Index Keys: extended_id Lower Index Filter: informix.sysattrtypes.extended_id = 2060