紧急求救，数据库执行like语句效率问题

Question

oracle下，单个表数据量200万多，查询一个count语句后面的条件是like，执行时间是15s，这正常吗？
各位大虾，紧急求救，谢啦

Answer 1

select count(*) from table_name where  字段1  = '111111'
请教请教

Answer 2

select count(*) from table_name where 字段1 = '111111'
这句sql换成select count(1) from table_name where 字段1 = '111111' 效率应该会稍微高点的呢 

Answer 3

select count(*) from table_name where 字段1 = '111111'
* 表示所有的字段   只需写表的主键就行了

Answer 4

我写错了  实际上是 select count(1) from 表 where 字段 like '_%1111'

Answer 5

scott@ORCL> create table t(id,owner,object_id) as select rownum,owner,object_id
  2  from all_objects nologging;Table created.scott@ORCL> select count(*) from t;  COUNT(*)
----------
     11446scott@ORCL> create index i_t_object_id on t(object_id);Index created.scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);PL/SQL procedure successfully completed.scott@ORCL> select count(*) from t where object_id like '_%1111';  COUNT(*)
----------
         1scott@ORCL> select * from table(dbms_xplan.display_cursor());    -->使用了索引快速扫描 ，性能应该可以啊，看看你的语句的执行计划 PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  dfmgyvm7087hj, child number 0
-------------------------------------
select count(*) from t where object_id like '_%1111'Plan hash value: 3095383276---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |     8 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID |   572 |  2860 |     8  (13)| 00:00:01 |
---------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter(TO_CHAR("OBJECT_ID") LIKE '_%1111')
19 rows selected.

Answer 6

scott@ORCL> select count(*) from t where object_id like '_%1111';
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets      --count(*)  30
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processedscott@ORCL> select count(1) from t where object_id like '_%1111';
Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
         57  consistent gets   -- >count(1) 57 
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processedscott@ORCL> /
Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
         57  consistent gets    -- >count(1) 57 
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processedscott@ORCL> select count(*) from t where object_id like '_%1111';
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets     --count(*)  30
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed不见得count(*) 比 count(1)高效啊 

Answer 7

select count(主键) from  t where object_id like '_%1111';
like 在使用索引时。like '1111%'这样更好

Answer 8

like '_%1111'情况下可能不走索引，like '1111%'情况下可能没问题