18.2 Table Access Ratios

---

Full scanning small static tables can be more efficient than reading both index and table. Full scans on large tables are often executed in data warehouses and for reporting. Full table scans are not necessarily a bad thing. However, poorly tuned SQL code can cause unwanted full table scans. Many applications inadvertently retrieve all table rows using views and subsequently filter view results. Querying a view will always execute the SQL code underlying the view in its fullest form, applying data retrieval SQL code filtering requirements only to the result of the view. So full table scans can be a very bad thing as well.

There are five ratios that might be useful. They are as follows:

• short scan /(short scan + long scan)

• short scan /(short scan + long scan + by ROWID)

• long scan /(short scan + long scan + by ROWID)

• by ROWID /(short scan + long scan + by ROWID)

• (short_scan + by ROWID)/(short scan + long scan + by ROWID)

  • short scan.   Implies full table scan of a small table. Good.

  • long scan.   Implies full table scan of a large table. Bad!

  • by ROWID.   Implies access to a table row via an index unique hit, a range scan or a full index scan. Good or bad depending on table size and usage.

The statistics we are interested in are as follows:

SELECT value, name FROM V$SYSSTAT WHERE name IN       ('table fetch by rowid', 'table scans (short tables)'       , 'table scans (long tables)');

Tip 

For the statistic table scans (short tables) the table must have the CACHE option set for the table (ALTER TABLE table CACHE).

SELECT 'Short to Long Full Table Scans' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN    ('table scans (short tables)', 'table scans (long        tables)')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Short Table Scans ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN    ('table scans (short tables)', 'table scans (long       tables)' , 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Long Table Scans ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT    WHERE name = 'table scans (long tables)') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name    IN ('table scans (short tables)', 'table scans (long        tables)', 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Table by Index ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch    by rowid') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name     IN ('table scans (short tables)', 'table scans (long        tables)', 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL UNION SELECT 'Efficient Table Access ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name    IN ('table scans (short tables)','table fetch by rowid')) / (SELECT SUM(value) FROM V$SYSSTAT WHERE name      IN ('table scans (short tables)', 'table scans (long        tables)', 'table fetch by rowid')) * 100, 2)||'%' "Percentage" FROM DUAL;

As you can see from the results below table and index access in my Accounts schema could be fairly well tuned. Most of the full table scans on large tables are probably a result of ad hoc counting of all the rows in those tables. There is a lot of short table scanning which offsets the table by index access. Overall, using short full table scans as opposed to index access by ROWID pointers can give better efficiency.

Ratio                               Percentage ---------------------------------   ---------- Efficient Table Access                  99.97% Long Table Scans                          .03% Short Table Scans                       31.16% Short to Long Full Table Scans          99.92% Table by Index                          68.81%

---