Re: Improve query performance

From: Ursula Lee <ursula.lee_at_thales-is.com>
Date: Wed, 10 Dec 2003 16:18:23 +0800
Message-ID: <br6ktm$kdq721@imsp212.netvigator.com>

Thanks all for your input.

We are running Oracle 9i Database (Release 9.2.0.1.0). But I don't know the optimizer mode nor trying the execution plan as I am not the DBA and we have no DBA around.

I need to use substring in my query because the column contains nested value which requires to extract data identified by colon. Let's say I create another column on the big table, but still requires to use substring to fill in the new column?

Also, the small tables are used to speed up the query time of reporting as those tables are used for reporting purpose from JAVA applet. With the use of small tables, this will avoid the building time using substring and joining tables, that's why I need to pick a better time to build up those small tables.

If it is required to run the execution plan before any suggestion, I will try to do this.

BTW, Noel suggests to use this syntax instead, any ideas?

SELECT *

     FROM TABLEA A
      WHERE NOT EXISTS
      (
      SELECT 1
           FROM TABLEB B
        WHERE B.ID = A.ID

    );

might be replaced with:

SELECT *
   FROM TABLEA
  WHERE ID IN SELECT A.ID ID
       FROM TABLEA
   MINUS
  SELECT B.ID
      FROM TABLEB
  );

Daniel Roy wrote:

> 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 Wed Dec 10 2003 - 02:18:23 CST