急!请帮忙指点一个SQL语句的优化问题

[UChuaihuai]

select
distinct *
from
person,addr,contract
where
lower(per_name) like '%john%' and
con_type = 'I' and
con_tin = per_ssn and
con_number = add_num and
(add_type = '01' or add_address_type = '02' or add_type = '29')

三个表均有300000条左右数据，其中add_type共有10种，con_type有两种。结果约有2000条数据，所有查询子段均建立了索引，但是查询速度非常慢。请问各位有没有办法优化。先谢了！
Execution plan:

OPERATION OPTIONS
------------------------------ --------
SELECT STATEMENT
SORT UNIQUE
MERGE JOIN
SORT JOIN
HASH JOIN
TABLE ACCESS FULL
TABLE ACCESS FULL
SORT JOIN
TABLE ACCESS FULL

[pengdali]

select
distinct *
from
person,addr,contract
where
instr(lower(per_name),'john')>0 and
con_type = 'I' and
con_tin = per_ssn and
con_number = add_num and
(add_type in ('01','29') or add_address_type = '02')

[tsj68]

不知3个表的表结构如何,可以贴出来看一下.

[vrv0129]

select
*
from
person,addr,contract
where
lower(per_name) like '%john%' and
con_type = 'I' and
con_tin = per_ssn and
con_number = add_num and
(add_type = '01' or add_address_type = '02' or add_type = '29')
group by person.OPERATION

[vrv0129]

group by 比distinct的速度开多了

[beckhambobo]

语句不能最终解决性能问题，根本在于表结构设计是否合理，大表,小表等

[JasLi]

看一下索引的用法，印象里：索引的顺序和WHERE字句中引用字段的顺序要一致；如果字段本身取值只有几个（如2个）没必要建索引；使用函数通常用不到索引。
hope it's helpful !

[hdkkk]

Execution plan显示没有用到所引，使用hint /*+index()*/

[LGQDUCKY]

lower(per_name) like '%john%' 这样是用不上索引的
把这个优化了。。