Oracle 大数据量 order by排序性能，求高手出解决方案

[yuzh08]

数据库oracle 10.1.2
sga:2G
pga:500M
连接数200
使用自动管理方案

需求，开发一个文档中心，数据量万百以上（不多）
表结构：
文档数据表：table_a(file_id,name,created_date,auth),主键:file_id,索引：created_date
目录表：table_b(catalog_id,name),主键:catalog_id
文档目录对应表：table_c(file_id,catalog_id)，外键file_id,catalog_id

查询语句

select * from (
select a.* from table_a a,table_c c 
where a.file_id=c.file_id and c.catalog_id='8193605320F91C68E040007F01005CAA'  and auth=?
order by created_date desc) 
where rownum<=10

说明：
c.catalog_id='8193605320F91C68E040007F01005CAA' 这个目录下有12万数据，现在执行速度还可以，此语句在plsql中为1秒内，但服务器CPU运行非常高，服务器现在4个CPU，要占用一个CPU的80%左右，而且此语句在程序中每个portal页面大约有15个左右这种查询，一个页面运行数据库CPU就会全部变成100%，页面响应5s，在压力测试过程中，20用启并发此页面，按每页15个查询算相当于同时执行15*20次这种SQL，数据库服务器4个CPU都处于满载状态，页面响应时间120s，如果去掉上面SQL中的order by，20用户并发响应时间在5s以内，created_date普通索引，正序索引和倒序索引都试过性能没有变化，由于每个人所访问文档的权限不一样，所以没办法做缓存或临时表

问题：

各人认为此SQL没有可以优化的余地，服务器也不可能在提升性能，所以请问各位大大们以前是否遇到过类似的问题，还有什么解决方案

[yuzh08]

补充：
各表主键都是32位UUID字符串，由于数据是在其它数据库和程序中抽取过来的，所以即使是使用自增长也需要对创建时间(created_date)进行排序，此系统也可以添加一些文档，所以实时性还算是比较高

[luyi6101]

created_date 数据多么?不多试试位图索引,也可以考虑做分区.

[yuzh08]

[Quote=引用 2 楼 luyi6101 的回复:]
created_date 数据多么?不多试试位图索引,也可以考虑做分区.
[/Quote]
谢谢你的回复
created_date就是一个日期字段，每条记录都有
如果做分区的话还是需要很多数据进行排序操作，我做过70万和120万，每个目录6万和12万数据量，两者没有太大变化，性能上基本上持平

[etsilence]

如果文档中心仅仅是用来查看，很少update和insert的话，试试建立个聚簇表，用聚簇索引看看效率怎么样。

[etsilence]

另外，你的临时表空间有多大，临时文件自增长设置为多大？如果你建库的时候没有更改过这些东西，那么赶紧新建个临时表空间吧，大数据量排序很吃空间的，尤其是你的并发查询这么多。

[codearts]

试下这个：

create index idx_createdt_auth on table_a(create_date, auth);

select *
from (
select /*+ index(a idx_createdt_auth) */
a.*
from table_a a
where auth = ?
and fileid in (
select file_id
from table_c
where catalog_id = '8193605320F91C68E040007F01005CAA'
)
order by created_date desc
)
where rownum<=10;

[codearts]

另外最好能帖出执行计划（在PLSQL developer中，选中SQL语句，按F5）

看下面的2个语句执行结果是多少
select count(1) from table_a;
select count(distinct auth) from table_a;

[codearts]

优化的思路在于根据created_date字段顺序扫描索引，以达到不排序的目的

[yulei8989]

另外最好能帖出执行计划（在PLSQL developer中，选中SQL语句，按F5）

看下面的2个语句执行结果是多少
select count(1) from table_a;
select count(distinct auth) from table_a;

[tangren]

百万级也可以试试分区表

[tangren]

另外，是否在表table_a的设计上冗余一下，增加字段catalog_id，不要再去关联了。

[yuzh08]

谢谢大家的回答，由于正式服务器的机器比较好，实际数据量也没有这么大，所以现在高并发性能还过得去，就直接结贴了
但还是有些疑问，不知道为什么网上所有的人都说排序字段要建索引，但据我实际测试，排序跟本就不走索引，包括最简单的SQL在执行计划里都看不到索引，还有就是这个SQL在IO和内存占用率都不高，只有CPU占用率非常高