最好能把你的表结构、索引、查询语句发来看一下。

5.5/s与130/s之间只有是否有索引这一个变量？

t.username like 'username'       没加%？

测试时t.username like筛选出的数据量占sys_user数据量的比例怎么样？

测试时，是否对sys_user表有频繁的新增、删除操作？

执行计划发出来

模糊查询如果返回的数据量不能得到控制，那么这种查询用关系数据库本身是无解的，加分页也一样。

Quote: 引用 6 楼 minsic78 的回复:

模糊查询如果返回的数据量不能得到控制，那么这种查询用关系数据库本身是无解的，加分页也一样。

模糊查询返回的数量是固定的，加索引变慢的这个是精确查询

Quote: 引用 7 楼 可爱的姚大宝 的回复:

Quote: 引用 6 楼 minsic78 的回复:

模糊查询如果返回的数据量不能得到控制，那么这种查询用关系数据库本身是无解的，加分页也一样。

模糊查询返回的数量是固定的，加索引变慢的这个是精确查询

如果固定200，那么是适合走索引的，上执行计划吧

都是表遍历

like '部门path'和 like 'username' 这两个是精确查询吗？如果是的话，把like改成=，如果不是加%%

都是表遍历

like '部门path'和 like 'username' 这两个是精确查询吗？如果是的话，把like改成=，如果不是加%%

部门path和username都是变量吧？你不会是直接用= '部门path'和='username'来查的吧？

这个是在eclipse里打印出的sql

看不全

Quote: 引用 17 楼 可爱的姚大宝 的回复:

这个是在eclipse里打印出的sql

explain试试

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

这个是在eclipse里打印出的sql里面明明有%%，为什么你贴出来的SQL没有？

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

问了一下开发人员
1、程序内没做username精确查询，like改成=，程序也不起作用
2、打印出来的username两边都带有%，前%会导致索引失效，这样的话，无论怎么查，索引都是失效的
现在就总结出这样的问题

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

Quote: 引用 29 楼 可爱的姚大宝 的回复:

问了一下开发人员
1、程序内没做username精确查询，like改成=，程序也不起作用
2、打印出来的username两边都带有%，前%会导致索引失效，这样的话，无论怎么查，索引都是失效的
现在就总结出这样的问题

如果是双向模糊查询，建议使用solar之类的玩意，或者用redis之类的先算出需要访问的数据的主键值再到RDB中获取需要的数据。
针对你数据量不大的场景，可以考虑这样：使用index fast full scan来完成模糊匹配并获取符合条件行的rowid，或者主键，再在外面嵌套一层，通过前面得到的主键或者rowid再次访问user表，这样需要修改SQL代码，能一定程度上优化原来的SQL，但不一定比两表扫描后的HJ高效，跑了才知道。

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

Quote: 引用 33 楼 minsic78 的回复:

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

对，我就是想弄清到底为什么会变慢

Quote: 引用 35 楼 可爱的姚大宝 的回复:

Quote: 引用 33 楼 minsic78 的回复:

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

对，我就是想弄清到底为什么会变慢

慢是因为你建了索引后，CBO选择了不太恰当的连接方式与连接顺序，导致相对较大的user表成为了嵌套循环中的被驱动表，而且走了表扫描，这意味着：如果驱动表上出来一条满足条件的记录，被驱动表就会经历一次表扫描，如果驱动表是几百条，那么被驱动表就是几百次的表扫描……

Quote: 引用 35 楼 可爱的姚大宝 的回复:

Quote: 引用 33 楼 minsic78 的回复:

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

对，我就是想弄清到底为什么会变慢

慢是因为你建了索引后，CBO选择了不太恰当的连接方式与连接顺序，导致相对较大的user表成为了嵌套循环中的被驱动表，而且走了表扫描，这意味着：如果驱动表上出来一条满足条件的记录，被驱动表就会经历一次表扫描，如果驱动表是几百条，那么被驱动表就是几百次的表扫描……

Quote: 引用 35 楼 可爱的姚大宝 的回复:

Quote: 引用 33 楼 minsic78 的回复:

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

对，我就是想弄清到底为什么会变慢

慢是因为你建了索引后，CBO选择了不太恰当的连接方式与连接顺序，导致相对较大的user表成为了嵌套循环中的被驱动表，而且走了表扫描，这意味着：如果驱动表上出来一条满足条件的记录，被驱动表就会经历一次表扫描，如果驱动表是几百条，那么被驱动表就是几百次的表扫描……

Quote: 引用 35 楼 可爱的姚大宝 的回复:

Quote: 引用 33 楼 minsic78 的回复:

Quote: 引用 31 楼 可爱的姚大宝 的回复:

Quote: 引用 28 楼 minsic78 的回复:

Quote: 引用 23 楼 可爱的姚大宝 的回复:

Quote: 引用 16 楼 minsic78 的回复:

先不说驱动表like的问题，按照刚才的思路，你的驱动表应该是t_user，而不是t_department，加个提示看下效果：
select * from (select tmp.*,rownum row_id from (select /*+leading(t)*/t.* from sys_user t join sys_department deptObj on t.dept = deptObj.id where (deptObj.path like '部门path')
and (t.username like 'username') and (t.userMgrType <> 10 and  t.deleted = 2) order by t.ordernum asc) tmp  where rownum <= 10 ) where row_id > 0;

查的确实是user表，我在下面加了对比图，有点看不太懂有索引和无索引的filter和access

通常，access是走索引的谓词，filter则是扫描后过滤的这种谓词，我看你上面模糊匹配还有双向模糊的，这种条件是没法用常规的方法来让他走索引的，需要大量改写，甚至添加提示才能让其走索引。所以，如果两张表user和departmen都不是太大的话，没有索引情况下，走hash join确实是合理的。

程序是肯定有问题的，我用jdbc压测的时候，索引失效但是也不会变慢

索引不是万能的优化手段，也不一定适合所有的场景。

对，我就是想弄清到底为什么会变慢

慢是因为你建了索引后，CBO选择了不太恰当的连接方式与连接顺序，导致相对较大的user表成为了嵌套循环中的被驱动表，而且走了表扫描，这意味着：如果驱动表上出来一条满足条件的记录，被驱动表就会经历一次表扫描，如果驱动表是几百条，那么被驱动表就是几百次的表扫描……

左面是有索引的执行计划，右面是无索引的执行计划，好像可以看出点问题

Quote: 引用 20 楼 可爱的姚大宝的回复:

左面是有索引的执行计划，右面是无索引的执行计划，好像可以看出点问题

这个sys_department.id和sys_user.dept加索引了么

这样写试试看

select * 

from (select tmp.*,rownum row_id 

      from (select t.* 

	        from (select * from sys_user where t.username like 'username' and t.userMgrType <> 10 and  t.deleted = 2 ) t 

			    join sys_department deptObj on t.dept = deptObj.id 

			where (deptObj.path like '部门path')

		    order by t.ordernum asc) tmp  

	   where rownum <= 10 ) 

where row_id > 0;

Quote: 引用 40 楼 顺势而为1 的回复:

这样写试试看

select * 

from (select tmp.*,rownum row_id 

      from (select t.* 

	        from (select * from sys_user where t.username like 'username' and t.userMgrType <> 10 and  t.deleted = 2 ) t 

			    join sys_department deptObj on t.dept = deptObj.id 

			where (deptObj.path like '部门path')

		    order by t.ordernum asc) tmp  

	   where rownum <= 10 ) 

where row_id > 0;

提示 ora-00904 T.DELETED 标识符无效

Quote: 引用 41 楼 lizer2015 的回复:

Quote: 引用 20 楼 可爱的姚大宝的回复:

左面是有索引的执行计划，右面是无索引的执行计划，好像可以看出点问题

这个sys_department.id和sys_user.dept加索引了么

没有 

Quote: 引用 42 楼 可爱的姚大宝 的回复:

Quote: 引用 41 楼 lizer2015 的回复:

Quote: 引用 20 楼 可爱的姚大宝的回复:

左面是有索引的执行计划，右面是无索引的执行计划，好像可以看出点问题

这个sys_department.id和sys_user.dept加索引了么

没有 

那就加个索引就好了啊

Quote: 引用 45 楼 lizer2015 的回复:

Quote: 引用 42 楼 可爱的姚大宝 的回复:

Quote: 引用 41 楼 lizer2015 的回复:

Quote: 引用 20 楼 可爱的姚大宝的回复:

左面是有索引的执行计划，右面是无索引的执行计划，好像可以看出点问题

这个sys_department.id和sys_user.dept加索引了么

没有 

那就加个索引就好了啊

建议把索引加到最里层，初始数据数据量越小越好。

楼主的这个问题还没解决吗。。。

如果你要把双向模糊匹配在RDB中搞定的话，我在36楼的方法应该是你最后的选择了。

Quote: 引用 48 楼 minsic78 的回复:

楼主的这个问题还没解决吗。。。

如果你要把双向模糊匹配在RDB中搞定的话，我在36楼的方法应该是你最后的选择了。

我之前在前面已经说引起问题的原因了忘了结贴了