SQL> SELECT COUNT(*) FROM WIP2ST_RECORD
2 / COUNT(*)
----------
2027009目前歷時: 00:00:25.85使用25秒查詢表的記錄條數為2027009SQL> SELECT MAX(RECORD_ID) FROM WIP2ST_RECORD
2 /MAX(RECORD_ID)
--------------------
WP000000000004089439目前歷時: 00:00:00.15使用不到一秒的時間查出最大的ID 號為WP000000000004089439由上可見,使用MAX()函數的速度取決與欄位的索引,
你建的索引可以給我看看嗎?
2 / COUNT(*)
----------
2027009目前歷時: 00:00:25.85使用25秒查詢表的記錄條數為2027009SQL> SELECT MAX(RECORD_ID) FROM WIP2ST_RECORD
2 /MAX(RECORD_ID)
--------------------
WP000000000004089439目前歷時: 00:00:00.15使用不到一秒的時間查出最大的ID 號為WP000000000004089439由上可見,使用MAX()函數的速度取決與欄位的索引,
你建的索引可以給我看看嗎?
---------
3951562SQL> set autotrace traceonly
SQL> set serverout on
SQL> set timing on
SQL> select max(transaction_id) from mtl_material_transactions; real: 3219Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'MTL_MATERIAL_TRANSACTION
S_U1' (UNIQUE) (Cost=3 Card=3951548 Bytes=23709288)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
166 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select count(*) from mtl_material_transactions; real: 13812Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8710 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'MTL_MATERIAL_TRANSACTIONS_U1' (UNI
QUE) (Cost=8710 Card=3951548)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8721 consistent gets
8706 physical reads
0 redo size
172 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL> select count(transaction_id) from mtl_material_transactions; real: 9797Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8710 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'MTL_MATERIAL_TRANSACTIONS_U1' (UNI
QUE) (Cost=8710 Card=3951548)Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8721 consistent gets
8706 physical reads
0 redo size
185 bytes sent via SQL*Net to client
309 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processedSQL> --'MTL_MATERIAL_TRANSACTIONS_U1' 该索引为表的第一个索引.都采用了全表扫描.都用了第一个索引.
explain plan for
select max(id) from 表b where ....
哎~~感觉oracle在做insert into语句机制的时候,应该做一个方法:取最后插入的一个值,这样就方便了,
不想用select max(...) from table1 where ...
做,因为再快也是要计算的