|
|
Re: Use of index vs full table scan [message #601036 is a reply to message #601035] |
Fri, 15 November 2013 00:25 |
|
Can u please elaborate the situation wherein the full table scan is better than the use of indexes. In one of the articles on full table scans i read, it was mentioned that indexes should be used if the number of rows returned are less and full table scans are better if the number of rows returned are large in numbers. But, what can be considered as small amount of data, and which data should be considered as large amount of data?
[Updated on: Fri, 15 November 2013 00:28] Report message to a moderator
|
|
|
Re: Use of index vs full table scan [message #601037 is a reply to message #601036] |
Fri, 15 November 2013 00:36 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Read the link that I posted.
Consider what an index is and what a full table scan is.
Think about it as a book.
If I look at Tom Kyte's Expert One on One. It has >1200 Pages. If I want to read the section on Parallel queries, A full table scan start from page one, and turn each page until I find the page(s) that I look for, read those pages, then continue to turn the pages until I get to the end (just to make sure I don't miss any other pages relevant to the subject).
That would be an extremely costly (in terms of time and effort) process. So I'd use the Index. I'd go to the index section, scan through the index until I found the subject matter that I was interested in, note the pages that have topics related to parallel queries, then go directl to those pages. Much faster and more efficient, yes?
Now consider the scenario where I want to read a large section of the book. Multiple topics, hundreds of pages. If I went to the index for every single topic, I'm doing extra reads i.e. I have to go to the index, scan through the index, then go to the main part of the book to actually get the info I want. I can very quickly scan through each page of the book, discarding/ignoring the pages that I'm not interested in and slowing down to read the ages that I am interested in (a full scan of the book).
In addition to the page I linked you to, take a look at the Performance and tuning documentation.
Oh, and have the courtesy to respond to Littlefoot, she put effort into responding to your question about Views, you have, so far, failed to respond.
|
|
|
Re: Use of index vs full table scan [message #601038 is a reply to message #601036] |
Fri, 15 November 2013 00:37 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
kalpeshrmopkar wrote on Fri, 15 November 2013 06:25Can u please elaborate the situation wherein the full table scan is better than the use of indexes. In one of the articles on full table scans i read, it was mentioned that indexes should be used if the number of rows returned are less and full table scans are better if the number of rows returned are large in numbers. But, what can be considered as small amount of data, and which data should be considered as large amount of data?
What document did you read that contained that info (it's right, if a little vague)?
|
|
|
|
|
|