• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

clarifications on oracle join

 
Ranch Hand
Posts: 56
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 42160
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic