在oracle中使用order by子句创建功能索引
我需要在表上按索引创建一个订单，
Student (
roll_No,
name,
stream,
percentage,
class_rank,
overall_rank )
我希望查询类似的结果
SELECT *
FROM student
WHERE stream = 'science'
，预期结果是学生按排名降序排列。要求是我不能在查询本身中指定 order by 子句。
这应该通过 (stream , order by class_rank desc) 上的索引来实现。这个在oracle中可以实现吗？
I Need to create a order by index on a table
Student (
roll_No,
name,
stream,
percentage,
class_rank,
overall_rank )
I wish to query something like
SELECT *
FROM student
WHERE stream = 'science'
The expected result would be the students arranged in descending order of their rank. A requirement is that I can not specify order by clause in the query itself.
This should be achieved by an index on (stream , order by class_rank desc). Is this achievable in oracle?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果不指定 ORDER BY 子句，Oracle 不保证返回行的顺序。这个要求没有意义。
您可能会很幸运，发现 Oracle 选择的查询计划恰好按照您想要的顺序返回行。但这是一个运气问题——Oracle 明天可能会选择不同的查询计划，或者 Oracle 版本升级可能会导致结果发生变化。例如，当 Oracle 的新版本添加了更有效的分组算法（没有对结果进行排序的副作用）时，多年来依赖 GROUP BY 子句对结果进行排序的人们感到苦恼。
If you do not specify an ORDER BY clause, Oracle does not guarantee the order in which rows are returned. The requirement does not make sense.
You might get lucky and find that Oracle chooses a query plan that happens to return the rows in the order you want. But that would be a matter of luck-- Oracle could choose a different query plan tomorrow or an Oracle version upgrade may create the results to change. For example, folks that relied for years on the GROUP BY clause ordering the results as a side effect were distressed when a new version of Oracle added a more efficient grouping algorithm that didn't have the side effect of ordering the results.
我让它工作了，
所以当我从学生那里触发 select * 时，stream =？查询将使用上面的索引，它将返回我想要的结果。
我认为如果我不升级 Oracle，它几乎总是安全的。即使在升级之后，oracle 改变索引选择方式的可能性也很小。
I got it working
so when ever i fire a select * from student where stream =? query the above index will be used and it will return me the desired result.
and i think its almost always safe if i am not upgrading oracle. and even after upgrade there is very low probabilty that oracle will change the way indexes are picked.