Re: Create index on ROW TYPE Columns

Hi, - I'm not surprised the query goes worst with more rows. It is a pretty hurting one. - Suppose you have updated statistics. - Why don't you use a functional index? (not sure it will help, but worth a try) - Sometimes it's better to break the query in smaller ones, even using temporary tables in the process. Just my 2 cents. Jose Luis Sean Kelsey wrote: > 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 > sending to informix-list