2

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

Improve this question
2
  • 1
    Are you loading them with the exact same data, or is the "random" comment meant to say that you generate random numbers each time?
    – Bill Weiss
    Sep 30, 2010 at 2:41
  • did it with same amount data also, as in tried to count 10000000,40000000 result of both join, on record column. The random was to test how loading fair with or without index. On random column, random populated number during results creation only, results count fetch about ~900000-1000000. The observation was with or without index, oracle always return ~20s and more, on ms sql improvement can be seen using index for product count on 10 million record.
    – user55708
    Sep 30, 2010 at 9:08

4 Answers 4

Reset to default
3

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.

Improve this answer
0
0

Presumably, Oracle is playing nice and staying inside its sandbox, while MSSQL is taking over your machine. That is to say, MSSQL's working almost entirely out of RAM/cache, while Oracle's having to hit the disk. Try applying some basic optimizations to both, and I'd bet the issue disappears.

Improve this answer
0

You could ask Oracle via 'Metalink'. Or if you do not have a Metalink account with them there is presumably a way for potential customers to ask this kind of question especially if you are likely to become a big user.

Is performance the most important factor for you in choice of DBMS and what sort of workload are you likely to run in production? You may well find choice of hardware has a bigger impact on performance than choice of DBMS.

Improve this answer
0

Here is the output after running gather_table_stats and explain plan

SQL> BEGIN
  2   dbms_stats.gather_table_stats('hr', 'TEST');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>explain plan for select count(*) from hr.test a join hr.test b 
on a.noofrecord between 50001 and 60000 and b.noofrecord between 80001 and 90000;

[IMG]http://i53.tinypic.com/262taol.jpg[/IMG]

seems like no filter was done before the join?

Improve this answer
1
  • Schema parameter to gather_table_stats is case sensitive, and likely should be upper case, e.g., 'HR'.
    – DCookie
    Oct 5, 2010 at 17:26

You must log in to answer this question.