Sql – why this statement in oracle perform much slower then ms sql

oraclequerysql

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

Best Answer

Are you certain your indexes are being used? What does EXPLAIN PLAN tell you about the query with the indexes in place? Just because an index exists doesn't mean Oracle will use it. If the statistics are not built/updated, the index is likely not used.

See the DBMS_STATS package, in particular GATHER_TABLE_STATS for more information.

Something as simple as 

BEGIN
  dbms_stats.gather_table_stats('<schema_owner>', 'TEST');
END;
/

Could solve your problem.

Note that '<schema_owner>' is case sensitive. Unless you went out of your way to make it otherwise, it's going to be upper case.

Related Topic