Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Improve query performance

Re: Improve query performance

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 10 Dec 2003 08:58:01 -0800
Message-ID: <1071075514.479843@yasure>


Ursula Lee wrote:

> 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

>
>

If by nested value you mean you must substring out part of a string because it has intrinsic value then you have a bad design. So the statement that you "need' to you substring is incorrect. You "need" to fix the design.

Yes you may need to substring the data out to load a new column. But that requires substringing once for each row. The way you have it now you must substring every time the row is accessed forever.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Dec 10 2003 - 10:58:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US