Oracle排序优化问题，求解决方案

Question

1.一张表内有3亿条数据，该表建立全文索引；2.现在我以“张三”关键词去检索该表,我只需要前300条数据（rownum<=300），这样Oracle查够300条就不再往下扫描，速度很快。SQL语句:select * from 表名 contains(text,'张三')>0 where rownum<=300；3.但实际上张三这个关键词有10000条数据，如果我想先按时间排序（倒序）再取前300条，速度就很慢了，因为如果有排序操作，Oracle会10000条结果集都检索出后再排序，然后才去取前300条,成本太高了,
SQL语句:select * from (select * from 表名 where contains(text,'张三')>0 order by 生产时间 desc) where rownum<=300；4.求解决方案提高检索速度。目的是:检索结果取前300条，但要求记录的生产时间最早的排前面。5.一定给分。

Answer 1

-- 或者这样试试：
with a1 as (select ... from tb_name where name='张三'),
     a2 as (select ... from a1 order by date_time desc)
select * from a2 where rownum<=300;

Answer 2

建议对此表进行时间分区管理，按照生产时间分区管理会快一点partition by time(生产时间)
(
partition part01 values less than (to_data('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace01,
partition part02 values less than (to_data('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace02
)毕竟你的数据上亿了，多了都会很慢的

Answer 3

你把查询计划发出来看看，
如果已经是带stopkey的sort（SORT ORDER BY STOPKEY），基本上就很难再优化了。

Answer 4

首先观察一下 张三这个人是否属于某个类别或者范围，如果是的话就用类别字段或者范围字段对表进行分区表处理 这样绝对可以提高查询速度！
假如你的表结构是这样 customers(id,name,dept)
你可以先建一个分区表 customers_t 语句如下：
CREATE TABLE customers_t( 
    id    INT PRIMARY KEY , 
    name VARCHAR (20), 
    dept VARCHAR (10) 
) 
PARTITION BY LIST (dept) 
( 
    PARTITION dept1 VALUES ('dept2') TABLESPACE xxx, 
    PARTITION dept2 VALUES ('dept2') TABLESPACE xxx
);
然后执行 insert into customers_t select * from customers;
        rename customers to customers_old;
        rename customers_t to customers;
查询的时候这样写语句
          select * from customers PARTITION (dept1);
上面所有语句用到的dept1 dept2 是dept字段的具体值，注意分区名后面的dept1不用加单引号，分区名规定必须字母开头且不能包含-

Answer 5

1  分区表之类的 你应该也考虑到了。可能业务原因不能使用分区表。
2  单独从sql语句上讲，只能这样查询。无更好的办法。
3  能否采用变通的方法？
假设你的业务是只需要前300条记录。在半夜某时间点调用job，将大表的前300条张三放入另一张表a中。而平时的查询是从另一张表中提取数据。

Answer 6

也可考虑使用临时表空间组--TTG

Answer 7

人间正道是沧桑，这个是首选。
1、先查询结果，放入临时表，速度搜一下
2、10000条数据进行排序，速度搜一下一步变成两步走，速度搜一下。或者
还个一体机吧，曾几何时，一个在普通服务器上跑2三个小时都跑不出来的sql
在一体机上 搜一下 几秒就完事儿了。

Answer 8

select * from 
              (
              select /*+ FIRST_ROWS(300) */ a.* from 表名 a
              where contains(text,'张三') > 0 
              order by 生产时间 desc
              )
where rownum<=300不知道这样行不行？

Answer 9

直接读取物理内存
    select *    from   (select rid
        from   (select a.rid, rownum rn
                from   (select rowid rid
                        from    表名 tr
                        order  by tr.d_createdate desc nulls last ) a) b) v1,
       表名 tr
where  v1.rid = tr.rowid