Thank you so much
rambabu konakanchi, April 08, 2016 - 3:46 am UTC
Thank you so much for the response and the help.
Query Rewrites
Rajeshwaran Jeyabal, April 08, 2016 - 5:39 am UTC
Depends on
1) How frequently the data changes on this table wsh_exceptions.
2) What fraction of data changes on this table.
3) The application nature surrounding this table.
We can think of having materialized view in place for query rewrites to make the count(*) to be faster.
rajesh@ORA12C> set autotrace on
rajesh@ORA12C> select count(*) from big_table ;
COUNT(*)
----------
10000000
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41119 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| BIG_TABLE | 10M| 41119 (1)| 00:00:02 |
------------------------------------------------------------------------
Statistics
----------------------------------------------------------
72 recursive calls
0 db block gets
157026 consistent gets
156920 physical reads
0 redo size
542 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
rajesh@ORA12C> create materialized view big_table_mv
2 build immediate
3 refresh on commit
4 enable query rewrite as
5 select mod(object_id,3)+1 x, count(*)
6 from big_table
7 group by mod(object_id,3)+1 ;
Materialized view created.
rajesh@ORA12C> set autotrace on
rajesh@ORA12C> select count(*) from big_table;
COUNT(*)
----------
10000000
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2301198475
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | MAT_VIEW REWRITE ACCESS FULL| BIG_TABLE_MV | 3 | 18 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@ORA12C> set autotrace off
rajesh@ORA12C>But again not sure about logic behind having count(*) - are we going to do something like this?
begin
select count(*) into l_cnt from some_table;
if l_cnt > 0 then
process_data ;
end if;
end;
/if that is the case, then count(*) would be the waste of resources, just process them rather than counting up.
April 08, 2016 - 6:50 am UTC
My first concern is:
For any table with the word "exceptions" in the name, how did it come to pass that there were 1billion to exceptions...to ...well...anything ?
That's an alarm bell right there.
Just one concern
AK, July 29, 2016 - 6:33 pm UTC
I had a very bad experience using just parallel hint without the degree specified in the query hint. Unfortunately, DBA did not apply a limit on parallel degree at table level, since my query didnt had degree specified in the hint, it took a lot of performance hit on the whole DB and every one was screaming. After that experience, I wont suggest anyone to use parallel hint without specifying degree
August 02, 2016 - 4:45 pm UTC
Alternate to count(*)
Paul Baker, August 19, 2016 - 4:55 pm UTC
Thank for this helpful info.. If you do not need up to the minute counts an alternate method would be to use data from the all_tables view:
select TABLE_NAME, num_rows, last_analyzed from all_tables where owner = '{your owner}' order by NUM_ROWS desc;
Another case
Pavel, November 28, 2018 - 12:54 pm UTC
Hello. We have another case.
We use ADF framework and it generates queryes like 'select count(1) from big_table'.
And we can't rewrite this query.
What can we do?
November 29, 2018 - 12:49 am UTC
David DUBOIS, November 29, 2018 - 11:07 am UTC
Hello Ram, You said "Do we have any better way to user select count(*) on these big tables?"
If you are using Oracle 12cr1 or later version, you can use the new function APPROX_COUNT_DISTINCT (col1) to go faster than count( distinct col1).
David