One question.. If I am assuming that our indexes are from B+ Tree, and in B+ Tree, on leaf nodes, we always have all the indexes available, and these nodes are also connected as a linkedlist. now if I am able to find the specific index of let's say empId=5000, and through that linked list, i can list all previously existing employees (which only will be 90% and not the full table). Won't this make our range scanning better?
Thanks for information and I liked your explanation. Hi I have a table like emp have 5000 employee records . I have two batches one is daily and another one is monthly . Daily batch result set is 10% and monthly batch result set is 90% of the table and both batches are using same column in where clause. Now my question is , any way to force full table scan only during monthly batch ?
You can always force SQL query to go for FULL table scan by using HINTS: select /*+ FULL(emp) */ EMPNO, Ename from emp; Also, you can force SQL query to use a specific index again by using HINTS: select /*+ INDEX(emp(index_col)) */ EMPNO, Ename from emp;
Hi Sir, sometime more index will impact that query become slower compare with a significant index. How can we differential that index will burden that query even though the index output is 5% only.
the way you said if we mention empid < 5000 then it will use index scan which may not be preferable. how can i use full table scan in this scenario. pls tell the process.