请教：千万级数据排序后很慢问题

richto

SELECT * FROM TABLE1 WHERE COL>100 ORDER BY COL;
It all depends on what you want to optimize, response time or elapsed time. If you are talking about displaying first few hundreds of records on screen, you should try to enable the COL index for index scan for better response time, it make your SQL return records faster.
If you are printing them on a report or use it in a Cursor and retrieve all records for evaluation. Elapsed time will be important, other execution methods may better, index may not be the good way to do it.

sqysl

richto 发表于 2018-1-6 20:07
SELECT * FROM TABLE1 WHERE COL>100 ORDER BY COL;
It all depends on what you want to optimize, respo ...

so great!

newkid

其实就是 FIRST_ROWS 和 ALL_ROWS的选择，OPTIMIZER_MODE的缺省是ALL_ROWS，也即不会对这种大结果集使用索引扫描。