Re: Improve query performance

Ursula Lee <ursula.lee_at_thales-is.com> wrote in message news:<br466r$hua19_at_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

get rid of the insert. Do a 'create table as'. do nologging if you can afford to not have the data recoverable(if this is a nightly load, its fine). if ytou can scale it use the parallel option.

insert is slow. especially if you have alot of indexes on the table. Then use dbms_job and create all the indexes on your new table in parallel. Just submit them as jobs. you can create those in parallel also.

your query doesnt tell me anything. there is no explain plan. I dont know anything about the number of records in each table and other stuff.

easiest way to speed up an insert is to turn it into a create table as. Now keep in mind, if you are doing alot of small inserts, do not use create table as statements, it wont scale up to accomodate a large number of people doing that. but for one or a small number of loads its not a problem. Received on Wed Dec 10 2003 - 07:20:49 CST