Improve query performance

Ursula Lee ursula....@thales-is.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

"Noel" t...@go2.pl

Yikes! I'm not even close to rebuild it with set operation.
But that's what i would do.
I give a simple example: 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 ); Sorry, but it would be take ages me to rebuild your query.
I have to do some views first.
--
Noel

"Alkos" aze...@nospam.org

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

danielroy10j ...@hotmail.com (Daniel Roy)

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

Sybrand Bakker gooidit...@sybrandb.nospam.demon.nl

According to Thomas Kyte in his latest book this is a myth.
--
Sybrand Bakker, Senior Oracle DBA

Daniel Morgan damor...@x.washington.edu

I've repeatedly proven it is a myth. And even when it wasn't ... the result was entirely dependent upon on the arrangement of the larger and smaller tables.
--
Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damor...@x.washington.edu (replace 'x' with a 'u' to reply)

"Anurag Varma" av...@hotmail.com

Daniel, Its a myth.
A "not in" query might perform differently with RBO.
With CBO and all tables analyzed, "NOT IN" is executed the same exact way as "NOT EXISTS".
I've tried it a lot of times and find this to be true 8i onwards.
In fact Harrison notes this fact in this SQL Tuning book.
Do you have a working example to prove this the other way?
Anurag

"Anurag Varma" av...@hotmail.com

--snip--
--snip--
I'm referring to Daniel Roy here .. :)

"VC" boston...@hotmail.com

Hello Anurag, ...
drop table t1; drop table t2; create table t1 as select * from all_objects; create index t1_idx on t1(object_id); create table t2 as select * from all_objects where rownum <= 500; analyze table t1 compute statistics; analyze table t1 compute statistics; set autot trace explain select * from t2 where object_id not in (select object_id from t1); Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=132) 1 0 HASH JOIN (ANTI) (Cost=12 Card=1 Bytes=132) 2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=899 Bytes=115072) 3 1 INDEX (FAST FULL SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=7 Card=25324 Bytes=101296) select * from t2 where not exists (select 1 from t1 where object_id=t2.object_id); Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=45 Bytes=5760) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=45 Bytes=5760) 3 1 INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card =1 Bytes=4) Rgds.

"Anurag Varma" av...@hotmail.com

Well I'll admit that proves my statement incorrect.
Although in most cases I've seen, the execution plans were identical.
In this case the not in is actually performing better and if the CBO was smarter it would/should have chosen the "not in" plan for the "not exists" query considering the join columns are "not null", which makes the two queries identical.
Anurag

Ursula Lee ursula....@thales-is.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 Morgan damor...@x.washington.edu

I said "I've repeatedly proven it is a myth".
Of that there is no question. It took creating special conditions in older versions of Oracle under RBO for it to be otherwise.
--
Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damor...@x.washington.edu (replace 'x' with a 'u' to reply)

Daniel Morgan damor...@x.washington.edu

Aha. Sorry I didn't see this first.
--
Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damor...@x.washington.edu (replace 'x' with a 'u' to reply)

Daniel Morgan damor...@x.washington.edu

In my testing the execution plans are often different but that doesn't change the fact that it is a myth. I have a test case I use with my students that contains the following three statements: SELECT srvr_id FROM servers WHERE srvr_id IN ( SELECT srvr_id FROM serv_inst); SELECT srvr_id FROM servers s WHERE EXISTS ( SELECT srvr_id FROM serv_inst i WHERE s.srvr_id = i.srvr_id); SELECT DISTINCT srvr_id FROM servers WHERE srvr_id NOT IN ( SELECT srvr_id FROM servers MINUS SELECT srvr_id FROM serv_inst); All have different plans and I don't think anyone would want to guess which has the lower cost.
--
Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damor...@x.washington.edu (replace 'x' with a 'u' to reply)

Daniel Morgan damor...@x.washington.edu

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 damor...@x.washington.edu (replace 'x' with a 'u' to reply)

"Niall Litchfield" n-litchfi...@audit-commission.gov.uk

does select
--
Niall Litchfield Oracle DBA Audit Commission UK ...
what about SELECT s.srvr_id from servers s,serv_inst i where s.srvr_id=i.srvr_id; for you or SELECT s.srvr_id from servers s,serv_inst i where s.srvr_id=i.srvr_id(+) and i.srvr_id is null; for the not in? (and yes I probably have got the outer join wrong).
--
Niall Litchfield Oracle DBA Audit Commission UK

"Niall Litchfield" n-litchfi...@audit-commission.gov.uk

ora-01043
--
Niall Litchfield Oracle DBA Audit Commission UK ...

To Top

Improve query performance

Back to Audit Commission

Back to Home Page