Compare two records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Compare two records

  1. #1
    Join Date
    Jun 2003
    Location
    Vietnam
    Posts
    9

    Compare two records

    Hi all,
    I have a table to store information about one person(firstname,lastname,birthday,sex,address,...). My table have a lot of record (about 1000000 rec). Could you show me how to compare one record with the other in this table(to count the number of the same column)? I have tried to test compare but it very slowly. I think my algorithm is not optimal.
    Can you help me?(show me the algorithm) Thanks!
    Trung

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you give an example of what you want to do?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Please only post your questions in ONE forum!
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Jun 2003
    Location
    Vietnam
    Posts
    9
    for example: I have a table Customer(firstname varchar2(20),
    lastname varchar2(20),
    sex number,
    address varchar2(255)
    .......................)
    I want compare a record with other record in this table. I write a function:
    Function Compare(rowid1 varchar2,rowid2 varchar2) return number is
    str1 varchar2(255);
    str2 varchar2(255);
    v_value1 varchar2(100);
    v_value2 varchar2(100);
    n_count number:=0;
    Begin
    str1:='select firstname from customer where rowid=''rowid1''';
    str2:='select firstname from customer where rowid=''rowid2''';
    execute str1 into v_value1;
    execute str1 into v_value2;
    if str1=str2 then
    n_count:=n_count+1;
    end if;
    --similar with other column: lastname,sex,address...
    return n_count;
    End;

    But when the number of record is too big(about 1000000 record) then the speed is very bad, very slow. Can you show me another algorithm?
    Thanks!
    Trung

  5. #5
    Join Date
    Mar 2003
    Posts
    60
    Do a self join - should run faster than PL SQL:

    select count(a.rowid) from cust a, cust b
    where a.fname = b.fname

    This should give all rows with same fname. try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width