Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: SQL Optimization

    Hi, I am using a Cursor for the following SQL statement.
    This SQL statement supposes to get me only the rows that are duplicated in the database.
    The problem with this statement is that it takes more then 10 sec to run it on a db with more then 200,000 records.
    Instead of going on the index it goes over the full table, probably because the sub select.

    I wanted to know if some one has a way to make it faster.
    The statement is:


    Code:
    select accession_number from big_table d where rowid > 
       (select min(rowid) from big_table where
       accession_number=d.accession_number);

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select id
    from tableA ta
    group by id
    having count(*) >1;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Post

    Rowid's change:
    Review http://asktom.oracle.com/pls/ask/f?p...A:371566648146,

    Have you considered using analytics for the min?
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    about

    Code:
    select id
    from tableA ta
    group by id
    having count(*) >1;
    I've traced it and it still goes on the full table.

    about the rowid article, well, I changed the key from rowid to an indexed column and still it went all over the full table probelby coz of the sub select, so it didn't really help to make it faster.

    some one have another idea?

    Thanks!

  5. #5
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Post

    What is the oracle release? What optimizer mode? What are your initalization parameters that favor full table scans vs. indexes?
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

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