Hints优化查询

  数据量：280万，后续还会大量增长

问题：因需要使用log_date||log_time(此前这两个字段已创建有索引)，查询，太慢，时间大约耗费几分钟，因为要在15分钟内生成文件并在指定时间内传输到另一个主机上，所以这个时间是不允许的。

当前使用SQL:

SELECT * FROM td_transfer_log_1 where log_date||log_time >='20101119104400' and log_date||log_time <'20101119145900'

 

问题出现：思考，使用set autotrace on explain查看，走全表扫描，这也是理所当然，因为字符串进行了连接。

策略一：增加一个字段indbdate，date类型，同时在此字段上面增加索引。

测试一：此招无效果，还是走的全表扫描，大约查询一次达到2分钟，太慢。

再将执行计划分析，还是走的全表扫描。

 

重新看现在状态：

1、Optermizer =Choose

2、数据280万，表有索引，使用下列语句查询统计信息，未匹配到行，表或索引没统计信息，而且相应的列有索引时，走RBO的方式，但现在执行分析计划得知没有走索引，矛盾。

SELECT * FROM user_tables WHERE table_name='TD_TRANSFER_LOG_1' AND num_rows is not null; SELECT * FROM user_indexes WHERE table_name='TD_TRANSFER_LOG_1' AND num_rows is not null;

2、使用下列语句查询统计信息，匹配到部分数据，说明最终走的是CBO方式，执行全表扫描。

 

SELECT * FROM user_tables WHERE table_name='TD_TRANSFER_LOG_1' ; SELECT * FROM user_indexes WHERE table_name='TD_TRANSFER_LOG_1' ;

 

那现在如何优化，我想最好的方法就是进行表和索引的分析，使用以下语句：

 

ANALYZE TABLE TD_TRANSFER_LOG_1 COMPUTE STATISTICS; ANALYZE INDEX INDBDATE_IDX ESTIMATE STATISTICS;

 

但这个语句没有老大及局方的人同意是不敢做的，所以找到了下面的方法：

 

策略二：

SELECT /*+INDEX(indbdate)*/ * FROM td_transfer_log_1 where indbdate >=to_date('20101119104400','yyyymmddhh24miss') and indbdate <to_date('20101119145900','yyyymmddhh24miss')

 

不同测试时间可能不同，但时间都没超过0.2秒。

暂时解决。

 

 

摘一段有帮助的话：

Understanding Optimizer Hints

Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:

• The optimization approach for a SQL statement
• The goal of the cost-based optimizer for a SQL statement
• The access path for a table accessed by the statement
• The join order for a join statement
• A join operation in a join statement

 参考文档：

http://download.oracle.com/docs/cd/A97630_01/server.920/a96533/hintsref.htm

 

 

 

 

 

 

 

更新：

 

同时遇到一个问题，是使用sqlplus再次进行时间分析时出现的：
已用时间:  00: 00: 00.01
SQL> set timing on
SQL> SELECT /*+INDEX(indbdate)*/  * FROM  td_transfer_log_1
  2   where indbdate >=to_date('20101119104400','yyyymmddhh24miss')
  3     and  indbdate <to_date('20101119145900','yyyymmddhh24miss');
SELECT /*+INDEX(indbdate)*/  * FROM  td_transfer_log_1
              *
第 1 行出现错误:
ORA-12571: TNS: 包写入程序失败

 

 

重新登录：

 

执行分析：

SQL> SELECT /*+INDEX(indbdate)*/  * FROM  td_transfer_log_1
  2   where indbdate >=to_date('20101119104400','yyyymmddhh24miss')
  3     and  indbdate <to_date('20101119145900','yyyymmddhh24miss')
  4  ;

    

已选择62行。

已用时间:  00: 00: 00.55

执行计划
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=39 Bytes=8814 
          )                                                                    
                                                                               
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TD_TRANSFER_LOG_1' (Cost         
          =3 Card=39 Bytes=8814)                                               
                                                                               
   2    1     INDEX (RANGE SCAN) OF 'INDBDATE_IDX' (NON-UNIQUE) (Cost=         
          2 Card=70)                                                           
                                                                               

 

SQL> spool off