Meaningless Drivel is fun!*
The moose likes Oracle/OAS and the fly likes Oracle very slow than sybase when there is multiple joins among tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle very slow than sybase when there is multiple joins among tables" Watch "Oracle very slow than sybase when there is multiple joins among tables" New topic
Author

Oracle very slow than sybase when there is multiple joins among tables

Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
I have tested a SQL query on Sybase and Oracle. The query involves join of 3 to 4 tables. Same data set in Sybase and Oracle. Same JDBC/Java code. Test shows the query from Oracle is significantly slower than Sybase. What can be the reason ? Just reindex ? anything else to your experience ?

Thanks.
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Originally posted by Artemesia Lakener:
I have tested a SQL query on Sybase and Oracle. The query involves join of 3 to 4 tables. Same data set in Sybase and Oracle. Same JDBC/Java code. Test shows the query from Oracle is significantly slower than Sybase. What can be the reason ? Just reindex ? anything else to your experience ?

Thanks.


EXPLAIN PLAN on both and see what the difference is.

Indexes, primary keys, order of JOIN - all will affect performance.


%
Michael Duffy
Ranch Hand

Joined: Oct 15, 2005
Posts: 163
Difference JDBC drivers, too.

Sure you know where the time is being spent? Have you profiled it to be sure?
Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
Originally posted by Michael Duffy:


EXPLAIN PLAN on both and see what the difference is.

Indexes, primary keys, order of JOIN - all will affect performance.


can you help further on "order of JOIN" ? if I have join among 2 big tables and 1 small Table, what order will be best ?

select * from BigTable_1, BigTable_2, SmallTable_1
select * from SmallTable_1, BigTable_1, BigTable_2
select * from BigTable_1, SmallTable_1, BigTable_2
Artemesia Lakener
Ranch Hand

Joined: Jun 21, 2005
Posts: 162
Actually I think theoretically we should be able to break any JOIN among tables into a series of chained SELECT statements like --

SELECT * from Table_1 where col_1=.. and col_2=.. and col_3 IN (SELECT col_3 from Table_2 where col1=.. and col2 IN (SELECT col2 from Table_3 where col1=.. and col3=..)

Things like that, right ?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Artemesia,
Excuse me, but I couldn't find any details in your post of the Oracle database version you are using. For your information, since Oracle 9i, the rule-based optimizer (RBO) has been replaced by the cost-based optimizer (CBO). In order for the CBO to perform efficiently, you need to perform a task known as "gathering statistics".

There are entire books that are completely devoted to tuning Oracle databases. In fact, there are entire books devoted to tuning SQL statements alone! So your question is very broad, and therefore difficult to provide a satisfactory answer for, in a forum (like this one).

Allow me to suggest that you start with the Oracle documentation, in particular Oracle Database Performance Tuning Guide. You can access it via the following Web site:

http://www.oracle.com/technology

By the way, the things Michael mentioned -- EXPLAIN PLAN, etc -- are described in the "Performance Tuning Guide", I referred you to.

Good Luck,
Avi.
[ February 21, 2006: Message edited by: Avi Abrami ]
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
It can also matter which version of Oracle you are using. Oracle 8 and back used dictionary-managed tablespaces, and improper extent sizing can crush query performance for non-trivial table sizes. Storage management options increased in later versions, and sometimes the specifics you hit depend on the version-specific defaults or how your DBA chose to set up the database.


Reid - SCJP2 (April 2002)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle very slow than sybase when there is multiple joins among tables