I tried to run a simple test to see how the speed fair between oracle and Ms Sql as follow: populate two tables with 2 million records, cross join them, filter by a range, and do a sum. Both table structure as fellow:
create table t ( record number, name , varchar(50), num number //--random )
SQL as :
select sum(a.record) from test a cross join test b where a.num between 50001 and 80000 and b.num between 80001 and 110000
Somehow the same query running on oracle perform badly when compare to Ms sql. On oracle, the timing around 20s, but on Ms Sql,the result return within 1s. As I increase the range, the timing on oracle degraded while sql still fair well.
Oracle setup: 11g single instance on red hat machine Ms sql setup: 2008 on win7
Try the statement on oracles xe, win 7, result also range around 20s. All queries done on single machine.
Shall the statement on oracle be written differently? Wonder if anyone can provide some insight?
Thanks