Re: I think I need to use Rowid??

Joe,

    First, try creating an index on lname, fname, SSN. i.e.

CREATE UNIQUE INDEX basic_ix ON basic(lname, fname, SSN) tablespace IDX_TBS;

If you are trying to get total number of SSNs with same names you can try

SELECT SUM(t1.cnt_same)
FROM
(SELECT lname, fname, count(SSN) cnt_same  from basic
 group by lname, fname
 having count(SSN) > 1) t1;

Indexing should make it run lot faster.

raghuvir

Joe Condle <condle_at_med.pitt.edu> wrote in article <3549D765.535A_at_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 Sat May 02 1998 - 22:18:39 CDT