Re: Improve query performance

"Ursula Lee" <ursula.lee_at_thales-is.com> a écrit dans le message news: br468d$hua20_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
>

Hi Ursula,

1)
Your query contains join predicates using string manipulation functions (instr, substr).
Unless you defined function-based indices, this way to do prevents Oracle from
retrieving rowid from any index (as far as I know). Maybe this causes the bad throughput you get. Consider defining function-based indices on your big table, this will require
a bit designing and testing, I think.
Another remark :
Use of instr+substr leads me to think that columns may contain multivalues that is a flaw according to normalization theory. (I would ask Daniel Morgan to confirm)

2)
Typically, this kind of job is done when real users aren't connected to the DB.
If your app is not a 24/24 7/7, consider planning these tasks within ranges of low activity. You can either use cron, DBMS_JOB or a tier product (CA Unicenter ou Orsyp $U) to start your batches (hope you run UNIX ;))

HTH

--
Cheers,
Alkos