Re: Force index usage

In article <33898e5e.11538938@news.det.ameritech.net>, dkoehler@ameritech.net ( Dave Koehler ) wrote: >rferdy@kuwait.net (Rudy Fernandes) wrotg: > >>...[snipped]... >>Our solution, probably of academic interest only now, went something >>like this. >> >>Background: A Reporting 4GL could be used for multiple purposes. The >>selection criteria, therefore, allowed constraints on many columns >>of the table(s). >>Depending on what the user wanted from the database, s/he would >>constrain specific columns and leave the others 'open'. >> >>Solution : Our 4gl program would examine the constraints entered by >>the user on the screen, and using some very basic rules, conclude >>which of the competing indexes was the best. <snip> >Hi--- > >I'm still in the 4GL 4.1, OnLine 4.0 world. Is a suitable example that you can >give me for what you speak of? > Here's an example which depicts the principles : Baseline : Table contains 2 indexes - issdt_idx on "issue date", clnt_idx on "client code". Users need two types of reports - usually, it is a report on business for a period; sometimes, the users want a client report. Since there are tons of clients, if the user wants a client's report, the better index to use is clnt_idx; otherwise, the index of choice is issdt_idx. Report driver screen has variety of filters including g_clntfm, g_clntto (client code filter) and g_issdtfm, g_issdtto (issue date filter) 4GL code: # Step 1 : Create Select Stmt to skip column whose index you do # not want to use. IF LENGTH(g_clntfm) > 5 THEN # Aha! Default '0' changed significantly. User has a constraint on Client. # clnt_idx is the one I want to use. LET l_select = 'SELECT ... FROM table WHERE p_clntcd = ? " ELSE LET l_select = 'SELECT ... FROM table WHERE p_issdt BETWEEN ? AND ? " END IF PREPARE stmt FROM l_select DECLARE rep_curs CURSOR FOR stmt IF LENGTH(g_clntfm) > 5 THEN OPEN rep_curs USING g_clntfm ELSE OPEN rep_curs USING g_issdtfm, g_issdtto END IF FOREACH rep_curs INTO ... # Irrespective of SELECT constraints, filter both columns based on # user inputs. IF l_clntcd < g_clntfm OR l_clntcd > g_clntto THEN CONTINUE FOREACH END IF IF l_issdt < g_issdtfm OR l_issdt > g_issdtto THEN CONTINUE FOREACH END IF ... For clarity, I've only shown 2 columns in the where clause. In practise, all the non-indexed columns which the user can constrain on will be present in both l_selects. HTH. ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------