It appears that 'EXPLAIN' is somewhat documented via an internet search, implying that it might be standard equipment for at least some SQL's in order to show query plans. I did find a file on our system named SQL.HELP. A search revealed 6 records containing 'EXPLAIN' but it also contained a lot of repetition. There was no occurrence in SYS.HELP that I found.
The EXPLAIN seemed to show the same thing whether I used a Universe uniquery or an SQL statement, given the same search criteria. On 10/17/07, Jef Lee <[EMAIL PROTECTED]> wrote: > Could someone please explain the EXPLAIN results? The Help does not > show about EXPLAIN. > > In particular, what does the index processing point out? I have 43,000 > records that have A8="DRJ" but only a handful that will have A50="70" so > if I can get the SELECT to use A50 first that would be great. I can see > that additional WITHs make a difference but is the order in the command > significant? > > > > Also is there anything undocumented like EXPLAIN that will tell me how > long a TCL command takes to execute? > > > > The file is indexed as follows: > > > > >LIST.INDEX GLDET A8 A9 A50 A72 > > Alternate Key Index Summary for file GLDET > > File........... GLDET > > Indices........ 3 (0 A-type, 0 C-type, 3 D-type, 0 I-type, 0 SQL, 0 > S-type) > > Index Updates.. Enabled, No updates pending > > > > Index name Type Build Nulls In DICT S/M Just Unique Field > num/I-type > > A8 D Not Reqd No Yes S L N 8 > > A9 D Not Reqd No Yes S L N 9 > > A50 D Not Reqd No Yes S L N 50 > > > > A72 is not a secondary index field. > > > > I run this SELECT statement: > > > > >SELECT GLDET WITH A50="70" AND A9="INV" AND A8="DRJ" AND A72="G" > BY.DSND A2 BY. > > DSND A10 BY @ID EXPLAIN > > Single-variable predicates processed in index: > > F8 = 'DRJ' AND (F50 = '70' AND F9 = 'INV') > > Optimizing query block 0 > > Tuple restriction: F72 = 'G' AND TRUE > > > > Driver source: GLDET > > Access method: select list 0 with 0 ids > > > > Sorted by: [EMAIL PROTECTED] > > UniVerse/SQL: Press any key to continue or 'Q' to quit > > > > 0 record(s) selected to SELECT list #0. > > > > Then if I change the SELECT by adding another WITH:- > > > > >SELECT GLDET WITH A50="70" AND WITH A9="INV" AND A8="DRJ" AND A72="G" > BY.DSND A2 BY.DSND A10 BY @ID EXPLAIN > > Single-variable predicates processed in index: > > F50 = '70' AND (F9 = 'INV' AND F8 = 'DRJ') > > Optimizing query block 0 > > Tuple restriction: F72 = 'G' AND TRUE > > > > Driver source: GLDET > > Access method: select list 0 with 0 ids > > > > Sorted by: [EMAIL PROTECTED] > > UniVerse/SQL: Press any key to continue or 'Q' to quit > > > > 0 record(s) selected to SELECT list #0. > > > > Then if I change the SELECT by adding more WITHs:- > > > > >SELECT GLDET WITH A50="70" AND WITH A9="INV" AND WITH A8="DRJ" AND WITH > A72="G" BY.DSND A2 BY.DSND A10 BY @ID EXPLAIN > > Single-variable predicates processed in index: > > F8 = 'DRJ' AND (F50 = '70' AND F9 = 'INV') > > Optimizing query block 0 > > Tuple restriction: F72 = 'G' AND TRUE > > > > Driver source: GLDET > > Access method: select list 0 with 0 ids > > > > Sorted by: [EMAIL PROTECTED] > > UniVerse/SQL: Press any key to continue or 'Q' to quit > > > > 0 record(s) selected to SELECT list #0. > > > > Thanks, > > > > > > Jeffrey Lee > Senior Analyst/Programmer > > > > IT Vision Australia Pty Ltd (ABN: 34 309 336 904) > PO Box 881, Canning Bridge WA 6153 > Level 3, Kirin Centre, 15 Ogilvie Road, Applecross, WA, 6153 > P: (08) 9315 7000 F: (08) 9315 7088 > W: http://www.itvision.com.au <http://www.itvision.com.au> > ___________________________________________________________ > > > > NOTICE : This e-mail and any attachments are intended for the > addressee(s) only and may > contain confidential or privileged material. Any unauthorised review, > use, alteration, > disclosure or distribution of this e-mail (including any attachments) by > an unintended recipient > is prohibited. If you are not the intended recipient please contact the > sender as soon as > possible by return e-mail and then delete both messages. > ___________________________________________________________ > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ > -- john ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
