Why index directive has higher cost?

Posted in 2007

TAN BK — Fri, 09 Mar 2007 08:54:11 — source: IIUG Forums & Mailing Lists

Hi all, Can any explain to me why index directive for the below select statmenet has higher cost compare with not using the index directive? This is informix 9.4. i_sub_info_3 = index by ship_acct,payer_acct,rcvr_acct QUERY: (Original SQL) ------ select {+INDEX(subscription_info, "i_sub_info_3")} subscriber_id, subscriber_ref, part_num, subscription_level, subscription_type, sd_criteria, event_criteria, awb_number, origin_stn, origin_ctry, origin_ctry_disp, dest_stn, dest_ctry, dest_ctry_disp, global_prod_code, local_prod_code, ship_acct, payer_acct, rcvr_acct, category, ckpt_abbr, status_code, ship_ref, ec_charge_type, registration_dt, subscrib_status, gsub_output_format, piece_id, origin_route, origin_zip, dest_zip, originator, instance_id from subscription_info where ( (ship_acct = "850557039" and payer_acct is NULL and rcvr_acct is NULL) or (payer_acct = "" and ship_acct is NULL and rcvr_acct is NULL) or (rcvr_acct = "" and ship_acct is NULL and payer_acct is NULL) or (ship_acct = "850557039" and payer_acct = "" and rcvr_acct is NULL) or (ship_acct = "850557039" and rcvr_acct = "" and payer_acct is NULL) or (payer_acct = "" and rcvr_acct = "" and ship_acct is NULL) or ((payer_acct = "") or (rcvr_acct = "") or (ship_acct = "850557039")) or ( (payer_acct="850557039") or (payer_acct="") or (payer_acct="") ) or ( (rcvr_acct="850557039") or (rcvr_acct="") or (rcvr_acct="") ) or ( (ship_acct="850557039") or (ship_acct="") or (ship_acct="") ) ) and subscrib_status = "ACT" and subscription_type IN ('K','k') DIRECTIVES FOLLOWED: INDEX ( subscription_info i_sub_info_3 ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 23949 Estimated # of Rows Returned: 3 1) gsubadm.subscription_info: INDEX PATH Filters: (gsubadm.subscription_info.subscrib_status = 'ACT' AND (((((((((((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.rcvr_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct = '' ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.ship_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.ship_acct = '850557039' ) ) OR ((gsubadm.subscription_info.payer_acct = '850557039' OR gsubadm.subscription_info.payer_acct = '' ) OR gsubadm.subscription_info.payer_acct = '' ) ) OR ((gsubadm.subscription_info.rcvr_acct = '850557039' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.rcvr_acct = '' ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' OR gsubadm.subscription_info.ship_acct = '' ) OR gsubadm.subscription_info.ship_acct = '' ) ) ) (1) Index Keys: ship_acct payer_acct rcvr_acct subscription_type (Key-First) (Serial, fragments: ALL) Index Key Filters: (gsubadm.subscription_info.subscription_type IN ('K' , 'k' )) QUERY: (Original SQL without INDEX Directives) ------ select subscriber_id, subscriber_ref, part_num, subscription_level, subscription_type, sd_criteria, event_criteria, awb_number, origin_stn, origin_ctry, origin_ctry_disp, dest_stn, dest_ctry, dest_ctry_disp, global_prod_code, local_prod_code, ship_acct, payer_acct, rcvr_acct, category, ckpt_abbr, status_code, ship_ref, ec_charge_type, registration_dt, subscrib_status, gsub_output_format, piece_id, origin_route, origin_zip, dest_zip, originator, instance_id from subscription_info where ( (ship_acct = "850557039" and payer_acct is NULL and rcvr_acct is NULL) or (payer_acct = "" and ship_acct is NULL and rcvr_acct is NULL) or (rcvr_acct = "" and ship_acct is NULL and payer_acct is NULL) or (ship_acct = "850557039" and payer_acct = "" and rcvr_acct is NULL) or (ship_acct = "850557039" and rcvr_acct = "" and payer_acct is NULL) or (payer_acct = "" and rcvr_acct = "" and ship_acct is NULL) or ((payer_acct = "") or (rcvr_acct = "") or (ship_acct = "850557039")) or ( (payer_acct="850557039") or (payer_acct="") or (payer_acct="") ) or ( (rcvr_acct="850557039") or (rcvr_acct="") or (rcvr_acct="") ) or ( (ship_acct="850557039") or (ship_acct="") or (ship_acct="") ) ) and subscrib_status = "ACT" and subscription_type IN ('K','k') Estimated Cost: 4 Estimated # of Rows Returned: 3 1) gsubadm.subscription_info: INDEX PATH Filters: (((((((((((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.rcvr_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct = '' ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.ship_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.ship_acct = '850557039' ) ) OR ((gsubadm.subscription_info.payer_acct = '850557039' OR gsubadm.subscription_info.payer_acct = '' ) OR gsubadm.subscription_info.payer_acct = '' ) ) OR ((gsubadm.subscription_info.rcvr_acct = '850557039' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.rcvr_acct = '' ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' OR gsubadm.subscription_info.ship_acct = '' ) OR gsubadm.s

↪ replying to TAN BK

Obnoxio The Clown — Fri, 09 Mar 2007 09:10:00 — source: IIUG Forums & Mailing Lists

TAN BK said: > Hi all, > > Can any explain to me why index directive for the below select statmenet > has > higher cost compare with not using the index directive? The cost is a meaningless number. > This is informix 9.4. > > Estimated Cost: 23949 > > Estimated Cost: 4 You have run UPDATE STATISTICS, haven't you? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to TAN BK

ART KAGEL, BLOOMBERG/ — Fri, 09 Mar 2007 12:37:44 — source: IIUG Forums & Mailing Lists

How do the actual runtime of the two queries compare? It's possible that the query without the directive is just MUCH cheaper to run than the one with the directive. Or, more likely, your Data Distributions are not up to snuff. Run the recommended UPDATE STATISTICS commands as described in the Performance Guide or the updated rules in John Miller III's white paper on the subject, or get and run my dostats utility which implements those protocols automatically. Art S. Kagel ----- Original Message ----- From: Tan Bk <ids@iiug.org> At: 3/09 3:55:03 Hi all, Can any explain to me why index directive for the below select statmenet has higher cost compare with not using the index directive? This is informix 9.4. i_sub_info_3 = index by ship_acct,payer_acct,rcvr_acct QUERY: (Original SQL) ------ select {+INDEX(subscription_info, "i_sub_info_3")} subscriber_id, subscriber_ref, part_num, subscription_level, subscription_type, sd_criteria, event_criteria, awb_number, origin_stn, origin_ctry, origin_ctry_disp, dest_stn, dest_ctry, dest_ctry_disp, global_prod_code, local_prod_code, ship_acct, payer_acct, rcvr_acct, category, ckpt_abbr, status_code, ship_ref, ec_charge_type, registration_dt, subscrib_status, gsub_output_format, piece_id, origin_route, origin_zip, dest_zip, originator, instance_id from subscription_info where ( (ship_acct = "850557039" and payer_acct is NULL and rcvr_acct is NULL) or (payer_acct = "" and ship_acct is NULL and rcvr_acct is NULL) or (rcvr_acct = "" and ship_acct is NULL and payer_acct is NULL) or (ship_acct = "850557039" and payer_acct = "" and rcvr_acct is NULL) or (ship_acct = "850557039" and rcvr_acct = "" and payer_acct is NULL) or (payer_acct = "" and rcvr_acct = "" and ship_acct is NULL) or ((payer_acct = "") or (rcvr_acct = "") or (ship_acct = "850557039")) or ( (payer_acct="850557039") or (payer_acct="") or (payer_acct="") ) or ( (rcvr_acct="850557039") or (rcvr_acct="") or (rcvr_acct="") ) or ( (ship_acct="850557039") or (ship_acct="") or (ship_acct="") ) ) and subscrib_status = "ACT" and subscription_type IN ('K','k') DIRECTIVES FOLLOWED: INDEX ( subscription_info i_sub_info_3 ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 23949 Estimated # of Rows Returned: 3 1) gsubadm.subscription_info: INDEX PATH Filters: (gsubadm.subscription_info.subscrib_status = 'ACT' AND (((((((((((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.rcvr_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct = '' ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.ship_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.ship_acct = '850557039' ) ) OR ((gsubadm.subscription_info.payer_acct = '850557039' OR gsubadm.subscription_info.payer_acct = '' ) OR gsubadm.subscription_info.payer_acct = '' ) ) OR ((gsubadm.subscription_info.rcvr_acct = '850557039' OR gsubadm.subscription_info.rcvr_acct = '' ) OR gsubadm.subscription_info.rcvr_acct = '' ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' OR gsubadm.subscription_info.ship_acct = '' ) OR gsubadm.subscription_info.ship_acct = '' ) ) ) (1) Index Keys: ship_acct payer_acct rcvr_acct subscription_type (Key-First) (Serial, fragments: ALL) Index Key Filters: (gsubadm.subscription_info.subscription_type IN ('K' , 'k' )) QUERY: (Original SQL without INDEX Directives) ------ select subscriber_id, subscriber_ref, part_num, subscription_level, subscription_type, sd_criteria, event_criteria, awb_number, origin_stn, origin_ctry, origin_ctry_disp, dest_stn, dest_ctry, dest_ctry_disp, global_prod_code, local_prod_code, ship_acct, payer_acct, rcvr_acct, category, ckpt_abbr, status_code, ship_ref, ec_charge_type, registration_dt, subscrib_status, gsub_output_format, piece_id, origin_route, origin_zip, dest_zip, originator, instance_id from subscription_info where ( (ship_acct = "850557039" and payer_acct is NULL and rcvr_acct is NULL) or (payer_acct = "" and ship_acct is NULL and rcvr_acct is NULL) or (rcvr_acct = "" and ship_acct is NULL and payer_acct is NULL) or (ship_acct = "850557039" and payer_acct = "" and rcvr_acct is NULL) or (ship_acct = "850557039" and rcvr_acct = "" and payer_acct is NULL) or (payer_acct = "" and rcvr_acct = "" and ship_acct is NULL) or ((payer_acct = "") or (rcvr_acct = "") or (ship_acct = "850557039")) or ( (payer_acct="850557039") or (payer_acct="") or (payer_acct="") ) or ( (rcvr_acct="850557039") or (rcvr_acct="") or (rcvr_acct="") ) or ( (ship_acct="850557039") or (ship_acct="") or (ship_acct="") ) ) and subscrib_status = "ACT" and subscription_type IN ('K','k') Estimated Cost: 4 Estimated # of Rows Returned: 3 1) gsubadm.subscription_info: INDEX PATH Filters: (((((((((((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.rcvr_acct = '' AND gsubadm.subscription_info.ship_acct IS NULL ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.payer_acct = '' ) AND gsubadm.subscription_info.rcvr_acct IS NULL ) ) OR ((gsubadm.subscription_info.ship_acct = '850557039' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.payer_acct IS NULL ) ) OR ((gsubadm.subscription_info.payer_acct = '' AND gsubadm.subscription_info.rcvr_acct = '' ) AND gsubadm.subscription_info.ship_acct IS NULL ) )