Home » SQL & PL/SQL » SQL & PL/SQL » Tuning Help - Can this be sped up??
Tuning Help - Can this be sped up?? [message #199502] Tue, 24 October 2006 16:57 Go to next message
psmith8909@aol.com
Messages: 8
Registered: October 2006
Location: Las Vegas
Junior Member
I am trying to assign groups of distinct records different ids (group). I haven written a procedure that loops through the table and assign records in the same group the same ID. The procedure is running very slow. Can someone offer pointers to possibly speed the process up? Below is part of the code and a sample of the data. Thanks.

Data
OBJECT_NAME TYPE GROUP_ID
-------------------- ---- ---------
6-fl90.-dwg acad 1
6-fl90.-dwg acad 1
A-102-1 acad 2
A-102-1 acad 2
A-102-1 acad 2
A-102-1 acad 2
A-102-1--[01] acad 3
A-102-1--[01] acad 3
A-105-1 tiff 4

Procedure
cursor ngroup is
select seq_id, object_name, a_content_type
from temp1
where group_id is null
order by seq_id;

cursor test is
select distinct object_name, a_content_type
from temp1
order by object_name, a_content_type;

BEGIN

for test_recs in test loop

v_cnt := v_cnt + 1;
v_objname := test_recs.object_name;
v_conttype := test_recs.a_content_type;

for ngroup_recs in ngroup loop

if ngroup_recs.object_name = v_objname and ngroup_recs.a_content_type = v_conttype then
update temp1
set group_id = v_cnt
where object_name = v_objname
and a_content_type = v_conttype;

end if;

end loop;

commit;

end loop;

END;

[Updated on: Tue, 24 October 2006 17:02]

Report message to a moderator

Re: Tuning Help - Can this be sped up?? [message #199511 is a reply to message #199502] Tue, 24 October 2006 21:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try searching the board. I found this.

Ross Leishman
Re: Tuning Help - Can this be sped up?? [message #199530 is a reply to message #199511] Wed, 25 October 2006 00:55 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This will not help solving your problem, but - perhaps just an observation: don't commit within the loop - commit at the end of the whole procedure (unless this is really what you want). Frequent committing might lead to errors (ORA-01555 - snapshot too old).
Re: Tuning Help - Can this be sped up?? [message #199849 is a reply to message #199530] Thu, 26 October 2006 07:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Bulk collect perhaps ?
Re: Tuning Help - Can this be sped up?? [message #199896 is a reply to message #199849] Thu, 26 October 2006 11:10 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check it out...

SQL> create table test
2 (colname varchar2(10),
3 colval varchar2(10),
4 sno number(4)
5 )
6 /

Table created.

SQL>
SQL> insert into test values ('AAA','123',1)
2 /

1 row created.

SQL> insert into test values ('AAA','123',1)
2 /

1 row created.

SQL> insert into test values ('BBB','456',2)
2 /

1 row created.

SQL> insert into test values ('BBc','456',3)
2 /

1 row created.

SQL> insert into test (colname, colval) values ('CDE','567')
2 /

1 row created.

SQL> insert into test (colname, colval) values ('CDE','567')
2 /

1 row created.

SQL> insert into test (colname, colval) values ('CDE','567')
2 /

1 row created.

SQL> insert into test (colname, colval) values ('CDE','678')
2 /

1 row created.

SQL> insert into test (colname, colval) values ('DEF','789')
2 /

1 row created.

SQL> select * from test
2 /

COLNAME COLVAL SNO
---------- ---------- ------------------
AAA 123 1
AAA 123 1
BBB 456 2
BBc 456 3
CDE 567
CDE 567
CDE 567
CDE 678
DEF 789

9 rows selected.

SQL> merge into test
2 using (select colname, colval, rownum as rno from (select colname, colval from test where sno is null group by colname, colval order by 1,2)) b
3 on (test.colname = b.colname and test.colval = b.colval)
4 when matched then update set test.sno = b.rno
5 when not matched then insert (test.colname, test.colval) values (NULL,NULL)
6 /

5 rows merged.

SQL> select * from test
2 /

COLNAME COLVAL SNO
---------- ---------- ------------------
AAA 123 1
AAA 123 1
BBB 456 2
BBc 456 3
CDE 567 1
CDE 567 1
CDE 567 1
CDE 678 2
DEF 789 3

9 rows selected.

SQL>

You have to tune it to meet your requirement. Also assumption here is you are on oracle 9i and later.

My suggestion or what i have learnt from Oracle Guru Thomas Kyte (Tom) is the following mantra :

a) Do it in sql
b) not possible do it in pl/sql
c) not posisble do it in java proc
d) not possible do it in c or proc
e) not possible read your requirement again and try to fine tune it.

Good Luck
Re: Tuning Help - Can this be sped up?? [message #199909 is a reply to message #199896] Thu, 26 October 2006 14:41 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Another Tom fan here ("analytics rule"):

SQL> select * from your_table;

OBJECT_NAME          TYPE         GROUP_ID
-------------------- ---------- ----------
6-fl90.-dwg          acad       
6-fl90.-dwg          acad       
A-102-1              acad       
A-102-1              acad       
A-102-1              acad       
A-102-1              acad       
A-102-1--[01]        acad       
A-102-1--[01]        acad       
A-105-1              tiff       

9 rows selected

SQL> SELECT sub.object_name
  2        ,sub.TYPE
  3        ,dense_rank() over(PARTITION BY sub.total ORDER BY sub.object_name, sub.TYPE)
  4  FROM   (SELECT t.object_name
  5                ,t.TYPE
  6                ,'dummy' total
  7          FROM   your_table t) sub
  8  /

OBJECT_NAME          TYPE       DENSE_RANK()OVER(PARTITIONBYSU
-------------------- ---------- ------------------------------
6-fl90.-dwg          acad                                    1
6-fl90.-dwg          acad                                    1
A-102-1              acad                                    2
A-102-1              acad                                    2
A-102-1              acad                                    2
A-102-1              acad                                    2
A-102-1--[01]        acad                                    3
A-102-1--[01]        acad                                    3
A-105-1              tiff                                    4

9 rows selected

Regards,
Sabine
Previous Topic: Occupied space from Data Dictionary
Next Topic: materialized view issues
Goto Forum:
  


Current Time: Wed Dec 07 06:35:38 CST 2016

Total time taken to generate the page: 0.05053 seconds