Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Improve query performance
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