A reader, May 26, - am UTC. Sorry, still didn't get you. But in my example i have not tried to use any of the mentioned tricks for Optimizer to choose the index? May 26, - am UTC. The query can and will use indexes when appropriate. That was the purpose of the link. Also, you just say "oracle doc says so" but you never quoted a reference. I would like to see it in context. It will either be: a there is a documentation bug which needs be fixed b you have misread what they were saying - which could be either because 1 what they were saying was not clear 2 you misread it So, post the link to otn.
A reader, May 27, - am UTC. Its quite that my interpretation of the documented statement may differ. Active Oldest Votes. Improve this answer. I read this article, this is why I'm asking why this is working this way.
It's weird. Well think about this, in a index how can they distinct from all NULL's? Add a comment. Allan Allan Well, we can't say that it definitely would have used the index, just that it could have. I agree, but he did state in the question " even a hint didn't help " — A. I am not sure the first query is pertinent in terms of index usage, at least the second could.
Farid Farid 2, 16 16 silver badges 13 13 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Thanks Tom, I know the idea is really bad, I donot even try to do this, but need to get my point clear and get my spine strong to make understand some others too.
Thanks for your help. Still now the question remains open. Please suggest any alternative strategy to deal. October 03, - am UTC. A reader, October 04, - am UTC.
Anything for a single-column unique index with NULLs? Hi Tom, I have a problem similar to that of the original poster, only I don't have a non-null column to help me out. In Oracle 9. IDs are generated by the third-party application, and PNOs have some internal structure that looks different from that of the IDs, but if something can happen, it will.
Additionally, I don't like it for these other reasons: a this index would be unnecessarily "fat", especially considering that the PNO column will be sparse, and all IDs are 24 characters long. Of course I can use a view to work around this, but it will have to return IDs in a column named PNO, and then only for some of the rows - yuck!
Is there a real solution for this problem, "more elegant or effective" than the shadow table and the trigger suggested by Sebastian in his original post? In this scenario we are going to replace the null value of one field with value from another field or column. In this example we are going to find the distinct cities present in the city column of the table employee. We are going to use NVL because we have a null value present in the city column and we are going to replace that using NVL function as distinct function will return null also as a unique value.
In this article we have seen about the definition of NVL function and its syntax. We also saw working of the function in Oracle and later on in the article we went through various scenarios along with examples. This is a guide to Oracle NVL. Here we discuss the introduction, how NVL function work in oracle? You may also have a look at the following articles to learn more —.
0コメント