RE: Update statistics high vs medium

Hi Abhi, Thanks for answering my note. I am aware that the DOT table will be sequentially scanned because of the wild card search. But the other tables joined in the same query should be able to use indexes. I attached a query and the sqexplain.out file. Thanks again, in advance. Here is one query: SELECT resume_obj.resume_serial_num, resume_obj.obj_serial_num,resume_obj.dot_serial_num, jobseeker.ssn,jobseeker.vet,dot.dot_title, resume_obj.exper,jobseeker.educ_cd,educ.educ_descrpt, jobseeker.js_city,resume.resume_serial_num,resume.ssn, resume.dl_class_cd,resume.will_rlct,resume.updt_dt, resume.kywd1,resume.kywd2,resume.kywd3,resume.kywd4,resume.kywd5,resume.kywd6 FROM resume_obj,dot,resume,jobseeker,educ WHERE (opt_to_suprs="N" ) and dot.dot_serial_num=resume_obj.dot_serial_num and jobseeker.status="Active" and resume_status="A" AND resume_obj.exper+0>="12" AND jobseeker.js_cnty="9" AND (resume.dl_class_cd="0" OR resume.dl_class_cd=" " OR resume.dl_class_cd="1" OR resume.dl_class_cd="2" OR resume.dl_class_cd="3" OR resume.dl_class_cd="4") AND jobseeker.educ_cd>="9" AND (resume.kywd1 = "DELPHI" OR resume.kywd2 = "DELPHI" OR resume.kywd3 = "DELPHI" OR resume.kywd4 = "DELPHI" OR resume.kywd5 = "DELPHI" OR resume.kywd6 = "DELPHI") AND (resume_obj.dot_serial_num IN (select dot_serial_num from dot where dot_title LIKE "%PROGRAMMER%")) AND (resume_obj.dot_serial_num IN (select dot_serial_num from dot where dot_title LIKE "%HELPER%")) AND resume.resume_serial_num=resume_obj.resume_serial_num AND dot.dot_serial_num = resume_obj.dot_serial_num AND jobseeker.ssn = resume.ssn AND jobseeker.educ_cd = educ.educ_cd ORDER BY dot.dot_title ASC, resume.updt_dt DESC, jobseeker.vet DESC; Here is the sqexplain.out file: ***This is the query plan when update stat medium is NOT run on dot(dot_title). Estimated Cost: 18475 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) informix.resume_obj: SEQUENTIAL SCAN Filters: (informix.resume_obj.exper + 0.0000000000000000 >= 12 AND (informix .resume_obj.dot_serial_num = ANY <subquery> AND informix.resume_obj.dot_serial_n um = ANY <subquery> ) ) 2) informix.resume: SEQUENTIAL SCAN Filters: (informix.resume.opt_to_suprs = 'N' AND informix.resume.resume_stat us = 'A' ) DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.resume_obj.resume_serial_num = informix.resum e.resume_serial_num 3) informix.jobseeker: INDEX PATH Filters: (informix.jobseeker.status = 'Active' AND informix.jobseeker.educ_c d >= '9' ) (1) Index Keys: ssn (Serial, fragments: ALL) Lower Index Filter: informix.jobseeker.ssn = informix.resume.ssn 4) informix.educ: INDEX PATH (1) Index Keys: educ_cd Lower Index Filter: informix.educ.educ_cd >= '9' DYNAMIC HASH JOIN Dynamic Hash Filters: informix.jobseeker.educ_cd = informix.educ.educ_cd 5) informix.dot: INDEX PATH (1) Index Keys: dot_serial_num Lower Index Filter: informix.dot.dot_serial_num = informix.resume_obj.do t_serial_num Subquery: --------- Estimated Cost: 1476 Estimated # of Rows Returned: 2147483647 1) informix.dot: SEQUENTIAL SCAN Filters: informix.dot.dot_title LIKE '%PROGRAMMER%' Subquery: --------- Estimated Cost: 1476 Estimated # of Rows Returned: 2147483647 1) informix.dot: SEQUENTIAL SCAN Filters: informix.dot.dot_title LIKE '%HELPER%' ************************************************************************ ************* ***This is the query plan that update stat medium is run on dot(dot_title). Estimated Cost: 2973 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) informix.resume_obj: INDEX PATH Filters: (informix.resume_obj.exper + 0.0000000000000000 >= 12 AND informix. resume_obj.dot_serial_num = ANY <subquery> ) (1) Index Keys: dot_serial_num (Serial, fragments: ALL) Lower Index Filter: informix.resume_obj.dot_serial_num = ANY <subquery> 2) informix.resume: INDEX PATH Filters: (informix.resume.opt_to_suprs = 'N' AND informix.resume.resume_stat us = 'A' ) (1) Index Keys: resume_serial_num Lower Index Filter: informix.resume.resume_serial_num = informix.resume_ obj.resume_serial_num 3) informix.jobseeker: INDEX PATH Filters: (informix.jobseeker.status = 'Active' AND informix.jobseeker.educ_c d >= '9' ) (1) Index Keys: ssn (Serial, fragments: ALL) Lower Index Filter: informix.jobseeker.ssn = informix.resume.ssn 4) informix.dot: INDEX PATH (1) Index Keys: dot_serial_num Lower Index Filter: informix.dot.dot_serial_num = informix.resume_obj.do t_serial_num 5) informix.educ: INDEX PATH (1) Index Keys: educ_cd Lower Index Filter: informix.educ.educ_cd = informix.jobseeker.educ_cd Subquery: --------- Estimated Cost: 1476 Estimated # of Rows Returned: 1 1) informix.dot: SEQUENTIAL SCAN Filters: informix.dot.dot_title LIKE '%PROGRAMMER%' Subquery: --------- Estimated Cost: 1476 Estimated # of Rows Returned: 1 1) informix.dot: SEQUENTIAL SCAN Filters: informix.dot.dot_title LIKE '%HELPER%' >---------- >From: abhi[SMTP:abhi@ihot.com] >Sent: Thursday, May 28, 1998 7:34 PM >To: informix-list@iiug.org >Subject: Re: Update statistics high vs medium > >Susanna, >If you are using wild card search or uses "match/like/subscripts" >the query cannot use index even if there is an index on the column(s). > >So update statistics cannot have any effect in performance of your query. >You should get the same result even if you never run update statistics. > >I will be interseted in seeing your query and sqexplain.out. > >Cheers > >Chung-Wong, Susanna@EDD wrote in message <6kibp5$t0p$1@news.xmission.com>... >> >>Hi, we are running Informix ODS v7.23 on AIX 4.2. There is a table with >>27000 records look like this: >> TABLE dot >> ( dot_serial_num char(11) not null PK, >> dot_title char(82), >> create_dt date indexed) >> >>We have several queries that join multiple tables including this DOT >>table and they perform wild card search on dot_title like this: >>dot_title like '%CLERK%'. I found out if the update statistics is not >>run correctly for this column, the optimizer chooses to sequential scan >>the other tables in these queries. And I found out only update >>statistics medium works for this column: >> Update statistics medium for table dot(dot_title) distributions only>> >>Originally, I run these two statements ev