Paul, I often use this feature when I am trying to understand the view I create. The actual wording come I sent came from the v6.5++ on line syntax reference at www.rsyntax.com. I am sure AI got the initial information way back when from the late lamented "R:Base Exchange" magazine. As to explanations of how to interpret the information I can not remember and discussions on this subjects in print or online.
It would be nice to open a dialog on this list to flesh out how one can better use the information presented in the EXPLAIN.DAT file. For example using the following view: CREATE VIEW RptVuMbrDtlDp + (FChrLastName, LastNameSortSeq, FirstName, MiddleName, LastName, FullNameLF+ M, MbrNumber, Salutation, PrsnTitle, DearBroGreet, NickName, SigOtherName,+ Birthday, Initiated, HighSchoolNbr, HighSchoolClassOf, CurHomeAudAction, Cu+ rHomeAudDate, CurHomeAudTime, CurWorkAudAction, CurWorkAudDate, CurWorkAudT+ ime, DateOfDeath, AcademicRecCnt, MbrStatus, RegisterChgDate, HqMailStatus,+ MAdrSource, HAdrSource, HPhoneNbr, HCellNbr, EMailAdr, HomeAdrBlock, HCoun+ try, HomeChgDate, WAdrSource, WPhoneNbr, WFaxNbr, JobTitle, CpyName, WorkAd+ rBlock, WCountry, WorkChgDate, FromOrgNameNbr, FromChapLit, EstAge) + AS SELECT T1.FChrLastName,T1.LastNameSortSeq,T1.FirstName,T1.MiddleName,T1.+ LastName,T1.FullNameLFM,T1.MbrNumber,T1.Salutation,T1.PrsnTitle,T1.DearBroG+ reet,T1.NickName,T1.SigOtherName,T1.Birthday,T1.Initiated,T1.HighSchoolNbr,+ T1.HighSchoolClassOf,T1.CurHomeAudAction,T1.CurHomeAudDate,T1.CurHomeAudTim+ e,T1.CurWorkAudAction,T1.CurWorkAudDate,T1.CurWorkAudTime,T1.DateOfDeath,T1+ .AcademicRecCnt,T1.MbrStatus,T1.RegisterChgDate,T1.HqMailStatus,T1.MAdrSour+ ce,T2.HAdrSource,T2.HPhoneNbr,T2.HCellNbr,T2.EMailAdr,T2.HomeAdrBlock,T2.HC+ ountry,T2.HomeChgDate,T3.WAdrSource,T3.WPhoneNbr,T3.WFaxNbr,T3.JobTitle,T3.+ CpyName,T3.WorkAdrBlock,T3.WCountry,T3.WorkChgDate,T4.FromOrgNameNbr,T4.Fro+ mChapLit,(FORMAT(IFNULL(DeathYear, INT((.#DATE - IFEXISTS(Birthday,Birthd+ ay,IFEXISTS(Initiated, ADDYR(Initiated,-19),.#DATE)))/365.25),DeathYear -+ IYR4(Birthday)),AgeFmtMask)) FROM RegisterOfMbrs T1,A1AllNbrHome01 T2,A1All+ NbrWork01 T3,A1FromOrgNameNbr T4 WHERE T1.MbrNumber = T2.MbrNumber AND T1.M+ brNumber = T3.MbrNumber AND T1.MbrNumber = T4.MbrNumber RegisterOfMbrs is the base table others are views: A1AllNbrHome01 is an outer join of RegisterOfMbrs with the MbrCurHomeAdr table (not all members have home address rec) A1AllNbrWork01 is an outer join of RegisterOfMbrs with the MbrCurWorkAdr table (not all members have work address rec) A1FromOrgNameNbr is an outer join of RegisterOfMbrs with MbrFromChapter (very limited number of members listed on the MbrFromChapter) Here is the contents of EXPLAIN.DAT when I do a SELECT * from RptVuMbrDtlDp R> prompt 05/28/2009 10:42:30 SelectCost=1 (OptimizationTime=0ms) RptVuMbrDtlDp Sequential R> prompt 05/28/2009 10:42:30 SelectCost=5.059211 (OptimizationTime=0ms) A1AllNbrHome01 Sequential RegisterOfMbrs (ColumnName=MbrNumber,Type=P) Random Dup=1 Adj=0.9956141 A1AllNbrWork01 Sequential A1FromOrgNameNbr Sequential R> prompt 05/28/2009 10:42:30 SelectCost=1.846558 (OptimizationTime=0ms) RegisterOfMbrs (ColumnName=MbrNumber,Type=P) IndexOnly Dup=1 Adj=0.9956141 MbrCurHomeAdr (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=0.995283 R> prompt 05/28/2009 10:42:30 SelectCost=0.06554899 (OptimizationTime=0ms) RegisterOfMbrs (ColumnName=MbrNumber,Type=P) IndexOnly Dup=1 Adj=0.9956141 MbrCurWorkAdr (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=0.9753846 R> prompt 05/28/2009 10:42:30 SelectCost=0.1005131 (OptimizationTime=0ms) RegisterOfMbrs (ColumnName=MbrNumber,Type=P) Random Dup=1 Adj=0.9956141 MbrFromChapter (ColumnName=MbrNumber,Type=F) Random Dup=1 Adj=1 Questions: What is SelectCost and how is it calculated? Type=P,Type=F seem to indicate PRIMARY and FOREIGN keys What do the other terms indicate? How would I use this information to construct a faster/better version of this view? Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: Paul InterlockInfo <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, May 28, 2009 9:26:18 AM Subject: [RBASE-L] - Re: Query Optimization Thank You – MR. James Bentley, All I can say is Ho Haaaa! (Scent of a Women) where did you find that. Nope I take that back, I do not want to know. MR James Bentley that is good recall and information that blew me away this morning, and I add - so early in the morning. I will have to go back in time to my RBase magazines back in the late 80’s and 90’s and see what I can find. Sincerely, Paul Dewey From:[email protected] [mailto:[email protected]] On Behalf Of James Bentley Sent: Thursday, May 28, 2009 6:45 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Query Optimization Stephen, Here is what you need to do. I have these commands in a RMD file. I then RBEDIT explain.dat To the best of my knowledge there is virtually no information on how to interpret the various parts of data that appears in the EXPLAIN.DAT file. It does however break down how it processes the various tables in the command. "SET DEBUG ON SET VAR MICRORIM_EXPLAIN = 47 RETURN *(MICRORIM_EXPLAIN This variable shows the decision the optimizer made when executing a particular command. The optimizer results are placed in the file EXPLAIN.DAT. The DEBUG command must be on to use microrim_ explain. The available options for microrim_ explain are 1 - output the table order. 2 - force optimization of joins over five tables. This option is ignored if microrim_fullopt is set. 4 - output the command file name and next byte offset. 8 - output the current date and time. 32 - display the sort technique used. To use multiple options, add the option numbers together and set the variable to the result. For example: SET VAR microrim_explain = (1 + 4 + 8) SET VAR microrim_explain = 13 ) Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From:Stephen Markson <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wednesday, May 27, 2009 7:36:53 PM Subject: [RBASE-L] - Query Optimization Is there any info on how the RBase engine parses queries to use indexes effectively? Are there are any techniques that can force the engine to use a particular index? I seem to recall that in the old days there was a system variable, MICRORIM_EXPLAIN_PLAN or something like that would show how R:Base had parsed the query. Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca<http://www.forensys.ca/> 416 512 6950
