clarifications on oracle join

[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

[Jeanne Boyarsky]

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.

[Paul Campbell]

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 ]