Re: Two table join

If you think I or anyone else is wrong, don't be shy - continue the thread publicly, I promise not to be upset. All of us learn that way. Certainly I have been mistaken in the past and will continue to make errors in the future. You might also try the comp.database.informix group - that has a wider distribution. I don't currently have an Informix engine running here (I'm still rebuilding things) so I can't see what you are seeing. Mark? Are you out there? Jump in any time. cheers j. ----- Original Message ----- From: "pathri chakravarthy" <ckvarthy@yahoo.com> To: "Jack Parker" <vze2qjg5@verizon.net> Sent: Friday, July 29, 2005 12:51 AM Subject: Re: Two table join [39] > Hi Jack, > Thanks a lot for your information jack. > > There was a problem in sending to IDS group so i > posted this in XPS. I am using IDS 9.3. > > The query you arrieved is almost the correct one. > > As you have stated that the PDQ memory is not used for > sort and group operations, i feel this is not true. > The performance PDF states that it uses the PDQ memory > for this type of operations. Also i checked this. WHen > there is a sort or group by operation and PDQ set it > is taking that memory. > > I still have a doubt lingering in my mind,Why should > the sorting be done on disk when i have sufficient RAM > main memory. > > > > Bye. > > > > > --- Jack Parker <vze2qjg5@verizon.net> wrote: > > > > > You have limited control over where sorting and > > grouping occur. You can > > attempt to make it more efficient > > 1 - increase the number of tempspaces up to 2 per > > cpu (3 per cpu is the max > > that the engine will use, but that's overkill) > > You could get fancy and set up RAMdisks as temp > > spaces if this is what > > you are trying to achieve. I've never bothered to > > try it. > > 2 - export PSORT_NPROCS=n where n is the number of > > parallel processes you > > want to SORT (and group) with. > > > > 80M of DS Memory is not a lot to work with. DS > > memory comes out of your > > virtual memory and you control how much of it you > > use with PDQPRIORITY - > > although the engine may not choose to use that much > > - and it certainly is > > not going to use it for a sort or group - it prefers > > to use the tempspace. > > DSMemory is most useful in a hash join. You monitor > > this memory with > > onstat -g mgm (or 'rgm' depending on your engine) -> > I am a little confused > > here, you started this thread in the xps group and > > now you've shifted it > > over to the ids group - so I am unclear as to which > > engine you have. If you > > are monitoring memory with 'mgm' then it's ids. > > > > PDQPRIORITY also controls the degree of parallelism, > > but then read this > > article: > > > http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0205parker/ > > > > It's a cookbook article on how to tune queries, it > > goes into PDQ as well. > > > > Sounds like the query you want is: > > > > select > > a.image_loc,a.access_domain,min(a.access_datetime) > > access_datetime > > from access a > > where a.access_success='F' > > and (a.image_loc is not null or not exists > > (select 0 from image where > > a.image_loc=image.image_loc)) > > and not exists (select 0 from access where > > a.key=access.key and > > a.access_success='F' and access_success='S') > > group by 1,2 > > > > That's not the prettiest of data structures against > > which to run this sort > > of query. If you've only got 100K rows, then it's > > not a big deal, but if > > you are going to scale this up, then I recommend not > > trying to search a > > history table of this nature for this sort of > > information. Better to keep > > track of current status of an image in a status > > table, which can be indexed > > and searched without joins. > > > > cheers > > j. > > > > --- > > > > Sorting in memory: > > > > One of my Sql statement having an group by clause is > > using temporary dbspace to sort. I need this sorting > > to happen in memory itself. How can it be achieved. > > I > > tried setting the PDQ priority parameter but still > > the > > sorts are going to temp space. > > > > PDQ memory: > > > > I am bit confused from where the PDQ memory is > > allocate. Pls clarify it. > > > > I have set shmvirtsize of 80M and DS_total_memory as > > 80M and MAXPDQPRIORITY and PDQPRIORITY as 100. when > > i > > run a query and issue a onstat -g mgm i find the mgm > > is showing 80M is occupied but in onstat -g ses that > > amount is not taken. It shows only around 125K(in > > memory column) in both onstat -g seg and onstat -g > > ses. I think the mgm allocates the memory from > > Virtual > > memory. so when i give a query with this settings, > > ideally there should not be any free memory in > > virtual > > memory. > > > > ----- Original Message ----- > > From: "pathri chakravarthy" <ckvarthy@yahoo.com> > > To: "Jack Parker" <vze2qjg5@verizon.net>; > > <ids@iiug.org> > > Sent: Thursday, July 28, 2005 1:02 AM > > Subject: Re: Two table join [39] > > > > > > > Hi, > > > > > > Yes your right the statistics are not being > > updated. > > > > > > My requirement is to fetch all the failed image > > > locations. The image location may fail at one > > point in > > > time(status F) and later succeed with a status of > > 'S'. > > > I am doing a self join to get only the failed > > > ones(which are not succeeded in future). > > > > > > Also i want to know whether the server allocates > > > necessary memory as and when required or it > > allocates > > > some default memory for the joins and sorts. > > > > > > Is there a way i can control the allocation of > > memory > > > for joins and sorts. Also can these joins be > > performed > > > parallely. > > > > > > > > > > > > bye. > > > > > > --- Jack Parker <vze2qjg5@verizon.net> wrote: > > > > > > > I should have been clearer. > > > > > > > > 1 select nrows from systables where tabname = > > > > 'access'; Just see what the > > > > database thinks you have there for rows - bet > > it's > > > > small. This is where > > > > 'update statistics' will help. Your estimated > > cost > > > > value from the explain > > > > plan screams that stats are not updated. You > > might > > > > consider an update > > > > statistics medium for the columns involved. > > > > > > > > 2. The 'not in' is more expensive than an > > 'exists' > > > > clause, consider > > > > replacing it with: > > > > 'and not exists (select 0 from image where > > > > a.image_loc=image.image_loc)' > > > > > > > > 3. you don't indicate what is indexed. That > > would > > > > be useful information, as > > > > well as rowsize and fragmentation of both tables > > > > (access and image) > > > > > > > > 4. Why do you have the self join in there? You > > > > aren't using anything from > > > > the second c