RE: Optimizer not using index

How is the performance of the sql? I ask because even though it chose sequential scan, the estimated cost is pretty low. And that's usually a good sign. Dianne -----Original Message----- From: Helmut Leininger [mailto:h.leininger@bull.at] Sent: September 13,1999 1:41 AM To: informix-list@iiug.org Subject: Re: Optimizer not using index Joerg Spilker wrote: > Hello, > > we've some strange problems with index selection on queries on our > Digital Alpha server with Online dynamic server running (7.24 FC6) and > 4GL developement tools (7.20 UD6). > > You can look at the example below. Only when using the select with the > "where" conditions literally, the optimizer chooses the index. In the > two other cases a sequential scan is done. > > This happens even after an UPDATE STATISTICS HIGH for the complete > databases. > > We have similar tables which are accessed via index (over about 2 to 5 > columns). The problem with the sequential scan happens not on all of > these tables. > > Greetings, Joerg > ============================================================================ ==================================== > > DATABASE invekos@dge1lokal > MAIN > DEFINE > l_bearb_id INTEGER > , l_bearb CHAR(3) > , l_afa SMALLINT > , l_query CHAR(2000) > #END DEFINE > > LET l_query = "SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND > dienst_id = ?" > PREPARE sc_bearb FROM l_query > DECLARE q_bearb CURSOR FOR sc_bearb > > SET EXPLAIN ON> > LET l_bearb_id = f_get_bearbid("bau", 0) > DISPLAY l_bearb_id > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > WHERE kuerzel = "bau" AND > dienst_id = 0 > DISPLAY l_bearb_id > > LET l_bearb = "bau" > LET l_afa = 0 > OPEN q_bearb USING l_bearb, l_afa > FETCH q_bearb INTO l_bearb_id > CLOSE q_bearb > > DISPLAY l_bearb_id > > END MAIN > > FUNCTION f_get_bearbid(p_bearb, p_afa) > DEFINE > l_bearb_id INTEGER > , p_bearb CHAR(3) > , p_afa SMALLINT > #END DEFINE > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > WHERE kuerzel = p_bearb AND > dienst_id = p_afa > > RETURN l_bearb_id > > END FUNCTION #f_get_bearbid > > QUERY: > ------ > select bearb_id from r_bearb where kuerzel = ? and dienst_id = ?> > Estimated Cost: 74 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) > > QUERY: > ------ > select bearb_id from r_bearb where kuerzel = "bau" and dienst_id = 0> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: INDEX PATH > > (1) Index Keys: kuerzel dienst_id > Lower Index Filter: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) > > QUERY: > ------ > SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND dienst_id = ?> > Estimated Cost: 74 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) Hi, Sometimes the optimizer's behaviour is strange. We had a similar problem when a table had several indexes defined which had some starting columns in common (.e. idx1: f1, f2, f3; idx2: f1, f2, f4; ...) The problem disappeared only after installation of IDS 7.31 and specifying OPT_GOAL = 0 (optimization for first rows) Regards -- Helmut Leininger Bull AG / Vienna Unix Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.

dianne.pendleton@autodesk.com wrote: Hello, > How is the performance of the sql? I ask because even though it chose > sequential scan, the estimated cost is pretty low. And that's usually a > good sign. yes, you´re right. There is no significant change in the application when using the seq scan from this example. But there is another table with about 20000 rows and we did an update (using only the index rows in the where statement). Even here a seq. scan is used to find the rows to update and every update lasts about 4 seconds. Greetings, Joerg