Re: Re: Execution Plan: Infx 5.01

On Fri, 19 Jan 1996, Fred BON wrote: > > Hi, > > > > I used to work with database, but I'm new to Informix. Could you tell > > me how to obtain the execution plan of a request ? And, how to update > > the statistics of the index ? To perform this operation, is there another > > mean than drop and recreate the index ? > > ------------------------------------------------------------------- > Fred, > Get the faq (watch for Kerry Sainsbury's sig; it's there and elsewhere), > read the docs, but right now a few quick answers (maybe right this time). > > update statistics; (updates statistics for all tables) > update statistics for mytable; Under the OnLine Dynamic Server you can also UPDATE STATISTICS for a specific column within a table. You also have MUCH more to play with, such as UPDATE STATISTICS HIGH, MEDIUM, and LOW. You may also vary the RESOLUTION, or confidence of the statistics. There is a lot to read about this topic, and it's not an easy task. Ready? See: "Informix-OnLine Dynamic Server Performance Guide" version 7.1 December, 1994 Part No. 000-7708 starting at page 4-4 (Read the whole chapter on the query plan!) "Informix Guide to SQL Syntax" version 7.1 December, 1994 Part No. 000-7633 starting at page 1-502 (Pretty sparse, but it's a start) "Informix-SQL Reference" version 6.0 April, 1994 Part No. 000-7607 starting at page B-29 (How updating stats can affect your disk usage.) "Upgrading to Informix-OnLine Dynamic Server Training Manual" version 01-95 Part No. 502-5-227-1-999999-3 Chapter 12 (Data Distribution internals and how they affect performance) "Informix-OnLine Performance Tuning" by Elizbeth Suto 1995, From Prentice-Hall ISBN 0-13-124322-5 starting at Page 105 Also all the Administrator's guides, including Joe Lumbley's (for how to survive in the "real world"). Someday I'll write my own book on this topic alone, and you won't have to read so many others! :) > > To see what's happening with your queries, and it's always advisable > to update statistics first, turn the optimizer on - > > set explain on;> > You'll begin to see the results of all your queries in the default output > file explain.out. > > Dropping and recreating your indexes is a bit radical. If you're really > in the mood (and no one is going to be using the database), use > > alter index to cluster (on the table name) Watch out for this one. It duplicates the table, physically ordering the rows into an order which matches the index. If you don't have enough room it won't work. This is often used as a mechanism to get *more* room, (which it can be) but it takes room to make room. > Not too sure about the syntax, but you can always get some help by doing > a Ctl-W in Informix's lean and mean front end, isql. > > Yours, > Nick > ********************************* > Nick Nobbe, Library of Congress > NLS/BPH > mail: nnob@loc.gov > ********************************* Nick's right with the "short answer", but it would be better if you had a clear understanding of what happens behind the scenes when you issue these commands. Most of the statistics commands, and the optimizer directives, and the index rebuilds will DRAMATICALLY affect your system, in terms of disk usage, memory usage, performance during your operation, and performance after your command is through. While you can "muddle through" like the rest of us have, you needn't pay the full price. "Good judgement is the product of experience. Experience is the product of bad judgement." Having survived the experience is why good systems people get the BIG bucks. Good luck, (Darn it, I just saw that the subject said v5. Oh, well. I've already built up this lengthy rant, so here it goes!) __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|