请教一个SQL效率的问题

Question

SELECT * from mtl_material_transactions where transaction_id >30000000 
这句执行 返回结果 在一秒内 select max(transaction_id)-100000 from mtl_material_transactions 
这句一样在1秒内SELECT * from mtl_material_transactions a where a.transaction_id > （select max(transaction_id)-100000 from mtl_material_transactions  ）这句的执行就很长时间 mtl_material_transactions 这个表的数据量非常大 大概了4千万

Answer 1

查看执行计划：
1.explain plan for SELECT * from mtl_material_transactions a where a.transaction_id > （select max(transaction_id)-100000 from mtl_material_transactions ）2.select * from table(DBMS_XPLAN.Display);

Answer 2

看过了 顺序没什么问题 
 第一步 执行的是 （select max(transaction_id)-100000 from mtl_material_transactions ）

Answer 3

对字段transaction_id 加个index呢？

Answer 4

筛选条件是常数的时候没什么问题  级个常数相互运算也没什么问题 但是涉及到数据库字段货值函数的计算的就会变得非常慢 
如 写个 date > sysdate 或者date > TO_DATE('2010-10-1','YYYY-mm-DD')就比较快 但是 写个  date > sysdate -10 就非常慢

Answer 5

transaction_id  是有索引的

Answer 6

索引字段用了函数（或者进行计算），就失效了，走了full scan table 

Answer 7

贴下执行计划吧.
按理说应该不会太慢啊.
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM test;已解释。SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |    48 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    16 |    48 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------已选择8行。
示例2：

Answer 8

--这个表扫描了两次 set autotrace on 开启执行计划看看
SELECT * from mtl_material_transactions a where a.transaction_id > （select max(transaction_id)-100000 from mtl_material_transactions ）

Answer 9

PLAN_TABLE_OUTPUT
 
----------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |   537K|   142M| 16264 |
|   1 |  TABLE ACCESS BY INDEX ROWID | MTL_MATERIAL_TRANSACTIONS     |   537K|   142M| 16264 |
|*  2 |   INDEX RANGE SCAN           | MTL_MATERIAL_TRANSACTIONS_U1  | 96713 |       |   383 |
|   3 |    SORT AGGREGATE            |                               |     1 |     6 |       |
|   4 |     INDEX FULL SCAN (MIN/MAX)| MTL_MATERIAL_TRANSACTIONS_U1  |    10M|    61M|     3 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."TRANSACTION_ID"> (SELECT /*+ */ MAX("B"."TRANSACTION_ID")-100000 FROM 
              "APPS"."MTL_MATERIAL_TRANSACTIONS" "B"))
 
Note: cpu costing is off

Answer 10

SELECT * from mtl_material_transactions a where a.transaction_id > （select max(transaction_id)-100000 from mtl_material_transactions ）中，
select max(transaction_id)-100000 from mtl_material_transactions ）索引列上有运算，
执行计划是index full scan。
改成这样
SELECT * from mtl_material_transactions a where a.transaction_id > （select max(transaction_id) from mtl_material_transactions ）- 100000，
然后再看下执行计划

Answer 11

感谢各位
 服务器重启后 现在能正常搜出来了
不过还是想知道，查询条件中 使用了函数 index 失效的 问题有没什么方法能避免

Answer 12

看执行计划索引都用上了啊.奇怪了.
transaction_id是不是唯一不重复的?

Answer 13

transaction_id 是唯一值  

Answer 14

测了下百万数据量的,速度还可以接受.请问你的index所在表空间和表所在空间是分开的么?

Answer 15

WITH TMP AS (SELECT max(transaction_id)-100000 AS transaction_id from mtl_material_transactions)
SELECT * from mtl_material_transactions a,TMP where a.transaction_id > TMP.transaction_id 
这样会不一样么