Re: anyway to optimize this query?

>let's see... > >there are 61772773 rows in the table. > >The table is indexed on three fields, let's call it f1,f2,f3, it is a >composite index, the reason I do that is because >there is also a where clause in the query which uses all three fields as >criterias. > >update satistics was performed. > >we are running 7.22 on solaris 2.6 with 2 p300 processors and 256 meg of >rams. here are my onconfig file: > > >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /usr/informix/dbspace/rootdbs >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 125000 # Size of root dbspace (Kbytes)>MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirroredroot >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>PHYSDBS rootdbs # Location (dbspace) of physical log >PHYSFILE 10000 # Physical log file size (Kbytes) >LOGFILES 8 # Number of logical log files >LOGSIZE 10000 # Logical log size (Kbytes)>MSGPATH /usr/informix/online.log # System message log file path >CONSOLE /dev/null # System null message path>ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path >TAPEDEV /dev/null # Tape device path >TAPEBLK 16 # Tape block size (Kbytes) >TAPESIZE 10240 # Maximum amount of data to put on tape >(Kbytes) >LTAPEDEV value # Log tape device path >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 10240 # Max amount of data to put on log tape >(Kbytes)>STAGEBLOB # INFORMIX-OnLine/Optical staging area >SERVERNUM 1 # Unique id corresponding to a OnLine>instance >DBSERVERNAME Hamlet # Name of default database server >DBSERVERALIASES Macbeth # List of alternate dbservernames >DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed >env. >RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 2 # Number of user (cpu) vps >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto >one Sometimes it helps to treat a 2 CPU box as a single CPU box. Have you tried this? >NOAGE 1 # Process aging >AFF_SPROC 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors >LOCKS 2000 # Maximum number of locks >BUFFERS 37768 # Maximum number of shared buffers >NUMAIOVPS 15 # Number of IO vps >PHYSBUFF 32 # Physical log buffer size (Kbytes) >LOGBUFF 32 # Logical log buffer size (Kbytes) Might try bigger PHYSBUFF and LOGBUFF, unless you're using unbuffered logging. >LOGSMAX 10 # Maximum number of logical log files >CLEANERS 4 # Number of buffer cleaner processes Do you only have 4 disks? How come you've got more AIO VPs than cleaners? >SHMBASE 0xa000000 # Shared memory base address >SHMVIRTSIZE 8000 # initial virtual shared memory segmentsize > >SHMADD 8192 # Size of new shared memory segments >(Kbytes) >SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > >CKPTINTVL 120 # Check point interval (in sec) >LRUS 4 # Number of LRU queues Why do you have so few LRU queues? >LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaninglimit >LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit >LTXHWM 50 # Long transaction high water mark>percentage >LTXEHWM 60 # Long transaction high water mark >(exclusive) >TXTIMEOUT 0x12c # Transaction timeout (in sec) >STACKSIZE 32 # Stack size (Kbytes) >OFF_RECVRY_THREADS 10 # Default number of offline worker>threads >ON_RECVRY_THREADS 1 >DRAUTO 0 # DR automatic switchover >DRINTERVAL 30 # DR max time between DR buffer flushes (in >sec) >DRTIMEOUT 30 # DR network timeout (in sec) >DRLOSTFOUND /tmp # DR lost+found file path>CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) >CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) >CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) >CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDRqueue >(Kbytes) >BAR_ACT_LOG /tmp/bar_act.log >BAR_MAX_BACKUP 0 >BAR_RETRY 1 >BAR_NB_XPORT_COUNT 10 >BAR_XFER_BUF_SIZE 31 >RA_PAGES 80 # Number of pages to attempt to readahead >RA_THRESHOLD 20 # Number of pages left before nextgroup >DBSPACETEMP tempdb1 # Default temp dbspaces Add some more temp dbspaces. >DUMPDIR /tmp # Preserve diagnostics in thisdirectory >DUMPSHMEM 1 # Dump a copy of shared memory >DUMPGCORE 0 # Dump a core image using 'gcore' >DUMPCORE 0 # Dump a core image (Warning:thisaborts >OnLine) >DUMPCNT 1 # Number of shared memory or gcoredumps for > >FILLFACTOR 90 # Fill factor for building indexes >USEOSTIME 0 # 0: use internal time(fast), 1: gettime >from OS(slow) >MAX_PDQPRIORITY 12 # Maximum allowed pdqpriority >DS_MAX_QUERIES 8 # Maximum number of decision supportqueries > >DS_TOTAL_MEMORY 36000 # Decision support memory (Kbytes) >DS_MAX_SCANS 150 # Maximum number of decision supportscans Are you running this as a PDQ query? If you are, turn that off and try again. >DATASKIP off # List of dbspaces to skip >OPTCOMPIND 2 # To hint the optimizer >ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 =WAIT >LBU_PRESERVE 0 # Preserve last log for log backup >OPCACHEMAX 0 # Maximum optical cache size (Kbytes) >HETERO_COMMIT 0 >NETTYPE ipcshm,2,100,CPU # Configure poll thread(s) for nettype >NETTYPE tlitcp,2,100,NET # Configure poll thread(s) for nettype> >ok, hmm, what else? that's about all I can think of. btw, I can't index all >the grouped field. I am constructing the sql by a web page, everything is >dynamic, they can randomly picked anyfields as output in any order, so >indexing any group of fields inorder to speed up the groupby won't help most >of them. > >Obnoxio The Clown wrote: > >> >hi all: >> > I have a 16 gig database, each record is contains 50 fields. >> > I am running a query like this:@@N