如果把查询语句改为select substr(e.ename,2,2) from emp e where length(e.ename)>3 会怎样
这个怎么弄也是全表扫描吧,要是我,我想就用sal做个范围表分区吧,
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
那就加个字段先存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
至于分区,那要看机器的配置了,要是就一块硬盘一个cpu也无法进行并行查询,分区了也没有意义.
还要增加一列substr(ename,2,2)的话还是需要进行全表扫描,而且一个数据块中存储的上面的索引数肯定比存储的表要多,也就是说进行索引扫描的io应该比全表扫描的低.
create index id_emp on emp(substr(ename,2,2));
建立索引,在WHERE条件中制定这个函数以便于索引可以被应用。
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
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
一时想不到优化的方案,面试官是想问并行?--->alter table emp parallel;
这样子比扫描索引要快些,而且这个表也会小不少。