This discussion is archived
10 Replies Latest reply: Jun 16, 2012 7:55 AM by indra budiantho RSS

Alternative to select count..

942476 Newbie
Currently Being Moderated
Hi folks,

I'm creating this procedure which needs to check if a value exists in a certain table.
If it exists, the data inside needs to be copied, if not, default data will be given instead.

This is a scrap of the code:

V_SQL := 'SELECT COUNT(ID) FROM ' || V_OLD_TABLE_LIST(J) || ' WHERE ' || V_OLD_COLUMN_LIST(J) || ' IS NOT NULL;
EXECUTE IMMEDIATE V_SQL INTO V_COUNT;
IF V_COUNT > 0 THEN
-- do other stuff
END IF;


Now, the above statement is encapsuled by a for loop. This loop contains thousands of values.

This is causing the procedure to be very slow. The thing taking the most time in my procedure, is the 'select count...' part.

Is there any alternative to this, which is much more performant?

Many thanks in advance!

Zjel
  • 1. Re: Alternative to select count..
    sybrand_b Guru
    Currently Being Moderated
    The code you posted is really, really horrible.

    First of all, PL/SQL was invented to avoid parsing. Your code is using execute immediate, so parses always
    This explain much of the 'slowness'.
    Secondly 'IS NOT NULL' results in a FULL TABLE scan.

    Is there an alternative?

    Sure there is: Remove this code, and/or replace the developer who is responsible for this disaster, by someone who knows Oracle.

    ---------------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Alternative to select count..
    PD81 Explorer
    Currently Being Moderated
    Rather than counting every row, stop when you find the first record
    Try this:
    i) lose the COUNT(ID)
    ii) add a ROWNUM = 1 clasue

    V_SQL := 'SELECT 1 FROM ' || V_OLD_TABLE_LIST(J) || ' WHERE ' || V_OLD_COLUMN_LIST(J) || ' IS NOT NULL AND ROWNUM = 1;
    EXECUTE IMMEDIATE V_SQL INTO V_COUNT;
    IF V_COUNT > 0 THEN
    -- do other stuff
    END IF;
  • 3. Re: Alternative to select count..
    ShankarViji Pro
    Currently Being Moderated
    Hi,

    Welcome to Forum !!!!!!!!!!!!!!!!!
    DECLARE
       v_temp   NUMBER;
    BEGIN
       FOR i IN 1 ..50
       LOOP
          EXECUTE IMMEDIATE 'SELECT ROWNUM FROM emp where deptno IS NOT NULL and Rownum=1'
             INTO v_temp; 
         DBMS_OUTPUT.put_line (v_temp);
    
          IF v_temp > 0
          THEN
             DBMS_OUTPUT.put_line ('Record Exists');         ---Process Here
          ELSE
         --    EXIT;
              NULL;
    
             DBMS_OUTPUT.put_line ('Record Not Exists'); 
          END IF;
       END LOOP;
    END;
    Thanks,
    Shankar
  • 4. Re: Alternative to select count..
    942476 Newbie
    Currently Being Moderated
    Thanks for your replies.

    Altough the suggested code will work, it's not the answer I was looking for.

    I simply want to know if there exists something like

    IF(do-a-select-statement-here-to-check-if-a-condition-is-true) THEN


    END IF;

    ... that would be more performant then a "select count into" statement.


    Cheers,
  • 5. Re: Alternative to select count..
    William Robertson Oracle ACE
    Currently Being Moderated
    My guess is the time is being spent in counting the rows, and a more elegant PL/SQL syntax, if there were one, would do nothing to change this.
  • 6. Re: Alternative to select count..
    AdamMartin Pro
    Currently Being Moderated
    I simply want to know if there exists something like

    IF(do-a-select-statement-here-to-check-if-a-condition-is-true) THEN
    END IF;
    Well, you can put the count logic into a function, which might make the main flow easier to read. For example, create this function:
    create or replace function my_function(p_table varchar2, p_column varchar2) return boolean as
      v_cnt number;
    begin
      execute immediate 'select count(*) 
                         from dual
                         where exists (select null
                                       from '||p_table||'
                                       where '||p_column||' is not null
                                       and ID is not null)'
      into v_cnt;                                   
      if v_cnt = 1 then 
        return true;
      else
        return false;
      end if;
    end;
    And then call it like this:
    begin
      if my_function(V_OLD_TABLE_LIST(J),V_OLD_COLUMN_LIST(J)) then
        dbms_output.put_line('Do something.');
      else
        dbms_output.put_line('Nothing.');
      end if;
    end;
    Obviously it is still doing a count, but it is doing it in the function. Also, by selecting from dual where exists... ensures that you stop counting when you reach one row. That's all you really need. Don't waste your time counting all of the rows if you just want to know if there is at least one.

    Note that I included "and ID is not null" because you were selecting count(ID) instead of count(*), and I wanted to make the query equivalent to your original. But it seems to me that ID is probably a primary key or at least a not null field. If that is true then you can take off "and ID is not null" and it will still work. It will work with it there too, but it's better to be concise where possible.
  • 7. Re: Alternative to select count..
    rp0428 Guru
    Currently Being Moderated
    >
    I'm creating this procedure which needs to check if a value exists in a certain table.
    If it exists, the data inside needs to be copied, if not, default data will be given instead.

    This is a scrap of the code:

    V_SQL := 'SELECT COUNT(ID) FROM ' || V_OLD_TABLE_LIST(J) || ' WHERE ' || V_OLD_COLUMN_LIST(J) || ' IS NOT NULL;
    >
    You aren't giving us all of the information.

    You said 'if a value exists' but your code is not checking 'a value' it is checking whether ANY row has a value for that column. The only way Oracle can know if there is any row that has a value for that column is to use an index on that column (nulls will not be in the index) or a full table scan until it finds a value. Other responders have already shown you how to stop the query after one row is found.

    You implied that 'if a row' has a value you want to copy it but your COUNT query won't copy anything. So what is it that you intend to copy? One value? Or data in every row that has that value?

    If the latter then you could use a CASE statement in your query of the table to use the table value if it is not null and use a default if it is

    Unless you provide information about what '-- do other stuff' is actually doing the only help you can get is what has already been provided.
  • 8. Re: Alternative to select count..
    Etbin Guru
    Currently Being Moderated
    Instead of
    IF (do-a-select-statement-here-to-check-if-a-condition-is-true) 
    THEN
      do-what-has-to be-done-when-above-condition-is-true;
    ELSE
      report-condition-is-false;
    END IF;
    try
    BEGIN
      select/insert/update/delete
      where condition; /* condition-is-true */
    
      IF not-done
      THEN
        report-not-done; /* condition-is-false */
      END IF;
    
    EXCEPTION  /* if required */
      report-exception;
    END;
    Regards

    Etbin
  • 9. Re: Alternative to select count..
    Hoek Guru
    Currently Being Moderated
    Is there any alternative to this, which is much more performant?
    Why bother counting? Rethink...
    http://tkyte.blogspot.nl/2008/12/doing-it-wrong.html
  • 10. Re: Alternative to select count..
    indra budiantho Expert
    Currently Being Moderated
    +IF(do-a-select-statement-here-to-check-if-a-condition-is-true) THEN
    END IF;+
    /* Formatted on 2012/06/16 21:53 (Formatter Plus v4.8.8) */
    DECLARE
    BEGIN
       FOR cur IN (SELECT 2
                     FROM DUAL)
       LOOP
          -- do if TRUE
          EXIT;
       END LOOP;
    END;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points