Re: Improve query performance

There's not much we can do with the (lack of) info you provide. Please give us at least the Oracle version and OPTIMIZER_MODE, as well as the execution plan. What I've seen so far is that NOT EXISTS performs much better than NOT IN, but that depends on the volumes of data, of course.

Daniel

> 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 - 09:49:34 CST