优化 count(*) 语句

Question

select count(*) from entable where ( contains(name,'公司')>0 ) and flag=1;其中name字段建有全文索引，flag字段建有位图索引，如果单独执行查询不进行count(*),速度还是很快的。但执行count(*)
后速度大幅降低。也试过用count(PK),但效果不佳。请问各位应该怎样优化呢？？

Answer 1

select count(rowid) from entable where flag = 1 and name like '%公司%';

Answer 2

select count(1) from entable where flag = 1 and name like '%公司%'; 

Answer 3

select count(1) from table where flag =1 and regexp_like(name,'公司')
估计没有LS写的执行效率高

Answer 4

COUNT（1）和COUNT（PK）、COUNT（*）的效果基本上是一样的

Answer 5

1楼这样看看，速度怎么样！
select count(rowid) from entable where flag = 1 and name like '%公司%';

Answer 6

这是执行计划
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11937 Card=1 Bytes
          =43)   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ENTERPRISEBASEINFOCOLL
          ECT' (TABLE) (Cost=11937 Card=32083 Bytes=1379569)   3    2       DOMAIN INDEX OF 'IND_ENTERPRISE_NAME' (INDEX (DOMAIN))
           (Cost=11937)统计信息
----------------------------------------------------------
       1890  recursive calls
          0  db block gets
     263420  consistent gets
      34561  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         31  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 7

1. 建索引
create index idx_xxx on entable(name, flag);2. 分析表
begin dbms_status.gather_table_status(user, 'entable', cascade=>true);3. 结果
你的count()走这个索引，肯定快

Answer 8

看看这个以前总结的帖http://www.inthirties.com/thread-112-1-1.htmlvc555大侠的详细分析过程，我总结到上面的帖里仂

Answer 9

select count(*) from entable where ( contains(name,'公司')>0 ) and flag=1;其中name字段建有全文索引，flag字段建有位图索引，name和flag字段建了联合索引我测试
 如果只保留flag=1条件需要 0.075秒
 如果保留 ( contains(name,'公司')>0 )条件需要9.7秒但是如果同时加上两个条件需要 111秒，怀疑此时count没有走索引，有没有在count时走索引的方法呢？？

Answer 10

count(rowid) 确实是快了一些，但很有限大概5-6秒。

Answer 11

楼上贴子里的我看了，可那是不带where条件的。

Answer 12

contains?这个9i都没有这个函数,你自己定义的?

Answer 13

///////////两个where条件都加时的执行计划///////////////////////////
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1415832 Card=1 Byt
          es=43)   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ENTERPRISEBASEINFOCOLL
          ECT' (TABLE) (Cost=1415832 Card=7608613 Bytes=327170359)   3    2       DOMAIN INDEX OF 'IND_ENTERPRISE_NAME' (INDEX (DOMAIN))
           (Cost=1415832)统计信息
----------------------------------------------------------
        221  recursive calls
          0  db block gets
     751773  consistent gets
     659392  physical reads
          0  redo size
        350  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 14

//////只保留一个查询条件时的执行计划/////////////////////////
执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1415832 Card=1 Byt
          es=41)   1    0   SORT (AGGREGATE)
   2    1     DOMAIN INDEX OF 'IND_ENTERPRISE_NAME' (INDEX (DOMAIN)) (
          Cost=1415832 Card=8491132 Bytes=348136412)统计信息
----------------------------------------------------------
        208  recursive calls
          0  db block gets
     102209  consistent gets
       9649  physical reads
          0  redo size
        350  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 15

楼主无视我给的方案？ 你的SQL不用改，加个索引即可。试过否？

Answer 16

在需要count的字段上加索引了么？

Answer 17

我按你的方法做过，但在count时不会走这个索引，我觉得可能我的查询中用到了全文索引和普通索引，造成oralce无法决定，另外请教一下 分析表是什么意思？？可以强制执行索引吗？？能具体说说分析表的语法吗？

Answer 18

DBMS_STATUS 这个在10g中是否还存在？

Answer 19

select count(*) from entable where ( contains(name,'公司')>0 ) and flag=1; 
这个语句中name字段是用不到索引的吧.
因为name放到了函数中.

Answer 20

两个字段上面都是索引的建议要使用!
select /*+index_combine(entable,name索引名字，flag索引名字)*/count(*) from entable where ( contains(name,'公司')>0 ) and flag=1;

Answer 21

31楼的应该到网上搜下：inter Media Oracle。你会知道你说的是错误的！

Answer 22

我随便作了一下测试，如果楼主的要求不变，执行计划倒是没问题的。
SQL> CREATE TABLE ENTABLE 
  2   ( ID integer
  3   , NAME CLOB
  4   , FLAG INTEGER);
Table created.SQL> CREATE INDEX IDX_ENTABLE_FLAG ON ENTABLE (FLAG);
Index created.SQL> CREATE INDEX IDX_ENTABLE_NAME ON ENTABLE (NAME) 
  2   INDEXTYPE IS CTXSYS.CONTEXT ;
Index created.SQL> insert into entable
  2  select rownum, object_name, mod(rownum,3) from all_objects;
40809 rows created.SQL> commit;
Commit complete.SQL> execute ctx_ddl.sync_index('idx_entable_name');
PL/SQL procedure successfully completed.SQL> execute DBMS_STATS.GATHER_TABLE_STATS(user,'ENTABLE',CASCADE=>TRUE);
PL/SQL procedure successfully completed.SQL> set linesize 999
SQL> set pagesize 999
SQL> set autotrace on
SQL> set timing on
SQL> select count(*)
  2  from entable
  3  where contains(name,'entable')>1 and flag=1;
  COUNT(*)
----------
         3
Elapsed: 00:00:00.11Execution Plan
----------------------------------------------------------
Plan hash value: 2195943076-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |   159 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                  |     1 |   159 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| ENTABLE          |    25 |  3975 |    16   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | IDX_ENTABLE_NAME |       |       |    16   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("FLAG"=1)
   3 - access("CTXSYS"."CONTAINS"("NAME",'entable')>1)
Statistics
----------------------------------------------------------
        169  recursive calls
          0  db block gets
        327  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 23

如果能够避免使用全文搜索，转而使用like '%公司%'条件的话，可以从组合index入手。
create index idx_entable_name_flag on entable (flag, lower(to_char(name)));
注意，contains是忽略大小写的，所以组合index是function-based的，这里我用lower+to_char完成clob到varchar2的转换并忽略大小写。查询就变成，
SQL> select count(flag)
  2  from entable
  3  where lower(to_char(name)) like '%entable%' and flag=1;COUNT(FLAG)
-----------
          3Elapsed: 00:00:00.07Execution Plan
----------------------------------------------------------
Plan hash value: 2268258602-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 |   156 |    52   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                       |     1 |   156 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_ENTABLE_NAME_FLAG |   680 |   103K|    52   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("FLAG"=1 AND LOWER(TO_CHAR("NAME")) LIKE '%entable%')
Statistics
----------------------------------------------------------
          2  recursive calls
          2  db block gets
        490  consistent gets
          0  physical reads
        256  redo size
        518  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
这样，该查询只需要对拥有两个列的index做一次全扫描，相对于表扫描，效率会高很多。我最近做的项目优化中有对150万记录的一个大表（120个列）作like '%xxx%'搜索的要求，使用该方案使查询时间降到5秒以下。
注意，要使用function-based index必须保证所有条件中和select list中用到的列（包括函数）必须存在于该index中。

Answer 24

如果必须用到全文搜索，也就是说查询条件必须用contains，无法转化为like '%公司%'的话，那就只能从分析现有的执行计划入手了。
最大的问题是数据读的量都很大，特别是physical reads
    751773  consistent gets
    659392  physical reads可能造成的原因很多
- 表中列太多
- 表中数据chain block太多
- 满足contains条件的记录太多
- 数据库db_block_size太小
- db_block_buffer太小建议楼主贴出详细的建表、索引脚本，大致的数据量，数据库参数，我们可以继续探讨。

Answer 25

这是整个的建表语句：
-- Create table
create table ENTERPRISEBASEINFOCOLLECT
(
  PRIPID          VARCHAR2(36),
  ENTNAME         VARCHAR2(200),
  REGNO           VARCHAR2(50),
  ENTTYPE         VARCHAR2(64),
  INDUSTRYPHY     VARCHAR2(64),
  INDUSTRYCO      VARCHAR2(64),
  ABUITEM         VARCHAR2(1000),
  CBUITEM         VARCHAR2(4000),
  OPFROM          DATE,
  OPTO            DATE,
  POSTALCODE      VARCHAR2(200),
  TEL             VARCHAR2(200),
  LOCALADM        VARCHAR2(64),
  CREDLEVEL       VARCHAR2(64),
  ESDATE          DATE,
  APPRDATE        DATE,
  REGORG          VARCHAR2(64),
  ENTCAT          VARCHAR2(64),
  ENTSTATUS       VARCHAR2(64),
  REGCAP          NUMBER(26,6),
  OPSCOPE         VARCHAR2(3000),
  OPSCOANDFORM    VARCHAR2(3000),
  DOMDISTRICT     VARCHAR2(64),
  DOM             VARCHAR2(600),
  RECCAP          NUMBER(26,6),
  PROTYPE         VARCHAR2(64),
  REGCAPCUR       VARCHAR2(64),
  S_EXT_NODENUM   VARCHAR2(40),
  S_EXT_SEQUENCE  VARCHAR2(32),
  S_EXT_VALIDFLAG VARCHAR2(1),
  S_EXT_TIMESTAMP DATE,
  TABID           NUMBER(10) not null
)
tablespace NDB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 4096M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ENTERPRISEBASEINFOCOLLECT
  add constraint PK_ENTERPRISEBASEINFOCOLLECT primary key (TABID)
  using index 
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index ENTINDEX on ENTERPRISEBASEINFOCOLLECT (PRIPID,S_EXT_VALIDFLAG)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 584M
    minextents 1
    maxextents unlimited
  );
create index INDEX_ENTERPRISE_ENTNAME on ENTERPRISEBASEINFOCOLLECT (ENTNAME)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_ENTERPRISE_PRIPID on ENTERPRISEBASEINFOCOLLECT (PRIPID)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_ENTERPRISE_PS on ENTERPRISEBASEINFOCOLLECT (PRIPID,S_EXT_NODENUM)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_ENTERPRISE_REGNO on ENTERPRISEBASEINFOCOLLECT (REGNO)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create bitmap index INDEX_ENTERPRISE_SEV on ENTERPRISEBASEINFOCOLLECT (S_EXT_VALIDFLAG)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index INDEX_ENTERPRISE_SEV_ENTNAME on ENTERPRISEBASEINFOCOLLECT (S_EXT_VALIDFLAG,ENTNAME)
  tablespace NDB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Answer 26

这是查询语句：
select count(*) from enterprisebaseinfocollect where ( contains(entname,'公司')>0 ) and s_ext_validflag=1;

Answer 27

我怎么没看到建立domain index on entname的语句？

Answer 28

数据量大的话创建一个索引 然后count(PK)

Answer 29

列ENTNAME是VARCHAR2(200)，为什么要建立全文索引？用like条件不行吗?

Answer 30

contain用到索引没？
count(1),count(*) yi yang de see see the excute plan

Answer 31

数据量太大，使用like查询太慢了

Answer 32

另外我也建了两列的联合索引，用select count(*) from enterprisebaseinfocollect where  S_EXT_VALIDFLAG=1 and entname like '%公司%';查询是快了不少，但也需要10秒的时间。还有什么可以优化的吗？

Answer 33

1、用全文索引有很大的局限性，因为全文索引必须定时同步，而同步的时候会所定全表，所以通常只用于非实时的查询。
2、你的问题在于有两个条件，其中只有一个能用到全文索引，通过全文索引找到纪录后还必须做Table Scan By RowId，当你全文索引中满足条件的纪录很多的时候，这个Table Scan是非常昂贵的，其实你的trace已经很清楚地表明这点了。
2个条件
    751773  consistent gets
    659392  physical reads 
1个条件
    102209  consistent gets
      9649  physical readsconsistent gets前者是后者的7倍，physical reads更是66倍。中间的差异就是因为Table Scan引起的。
所以针对你的条件，全文索引用还不如不用。3、不用全文索引，而用like '%xxx%'就无法避免Full Index Scan。10秒走Fast Full Index Scan查询1500万纪录不算慢。4、如果能用到like 'xxx%'条件的话,Oracle就会走Index Range Scan或者Index Skip Scan。那效率就高很多了。5、对于like '%xxx%'，如果你有一个表控制entname的话，比如
create table enterprise
(entid integer,
 entname varchar2(200),
 xxx
);
同时建立组合索引，entname在前
CREATE INDEX INDEX_ENTERPRISE_ENTNAME_ENV ON ENTERPRISEBASEINFOCOLLECT
(ENTNAME, S_EXT_VALIDFLAG);
假定enterprise表纪录有限的话，用以下的查询就能走NEST LOOP on enterprise + Index Range Scan on INDEX_ENTERPRISE_ENTNAME_ENV,根据我的测试，执行成本奖励到1/3以下。good luck!