- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Fundamental question about "Full table Scan"
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 03:18 AM
тАО12-01-2003 03:18 AM
I just want to confirm the following cases:
1. select * from sales;
2. select * from sales where order_date > '01-Nov-2003';
In the no index build situation, is that 1 and 2 will perform a full table scan ?
Besides, for 2, is that only the "order_date" colume will be scanned completely rather than all the columes ?
On the other hand, if index is built on order_date" colume, is that 2 will be benefited by the index scan through the b-tree structure ?
Cheers,
Chris,
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 03:27 AM
тАО12-01-2003 03:27 AM
Re: Fundamental question about "Full table Scan"
without index 1 and 2 produce a full table scan. Since there is no Index, where it can compare the search text to, it has to read every row anw thus with it every column. If an index is used depends on a few things. If the table is small enough, it will be a full table access anyway, because the overhead to use an index is not justified. Also, when the expected number of rows is a substantial portion of the table, because there are not many different values in the key, the index will not be used.
hope, you get an idea,
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 03:43 AM
тАО12-01-2003 03:43 AM
Re: Fundamental question about "Full table Scan"
Please not that in modern RDBMSs all i/o is done by the page, not by the record. So if your record is 1kb long and your page size is 4kb, you will get 1 i/o per 4 records. This will then be reduced by the read-ahead parameters you have configured for your instance.
If you have an index on the order_date column, it will be able to scan the index pages directly, starting with the first relevent page. Of course each index leaf will then point to the data page (from disk if it is not already in buffers) so that it can read the result set sales.*
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 03:46 AM
тАО12-01-2003 03:46 AM
Re: Fundamental question about "Full table Scan"
Now if there is an index on date, that still does not garantuee storage order. So Oracle may have to go back and forward over the data pages to agther all matching rows. Therefor Oracle will decided to jsut read all (tablescan) evern with index (unless it knows only a few rows will results.
Now if the table is organized by that index, then it can do a partial scan.
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 04:49 AM
тАО12-01-2003 04:49 AM
Re: Fundamental question about "Full table Scan"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 05:08 AM
тАО12-01-2003 05:08 AM
Re: Fundamental question about "Full table Scan"
one more thing. For the optimizer to choose the right path, you have to keep the statistics of the tables and indexes up todate.
greetings,
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 06:02 AM
тАО12-01-2003 06:02 AM
Re: Fundamental question about "Full table Scan"
a. no index: FTS for both queries
b. with specified index:
-1. again FTS
-2. - the index will be scanned for matching order_dates, and for matching values the row will be retrieved in the table by rowid.
- if cost-based optimizer is used & statistics are available a Full Table Scan will be chosen if it's a small table.
regards,
Thierry.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 08:01 PM
тАО12-01-2003 08:01 PM
Re: Fundamental question about "Full table Scan"
Thanks for the input,
What about if the following statement is issued ?
select order_date from sales where order_date > '01-Nov-2003';
Please evalute 2 cases - with index and without index on the order_date field !!
What I am thinking for without index on the above case will be a full table scan on just the "order_date" column !! Is that correct ??
Please clarify,
Cheers,
Chris,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 08:52 PM
тАО12-01-2003 08:52 PM
Solutioneven if you select only one column from a row, Oracle has to get the entire row into memory to extract the column and without index to minimize the number of rows to check this means full table access.
greetings,
Michael
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-01-2003 09:36 PM
тАО12-01-2003 09:36 PM
Re: Fundamental question about "Full table Scan"
But see the responses above, which tell you that you may still get a full table scan regardless of the index - you must test it.
Without an index it will read in the whole table page by page, then just look at the order_date column (by offset) in each record in each page. Its caused by the logical separation of records from physical data pages.
But, why only select order_date when you should know what the dates are between then and now? Are people pre-placing orders for the future? It sounds like you really want something useful, like the order_id, which you need an index on order_date for, to avoid FTS.