science

Full table scan

Full table scan is a science topic covered in the lgStudy science library. This page brings together a partial reference excerpt, illustrations, worked examples, real-world applications and a short study plan, so you can understand Full table scan rather than just read about it. In short: A full table scan (also known as a sequential scan) is a scan made on a database where each row of the table is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition. Full table scans are usually the slowest method of scanning a table due to the heavy amount of I/O reads required from the disk which consists of multiple seeks as well as costly disk to memory tran…

Key takeaways

Full table scan belongs to science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Full table scan to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Full table scan from memory before moving on to harder problems.

Reference excerpt

A full table scan (also known as a sequential scan) is a scan made on a database where each row of the table is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition. Full table scans are usually the slowest method of scanning a table due to the heavy amount of I/O reads required from the disk which consists of multiple seeks as well as costly disk to memory transfers.

Overview In a database, a query that is not indexed results in a full table scan, where the database processes each record of the table to find all records meeting the given requirements. Even if the query selects just a few rows from the table, all rows in the entire table will be examined. This usually results in suboptimal performance but may be acceptable with very small tables or when the overhead of keeping indexes up to date is high.

When the optimizer considers a full table scan The most important factor in choosing depends on speed. This means that a full table scan should be used when it is the fastest and cannot use a different access path. Several full table scan examples are as follows.

No index The optimizer must use a full table scan since no index exists. Small number of rows The cost of full table scan is less than index range scan due to small table. When query processed SELECT COUNT(*), nulls existed in the column The query is counting the number of null columns in a typical index. However, SELECT COUNT(*) can't count the number of null columns. The query is unselective The number of return rows is too large and takes nearly 100% in the whole table. These rows are unselective. The table statistics does not update The number of rows in the table is higher than before, but table statistics haven't been updated yet. The optimizer can't correctly estimate that using the index is faster. The table has a high degree of parallelism The high degree of parallelism table distorts the optimizer from a true way, because optimizer would use full table scan. A full table scan hint The hint lets optimizer to use full table scan.

Examples The first example shows a SQL statement that returns the name of every fruit in the fruits table whose color is red. If the fruits table does not have an index for the color column, then the database engine must load and examine every row within fruits in order to compare each row's color to 'red':

The second example shows a SQL statement which returns the name of all fruits in the fruits table. Because this statement has no condition - no WHERE clause - the database engine will use a table scan to load and return the data for this query even if the fruits table has an index on the name column because accessing - i.e. scanning - the table directly is faster than accessing the table through the extra abstraction layer of an index:

The third example is a counter-example that will almost certainly cause the SQL engine to use an index instead of a table scan. This example uses almost the same query as the previous one, but adds an ORDER BY clause so that the returned names will be in alphabetical order. Assuming that the fruits table has an index on the name column, the database engine will now use that index to return the names in order because accessing the table through the extra abstraction layer of the index provides the benefit of returning the rows in the requested order. Had the engine loaded the rows using a table scan, it would then have to perform the additional work of sorting the returned rows. In some extreme cases - e.g. the statistics maintained by the database engine indicate that the table contains a very small number of rows - the optimizer may still decide to use a table scan for this type of query:

Pros and cons Pros:

The cost is predictable, as every time database system needs to scan full table row by row. When table is less than 2 percent of database block buffer, the full scan table is quicker. Cons:

Full table scan occurs when there is no index or index is not being used by SQL. And the result of full scan table is usually slower that index table scan. The situation is that: the larger the table, the slower of the data returns. Unnecessary full-table scan will lead to a huge amount of unnecessary I/O with a process burden on the entire database.

See also Database Database index Optimizing compiler Query plan Query optimization SQL

References

External links Oracle full table scan tips

Worked examples

Example 1 — a first encounter with Full table scan

Start with the simplest possible case. Write down what Full table scan claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Full table scan before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Full table scan ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Full table scan

In research: Full table scan appears in science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Full table scan in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Full table scan is common in secondary-school and first-year university syllabi. It links to neighbouring topics Database theory, so understanding it makes those chapters shorter.
In everyday life: Look for Full table scan outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

Affiliate

Preply — study more efficiently by working with a personal tutor. 50% off.

How to study Full table scan in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Full table scan means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Full table scan out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Full table scan in simple terms?

Why does Full table scan matter?

Because it connects several science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Full table scan?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Full table scan.