看来我是真的想的太简单,请大家帮我看看SQL吧: select t3.* from (select t1.id,t1.deviceid,t2.device_type_id,t1.geomtype,t1.left,t1.top,t1.right,t1.bottom,t1.orientation,t1.scale,t1.text,t1.height,t1.font_orientation from t1,t2 where t1.geomtablename=t2.geom_table_name) t3,t4 where (t4.min_scale<={:scale} and t4.max_scale>={:scale} and t3.device_type_id=t4.device_type_id) and ((t3.left>={:xmin} and t3.left<={:xmax}and t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.right>={:xmin} and t3.right<={:xmax} and t3.top>={:ymin} and t3.top<={:ymax}) or (t3.left>={:xmin} and t3.left<={:xmax} and t3.top>={:ymin} and t3.top<={:ymax}) or (t3.right>={:xmin} and t3.right<={:xmax} and t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.left<={:xmin} and t3.bottom<={:ymin} and t3.right>={:xmax} and t3.top>={:ymax}) or (((t3.left>={:xmin} and t3.left<={:xmax}) or (t3.right>={:xmin} and t3.right<={:xmax})) and t3.top>={:ymax} and t3.bottom<={:ymin}) or (((t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.top>={:ymin} and t3.top<={:ymax})) and t3.left<={:xmin} and t3.right>={:xmax}))
或者你是不是在A字段上用了函数
大表放小表前面
要学习,就去搜索一下oracle解释计划和类型转换,找几篇博客仔细研究一下。
select t3.* from (select t1.id,t1.deviceid,t2.device_type_id,t1.geomtype,t1.left,t1.top,t1.right,t1.bottom,t1.orientation,t1.scale,t1.text,t1.height,t1.font_orientation from t1,t2 where t1.geomtablename=t2.geom_table_name) t3,t4 where (t4.min_scale<={:scale} and t4.max_scale>={:scale} and t3.device_type_id=t4.device_type_id) and ((t3.left>={:xmin} and t3.left<={:xmax}and t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.right>={:xmin} and t3.right<={:xmax} and t3.top>={:ymin} and t3.top<={:ymax}) or (t3.left>={:xmin} and t3.left<={:xmax} and t3.top>={:ymin} and t3.top<={:ymax}) or (t3.right>={:xmin} and t3.right<={:xmax} and t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.left<={:xmin} and t3.bottom<={:ymin} and t3.right>={:xmax} and t3.top>={:ymax}) or (((t3.left>={:xmin} and t3.left<={:xmax}) or (t3.right>={:xmin} and t3.right<={:xmax})) and t3.top>={:ymax} and t3.bottom<={:ymin}) or (((t3.bottom>={:ymin} and t3.bottom<={:ymax}) or (t3.top>={:ymin} and t3.top<={:ymax})) and t3.left<={:xmin} and t3.right>={:xmax}))
别人应该不会有那么多的精力给你看这个sql你要查询的表,和索引其实是两个段,就类似于一个是你字典的汉字部分,索引是你字典的偏旁部首部分。直接翻字典,那是 全表扫描。按照偏旁部首查找字典,那个就是索引,具体的是,根据偏旁部首再去读字典(回表)。但是,如果如你所说,查询的数据很多,Oracle还是会选择直接去翻字典的,因为大量的数据,Oracle直接去把字典读一遍,就不用一条条的“回表”了。
另外left\top\right\bottom与x和y的最小最大到底什么关系,感觉语义上就可以优化,不用这么多比较吧