I think I need to use Rowid??

From: Joe Condle <condle_at_med.pitt.edu>
Date: Fri, 01 May 1998 10:08:37 -0400
Message-ID: <3549D765.535A@med.pitt.edu>

I have a table with 500,000 records. I want to to do a query in plsql on the table itself.

Example

	Select * 
	From Basic A, BASIC B
	Where A.lname = B.lname
	And A.fname = B.fname
	And A.SSN != B.SSN

I want to do this using a cursor where A is one row of the BASIC table being queried against the B table. Here is a sample of my procedure. It runs ridiculously slow. I think it is the building of the huge cursor c1. Is there some way of incrementing throught the table using rowid or some other incrementer in a cursor without having to build a cursor that equals the table? Thanks in Advance.

Joe Condle
System Engr
Medical ARchival Systems Inc.

	Procedure m111(v_all OUT NUMBER)
	IS
	
	ma basic%ROWTYPE
	mb basic%ROWTYPE
	CURSOR c1 IS
	    SELECT * FROM basic;
	CURSOR c2 IS
		Select * FROM basic
	WHERE ma.lname = basic.lname
	AND   ma.lname = basic.lname
	AND    ma.SSN != basic.SSN;

	i_same NUMBER;

	BEGIN
	   i_same :=0;
                n :=0;	   
           OPEN c1;
	   LOOP
	      FETCH c1 INTO ma;
	      EXIT WHEN c1%NOTFOUND

	      OPEN c2;
	      LOOP
	      	 FETCH c2 INTO mb;
	         EXIT WHEN c1%NOTFOUND

		 INSERT INTO m1111()
		 VALUES()
		 i_same := i_same +1;

                 n := n+1;
		 IF n>100 THEN
                    COMMIT;
		     n:=0;
		 ENDIF:
             END LOOP;
	     CLOSE c2;
           END LOOP
           Close c1;
         END:

Received on Fri May 01 1998 - 09:08:37 CDT