我的oracle数据库中的表总共有18个字段,大概行数为4000多万,表中没有主键。其中有字段:编号(编号为1到35000不等),日期,等。我在编号和日期上单独建立了索引,请问怎么快速得到该表中数据的总行数?
我用的语句是:select count(*) from table;
select count(id) from table;
select count(date) from table;速度都是比较慢的。
请问有别的什么快速的方法吗?
我用的语句是:select count(*) from table;
select count(id) from table;
select count(date) from table;速度都是比较慢的。
请问有别的什么快速的方法吗?
试试
SQL> select count(*) from dbzg_khxm_temp; COUNT(*)
---------
12710实际:31
SQL> select MAX(ROWNUM) from dbzg_khxm_temp;MAX(ROWNUM)
-----------
12710实际:47
OPER@tl> select count(object_id) from test
2 where object_id is not null;COUNT(OBJECT_ID)
----------------
50286
执行计划
----------------------------------------------------------
Plan hash value: 2611454708--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 5 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|
|* 2 | INDEX FAST FULL SCAN| IND_TEST | 50286 | 245K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
118 consistent gets
0 physical reads
0 redo size
429 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedOPER@tl> select count(rowid) from test;COUNT(ROWID)
------------
50286
执行计划
----------------------------------------------------------
Plan hash value: 1950795681---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 158 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS FULL| TEST | 50286 | 589K| 158 (1)| 00:00:02 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedOPER@tl>
-----------
50286
执行计划
----------------------------------------------------------
Plan hash value: 1092352300--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | COUNT | | | | |
| 3 | TABLE ACCESS FULL| TEST | 50286 | 158 (1)| 00:00:02 |
--------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedOPER@tl>
这个思路怎样啊!
另外再建一张表,专门用于记录大表的数据记录数量,每次大表插入数据的时候就将其更新(可以通过在大表上建立触发器的方式或者在应用程序中实现),在需要用到记录数的时候,一个简单的select就可以搞定。这样的做法就是将负载平均分布在平时,而不是集中在需要记录数的时候。