wood burning stoves 2.0*
The moose likes JDBC and the fly likes clarifications on oracle join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "clarifications on oracle join" Watch "clarifications on oracle join" New topic
Author

clarifications on oracle join

ravi v kumar
Ranch Hand

Joined: Dec 14, 2004
Posts: 56
hii all,

i need few clarifications regarding oracle joins and performance tuning

lets take table tab1 with columns id, name, effdt, age
tab2 with columns id, dept, sal, empl_status

both the tables i am planning to join based on column id

select a.*, b.* from tab1 a, tab2 b
where b.id(+)=a.id // Is this needs to be the first condition??
and a.name like 'ma%' // will this affects outer join??
and a.effdt = (select max(effdt) from tab1 where id=a.id
and effdt <= sysdate)

is it good to have indexes based on where clause, like for the above query
in order to get fast execution of sql do we need to create indexs like
tab1 : id,name,effdt (combined index)
tab2 : id


pls clarify me the above 3 questions?

right i am working on the search query which will have 7 tables joined and the results range from 25k - 50k rows. as of now its taking more than 3 mins for 1000 records. i need to apply pagination like google search for the search results.

thanks in advance
ravi
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29261
    
140

Originally posted by ravi v kumar:
Is this needs to be the first condition??

No. The outer join does not to be the first condition.


will this affects outer join??

The where clause narrows down the records from table "a" returned in the final query.[/QB]
Yes. I suspect having an additional index on a smaller set of criteria (for table 1) will help matters further. If the database can narrow down results quickly - say on the name field - you narrow the results faster. Having an index on three of the four fields doesn't save as many disk accesses. It is almost, but not quite, as bad as a full table scan. Similarly, I would have an index just on id. That gives you at least two indexes - one on name and one on id.

1000 records shouldn't take anywhere near three minutes. This can definitely be tuned more.

Of course this is all speculation. The way to find out for sure is to look at the execution plan ("explain") from your database for the query. This will show you what the database is actually doing to execute your query.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by ravi v kumar:
hii all,

i need few clarifications regarding oracle joins and performance tuning

lets take table tab1 with columns id, name, effdt, age
tab2 with columns id, dept, sal, empl_status

both the tables i am planning to join based on column id

select a.*, b.* from tab1 a, tab2 b
where b.id(+)=a.id // Is this needs to be the first condition??
and a.name like 'ma%' // will this affects outer join??
and a.effdt = (select max(effdt) from tab1 where id=a.id
and effdt <= sysdate)

is it good to have indexes based on where clause, like for the above query
in order to get fast execution of sql do we need to create indexs like
tab1 : id,name,effdt (combined index)
tab2 : id


pls clarify me the above 3 questions?

right i am working on the search query which will have 7 tables joined and the results range from 25k - 50k rows. as of now its taking more than 3 mins for 1000 records. i need to apply pagination like google search for the search results.

thanks in advance
ravi


ravi, part of your problem is that it would appear your query requires a full table scan to complete (might improve with an index, but I don't know what else is this table is used for).

could you give more details about your columns and tables... maybe the structure is part of the problem.

jeanne is right, an explain plan would go a long way to identifying the problem.
[ January 22, 2008: Message edited by: Paul Campbell ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: clarifications on oracle join
 
Similar Threads
Getting Max Year
LEFT JOIN problem. 2 tables, include null
Sybase to Oracle conversion. (SQL)
Perforarmance diff bet hibernate and JDBC
how can we use where clause with insert query using preparedStatement?