SQL语句如下:
------------------------------
select tt.* from (
(
子查询1
)
union all
(
子查询2
)
) tt
order by tt.F_GlobalOrder DESC, tt.SendDateTime DESC这条sql执行起来非常慢,要28秒,但如果去掉order by 子句,不排序的话,就会很快,仅1秒多。
F_GlobalOrder 和SendDateTime两个字段上都创建了索引。还有就是,如果直接从数据表里读取数据,然后排序,也很快:select * from TableName order by F_GlobalOrder DESC, SendDateTime DESC请高手帮忙!!!
------------------------------
select tt.* from (
(
子查询1
)
union all
(
子查询2
)
) tt
order by tt.F_GlobalOrder DESC, tt.SendDateTime DESC这条sql执行起来非常慢,要28秒,但如果去掉order by 子句,不排序的话,就会很快,仅1秒多。
F_GlobalOrder 和SendDateTime两个字段上都创建了索引。还有就是,如果直接从数据表里读取数据,然后排序,也很快:select * from TableName order by F_GlobalOrder DESC, SendDateTime DESC请高手帮忙!!!
在 tt.F_GlobalOrder,tt.SendDateTime 上建降序索引试试。
都是降序的,降序索引就不必要了
要不每次查询都会导致对这俩列进行降序排序。
另外对固定有降序查询的操作,建降序索引是很有必要的。举个例子:
OPER@TL>create create table test(aaa number,bbb varchar2(10));Table created.OPER@TL>insert into test
2 select trunc(dbms_random.value(1,100)),dbms_random.string('l',3)
3 from dual
4 connect by rownum<11;10 rows created.OPER@TL>commit;Commit complete.OPER@TL>select * from test; AAA BBB
---------- ----------
93 kjw
73 boa
99 psx
94 udc
79 ejw
71 bos
36 rfp
64 kbw
25 mnp
44 xcx10 rows selected.OPER@TL>set autot traceonly statistics
OPER@TL>select * from test
2 where aaa between 10 and 80
3 order by aaa desc,bbb desc;7 rows selected.
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
566 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
7 rows processedOPER@TL>create index t_1 on test(aaa,bbb);Index created.OPER@TL>exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL procedure successfully completed.OPER@TL>alter system flush shared_pool;System altered.OPER@TL>alter system flush buffer_cache;System altered.OPER@TL>select * from test where aaa between 10 and 80
2 order by aaa desc,bbb desc;7 rows selected.
Statistics
----------------------------------------------------------
301 recursive calls
0 db block gets
46 consistent gets
10 physical reads
0 redo size
566 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
7 rows processedOPER@TL>drop index t_1;Index dropped.OPER@TL>create index t_1 on test(aaa desc,bbb desc);Index created.OPER@TL>alter system flush shared_pool;System altered.OPER@TL>alter system flush buffer_cache;System altered.OPER@TL>exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL procedure successfully completed.OPER@TL>select * from test where aaa between 10 and 80
2 order by aaa desc,bbb desc;7 rows selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
566 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7 rows processedOPER@TL>
大家可以看到,在建索引,不建索引,建降序索引这几种方式下统计信息sorts (memory)的区别。
子查询1
union all
子查询2
order by F_GlobalOrder DESC, SendDateTime DESC --order by 可以放到最后一个子查询的
) tt