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 ]