Improve query performance

From: Ursula Lee <ursula.lee_at_thales-is.com>
Date: Tue, 09 Dec 2003 17:55:38 +0800
Message-ID: <br468d$hua20@imsp212.netvigator.com>

Hi All,

I need build the small tables extracting from the big tables called HISVALUE. Problem is the build up time is extremely slow as the insertion into the big tables will occur continuously.

(That is Files loaded into table HISVALUE --> then table TEMP_SENSOR)

Thus, I need to choose a build up time to those small tables, e.g. every 15 min that won't affect the original insertion.

Question:
1. How can I improve the query performance? Any suggestion on how to replace 'NOT EXISTS' in the following query?

2. When should I build that small tables? I have at least 6 of those tables retrieving from the same big table (HISVALUE).

Here is the query:

INSERT INTO temp_sensor

        (SELECT hisv.DATEANDTIME,
        substr(hisv.POINTNAME, instr(hisv.POINTNAME, ':') + 
1,instr(hisv.POINTNAME, ':',1,2) - instr(hisv.POINTNAME, ':') - 1)


        ,substr(hisv.POINTNAME, instr(hisv.POINTNAME, ':',1,2) + 

1,instr(hisv.POINTNAME, ':',1,3) - instr(hisv.POINTNAME, ':', 1, 2) - 1)

,eqlab.EQUIP_LABEL, hisv.VALUE, eqlab.TYPE

        FROM hisvalue hisv, equipment_label eqlab
        WHERE substr(hisv.POINTNAME, 1, instr(hisv.POINTNAME, 
':',1,4)-1) = eqlab.EQUIP_ALIAS
          and (eqlab.EQUIP_LABEL like 'TSN%'
                or eqlab.EQUIP_LABEL like 'RST%')
          and not exists (select 1 from temp_sensor ts
                where ts.DATEANDTIME = hisv.DATEANDTIME
                  and ts.LOCATION = substr(hisv.POINTNAME, 

instr(hisv.POINTNAME, ':') + 1,instr(hisv.POINTNAME, ':',1,2) - instr(his v.POINTNAME, ':') - 1)

                  and ts.SYSTEM = substr(hisv.POINTNAME, 

instr(hisv.POINTNAME, ':',1,2) + 1,instr(hisv.POINTNAME, ':',1,3) - instr(h isv.POINTNAME, ':',1, 2) - 1)

                  and ts.EQUIP_LABEL = eqlab.EQUIP_LABEL
                  and ts.VALUE = hisv.VALUE
                  and ts.TYPE = eqlab.TYPE
                          )
     );

Regards,
Ursula Received on Tue Dec 09 2003 - 03:55:38 CST