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/
