CODEWITHSUNDEEP
sqloracle-database
teepu
teepu

Reputation: 276

Find total count from a sql query

I have a sql query with where condition as ROWNUM=10. And the query result i store it in one GTT table. But it is possible that the sql query is fetching more records than the mentioned WHERE condition i.e. ROWNUM=10.

So i wanted to know, is the query fetching >10 records are not.

This i can achieve by executing the same query twice i.e. once time to know the count and second time to insert the records into the gtt table.

But it is not an good idea to run the query twice.

So can any one help me to find the count of the sql query with out executing it twice.

Upvotes: 1

Views: 405

Answers (3)

Alex Poole
Alex Poole

Reputation: 191275

You could open a cursor for the query without the rownum condition and fetch until you run out of data or hit an 11th row:

declare
  l_query varchar2(4000) := '<your query without rownum condition>';
  l_counter pls_integer := 0;
  l_cursor sys_refcursor;
  l_row gtt%rowtype;
begin
  open l_cursor for l_query;
  loop
    fetch l_cursor into l_row;
    exit when l_cursor%notfound;

    l_counter := l_counter + 1;
    if l_counter > 10 then
      dbms_output.put_line('Query got more than 10 rows');
      exit;
    end if;

    -- first 1-10 rows so insert
    insert into gtt values l_row;
  end loop;
end;
/

Or with a collection to make it slightly more efficient:

declare
  l_query varchar2(4000) := '<your query without rownum condition>';
  l_cursor sys_refcursor;
  type t_tab is table of gtt%rowtype;
  l_tab t_tab;
begin
  open l_cursor for l_query;
  -- fetch gets at most 10 rows
  fetch l_cursor bulk collect into l_tab limit 10;
  -- up to 10 rows found are inserted
  forall i in 1..l_tab.count
    insert into gtt values l_tab(i);

  -- fetch again, if it finds anything then total row count > 10
  fetch l_cursor bulk collect into l_tab limit 1;
  if l_cursor%found then
    dbms_output.put_line('Query got more than 10 rows');
  end if;

  close l_cursor;
end;
/

However, the optimiser can often use a rownum condition to reduce the work it has to do (via a stopkey which you can see in the execution plan). It might still be faster and more efficient to run the query twice, once with the 10-row limit for the insert, and again with an 11-row limit to just get the count, and see if that is 11 or not. You should test both approaches to see which is actually better for you data. (And any others that are suggested, of course!)

Upvotes: 0

yılmaz
yılmaz

Reputation: 1826

You can use the solution found at https://stackoverflow.com/a/17206119/7676742 to get records and count of these records together.

SELECT COUNT(*) OVER (), c.*
FROM CUSTOMER c 
WHERE c.Name like 'foo%';

Upvotes: 0

Alex Zen
Alex Zen

Reputation: 926

If you are inserting those records in the GTT table, and you want to know how many rows you have selected/inserted, you could use SQL%ROWCOUNT

Begin
    INSERT INTO GTT_TABLE
         SELECT *
           FROM QUERY_VIEW
          WHERE Condition() = '1';

    If SQL%ROWCOUNT > 10 Then
        dbms_output.put_line('Query returns ' || SQL%ROWCOUNT || ' rows.');
    End if;
End;

Upvotes: 3

Related Questions