When empty is not empty

To quote my previous blog entry
"I haven't found a query yet that runs slowly when all the tables are empty."

Actually, some empty tables may actually be quite big, and therefore slow. Just because a table has no rows in it, doesn't mean it doesn't have a lot of space allocated to it.
If the table did have a lot of rows which were deleted, the empty space is not reclaimed and a full scan of the table would necessitate reading all that space.

SQL> create table size_test (col_1 varchar2(2000));

Table created.

SQL>
SQL> select bytes, blocks from user_segments
2 where segment_name = 'SIZE_TEST';

BYTES BLOCKS
----- ------
65536 8

SQL>
SQL> insert into size_test select rpad('A',2000,'B') from all_tab_colums

41060 rows created.

SQL>
SQL> select bytes, blocks from user_segments
2 where segment_name = 'SIZE_TEST';

BYTES BLOCKS
--------- ------
117440512 14336

SQL>
SQL> delete from size_test;

41060 rows deleted.

SQL>
SQL> select bytes, blocks from user_segments
2 where segment_name = 'SIZE_TEST';

BYTES BLOCKS
--------- ------
117440512 14336

If the table is empty, a TRUNCATE will reclaim all that space.
If not, one way is to use ALTER TABLE ... MOVE;
Watch out though, as this will change the ROWIDs of all the rows in the table. Indexes will need to be rebuilt and materialized views fully refreshed.

SQL> alter table size_test move;

Table altered.

SQL> select bytes, blocks from user_segments
2 where segment_name = 'SIZE_TEST';

BYTES BLOCKS
----- ------
65536 8