华为的一条数据库查询优化笔试题

Question

表有一千万条数据以上,有字段eno,ename,sal,未建立索引,现在要执行select substr(ename,2,2) from emp ,请问怎样修改可以优化查询速度？（可以包括修改查询语句和修改表结构等）

Answer 1

create index id_emp on emp(substr(ename,2,2))

Answer 2

不建立索引,速度肯定快不了.当然你说：可以包括修改查询语句和修改表结构等
那就加个字段先存substr(ename,2,2)然后直接查询这个字段．

Answer 3

如果把查询语句改为select substr(e.ename,2,2) from emp e where length(e.ename)>3 会怎样

Answer 4

这个怎么弄也是全表扫描吧，要是我，我想就用sal做个范围表分区吧，

Answer 5

substr(ename,2,2)是用来迷惑人的
在是substr(ename,2,2)在WHERE后面还可以用函数索引.
sal做个范围表分区好像也没什么用.能优化的就是少执行substr() 函数,如果不考虑储存空间和表设计的合理性,只是从select substr(e.ename,2,2) from emp 语句考虑
可以增加一个列,列 XX 的值就是substr(ename,2,2) 然后 select XX from emp

Answer 6

感觉这个查询只能建substr(ename,2,2)的函数索引了,这样的话查询可以进行索引扫描,
至于分区,那要看机器的配置了,要是就一块硬盘一个cpu也无法进行并行查询,分区了也没有意义.
还要增加一列substr(ename,2,2)的话还是需要进行全表扫描,而且一个数据块中存储的上面的索引数肯定比存储的表要多,也就是说进行索引扫描的io应该比全表扫描的低.

Answer 7

感觉可以这样：
create index id_emp on emp(substr(ename,2,2));
建立索引，在WHERE条件中制定这个函数以便于索引可以被应用。

Answer 8

另一个版已经讨论过了，对2,2单独保存到一个字段里面，然后全表扫描如果cpu和磁盘好，比如阵列，可以分区，让多个线程、进程干活

Answer 9

从我做的测试来看，create index idx on emp(substr(ename, 1, 2)) 这个索引根本用不上scott@ORA1> desc t1;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 OWNER                                                                      VARCHAR2(30)
 OBJECT_NAME                                                       NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                             VARCHAR2(30)
 OBJECT_ID                                                                  NUMBER
 DATA_OBJECT_ID                                                             NUMBER
 OBJECT_TYPE                                                                VARCHAR2(19)
 CREATED                                                                    DATE
 LAST_DDL_TIME                                                              DATE
 TIMESTAMP                                                                  VARCHAR2(19)
 STATUS                                                                     VARCHAR2(7)
 TEMPORARY                                                                  VARCHAR2(1)
 GENERATED                                                                  VARCHAR2(1)
 SECONDARY                                                                  VARCHAR2(1)scott@ORA1> create index idx_t1 on t1(substr(object_name, 1, 2));索引已创建。scott@ORA1> exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);PL/SQL 过程已成功完成。scott@ORA1> set autotrace traceonly;
scott@ORA1> select substr(object_name, 1, 2) from t1;已选择49800行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49800 |  1215K|   157   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 49800 |  1215K|   157   (2)| 00:00:02 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3974  consistent gets
          0  physical reads
          0  redo size
     596789  bytes sent via SQL*Net to client
      36886  bytes received via SQL*Net from client
       3321  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49800  rows processedscott@ORA1> select /*+ index(t1, idx_t1) */ substr(object_name, 1, 2) from t1;已选择49800行。
执行计划
----------------------------------------------------------
Plan hash value: 3617692013--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49800 |  1215K|   157   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   | 49800 |  1215K|   157   (2)| 00:00:02 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3974  consistent gets
          0  physical reads
          0  redo size
     596789  bytes sent via SQL*Net to client
      36886  bytes received via SQL*Net from client
       3321  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49800  rows processedscott@ORA1> drop index idx_t1;索引已删除。scott@ORA1> create index idx_t1 on t1(object_name);索引已创建。scott@ORA1> exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);PL/SQL 过程已成功完成。scott@ORA1> select /*+ index(t1, idx_t1) */ substr(object_name, 1, 2) from t1;已选择49800行。
执行计划
----------------------------------------------------------
Plan hash value: 1481053488---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        | 49800 |  1215K|    75   (2)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_T1 | 49800 |  1215K|    75   (2)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3553  consistent gets
          0  physical reads
          0  redo size
     535740  bytes sent via SQL*Net to client
      36886  bytes received via SQL*Net from client
       3321  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49800  rows processed

Answer 10

对于这个1000万且仅有3个字段的表，select substr(ename,2,2) from emp 差不多是要走fts了。而在ename上建个索引，这个索引(索引上要加上rowid)至少有表的一半大小。
一时想不到优化的方案，面试官是想问并行？--->alter table emp parallel;  

Answer 11

create table t2 as select substr(object_name, 1, 2) object_name from t1;select object_name from t2;
这样子比扫描索引要快些，而且这个表也会小不少。