Oracle 10g vs SQL Server 2000 - Range scans

[JtheRipper]

Hi there,

I hope someone can shed some light on this...

We have a partitioned table in Oracle, partitioned by month (+- 1 500 000 records per day, +- 35 000 000 records per month) ie.

test_tbl:
partitionkey number;
my_otherkey number
somedatefield date;

select min(my_otherkey), max(my_otherkey)
from test_tbl
where partitionkey >= 1 and partitionkey < 20;

We have the same table in SQL Server (simulating partitioning by building views using the union clause on multiple tables). The table has an index (PK) on partitionkey,my_otherkey. This index is clustered in SQL Server (similar to Oracle's IOT). There is also an index on the my_otherkey.

When doing a range query on these tables, SQL Server returns the result in 2 seconds, where Oracle takes about 2 minutes. Oracle is doing a Fast Full Index scan to get the data back. It seems as if SQL Server is using the index on my_otherkey to get the data back and somehow knows where the min and max values are for each partition.
Also....

I created a non-partitioned table on Oracle and SQL Server with the same indexes and this takes approx 40 seconds on Oracle and still 1 second on SQL Server.

I also tried to use Oracle IOT's but still no luck.

From the looks of things it seems that Oracle cannot handle range queries as good as SQL Server ??
Any ideas on how one can improve range type queries (with aggregate functions) such as these?

Any ideas/help/motivation will be highly appreciated.

Thanks,
J.

 

[JtheRipper]

Just an update...

You can build a materialized view to improve these type of queries which will improve performance a LOT, but I would prefer not to go that route as it would result in a proliferation of materialized views in the database.

J.

 

[Beilstwh]

In the oracle table, do you have a key on partitionkey? Have you generated statistics?

Bill
Oracle DBA/Developer
New York State, USA

 

[JtheRipper]

Yes,

The PK is partitionkey,myotherkey. I also created an intex (tried bitmap and b-tree) on partitionkey alone.

Generates stats/histograms.
Changed optimizer parameters.

Looks like this is one where SQL Server has one up on Oracle.

 

[dbalearner]

As a matter of interest how many records do you have in Oracle and SQL tables? Are they the same?

 

[JtheRipper]

Yes, they're exactly the same, approx 35mill records per month (4 months worth).
I also tested using only 1 month's data (SQL and Oracle).

J.