RE: Update Statistics with Distributions

Jay, You can get this kind of problem. The optimizer rules are to read the smaller table first (if you have a restrictive where clause that allows it to use an index), then join to the next table. The way I fix this problem is to drop the statistics on the smaller table. I am not sure if this will work for you though as you probably need the statistics for other queries. Maybe you could drop them before running this query. It seems like an ugly solution. Regards, David Ashby -----Original Message----- From: jay_aymond@exe.com [mailto:jay_aymond@exe.com] Sent: Thursday, 1 October 1998 10:36 To: informix-list@iiug.org Subject: Update Statistics with Distributions I am running Informix Online 7.23.UC10 on a 4 Processor HP using HP UX 10.2. I was trying to imrove performance of some particular programs and noticed that "update statistics" was not being run regularly. After running the appropriate "update statistics" on all tables, most queries became extreamely slow. The "update statistics" I used was to update statistics high with distributions for all columns that were the head of an index, and update statistics low with distributions for all columnsthat were just part of an index. Most of my tests centered around two tables --- one with about 50,000 rows in it, and the other with about 1.4 million rows in it. The SQL Select statement joined 3 rows from the smaller table to about 60,000 rows of the larger table. The sqexplain.out file showed that an index was being used to perform the join. With distributions, this query took over 1.5 hours to run. Without distributions, it runs in about 5 minutes!!!!! With distributions on, it seems that the 3 CPU VP oninit process's get a huge amount of CPU time during this 1.5 hour long run. I can see nothing else out of the ordinary going on. Has anybody ever had trouble with using distributions? Any suggestions? Thanks ___________________________________________________________ Jay Aymond EXE Technologies jay_aymond@exe.com