slow DSS large dataset query, help

HI, Informix 7.3 UC3 on SUN. This is a DSS system, mostly for query. We have this query runs for 4 hours, looking for methods to improve performance. Please help. number 0f rows: Table_A : 30000000 Table_B : 2400000 Table_C : 5000 INDEX: Table_A.c1 Table_B.c1 TAble_B.(c2,c3) Table_C.(c2,c3) There is no index on Table_A.create_timestamp because there are only 6 different values. select A.c1, A.c2, A.c3, C.c1 from Table_A A, Table_B B, Table_C C where month(A.create_timestamp) = 11 and year(A.create_timestamp) = 1995 and A.c1 = B.c1 and B.c2 = C.c2 and B.c3 = C.c3 The Explain OUT: Estimated Cost: 170427 Estimated # of Rows Returned: 1457 1) c: SEQUENTIAL SCAN 2) b: INDEX PATH (1) Index Keys: c2 c3 Lower Index Filter: (b.c3 = c.c3 AND b.c2 = c.c2 ) NESTED LOOP JOIN 3) a: INDEX PATH Filters: (MONTH (a.create_timestamp ) = 11 AND YEAR (a.create_timestamp ) =1995 ) (1) Index Keys: c1 Lower Index Filter: a.c1 = b.c1 NESTED LOOP JOIN Onconfig file: # Physical Log Configuration PHYSDBS dbs_plog # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 20 # Number oflogical log files LOGSIZE 40000 # Logical log size (Kbytes) TBLSPACE_STATS 1 # System Configuration SERVERNUM 2 # Unique idcorresponding to a Dynamic Server in stance DBSERVERNAME prod2 # Name of defaultdatabase server DBSERVERALIASES prodshm # List ofalternate dbservernames NETTYPE ipcshm,1,20,NET # Configure pollthread(s) for nettype NETTYPE tlitcp,1,20,CPU # Configure pollthread(s) for nettype DEADLOCK_TIMEOUT 60 # Max time towait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 forsingle-processor, 1 for multi-processor NUMCPUVPS 6 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero,limit number of cpu vps to one NOAGE 0 # Process aging AFF_SPROC 0 # Affinity startprocessor AFF_NPROCS 0 # Affinity numberof processors # Shared Memory Parameters LOCKS 300000 # Maximum numberof locks BUFFERS 300000 # Maximum numberof shared buffers NUMAIOVPS 6 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes) LOGSMAX 30 # Maximum numberof logical log files CLEANERS 50 # Number of buffer cleaner processes(ST 9/5/99) #CLEANERS 10 # Number ofbuffer cleaner processes SHMBASE 0xa000000 # Shared memorybase address # (Release Notes recommend "0x0A000000L") SHMVIRTSIZE 2000000 # initial virtualshared memory segment size SHMADD 8192 # Size of newshared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 50 # Number of LRU queues(ST 9/5/99)#LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 30 # LRU percentdirty begin cleaning limit LRU_MIN_DIRTY 20 # LRU percentdirty end cleaning limit LTXHWM 40 # Long transactionhigh water mark percentage LTXEHWM 50 # Long transactionhigh water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server on your platform # see the last line of output from the command, 'onstat -b'. OFF_RECVRY_THREADS 10 # Defaultnumber of offline worker threads ON_RECVRY_THREADS 1 # Default numberof online worker threads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automaticswitchover DRINTERVAL 30 # DR max timebetween DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND /usr/informix/7.30.UC3/etc/dr.lostfound # DR lost+foundfile path # CDR Variables 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 amountof memory for any CDR queue (Kb ytes) # Read Ahead Variables RA_PAGES 64 # Number of pagesto attempt to read ahead RA_THRESHOLD 50 # Number of pagesleft before next group DBSPACETEMP temp01,temp02,temp03,temp04,temp05,temp06,temp07,t emp08,temp09,t emp10,temp11,temp12 FILLFACTOR 90 # Fill factor forbuilding indexes # method for Dynamic Server to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from O S(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 85 # Maximum allowedpdqpriority DS_MAX_QUERIES 10 # Maximum numberof decision support queries DS_TOTAL_MEMORY 2000000 # Decision support memory (Kbytes) DS_MAX_SCANS 100 # Maximum numberof decision support scans DATASKIP off # List of dbspacesto skip OPTCOMPIND 2 # To hint theoptimizer ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITLBU_PRESERVE 0 # Preserve last log for log backup OPCACHEMAX 0 # Maximum optical cache size (Kbytes) HETERO_COMMIT 0 OPT_GOAL -1 DIRECTIVES 1 Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.