Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

ROWNUM

Tested on an Oracle 9 database. ROWNUM is a pseudo column which you can include in the output  from a SQL query:

SQL> select rownum, object_id from dba_objects

  2  where rownum < 6

  3  /

    ROWNUM  OBJECT_ID

---------- ----------

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

The first rownum is always 1, the second is always 2 and so on. So if you do not have a rownum of 1 at the start, you will not get any output at all. The following query selects the same rows as the one above:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 1 and 5

  3  /

    ROWNUM  OBJECT_ID

---------- ----------

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

But the next one does not select rows 2 through 5. It produces no output at all as the first row of output cannot have a rownum of 1:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 2 and 5

  3  /

no rows selected

SQL>

Group By ... Having

This post shows 2 pieces of SQL. The first has a group by, which I have already shown in other posts. The second restricts the output to show groups of 50 or more. You are not allowed to use a where clause to filter the output from a group by statement, you must use having instead, as shown in the example:  

SQL> select object_type, count(*)
  2  from dba_objects
  3  group by object_type
  4  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
CLUSTER                    10
CONSUMER GROUP              4
DATABASE LINK              12
DIRECTORY                   1
EVALUATION CONTEXT          1
FUNCTION                  129
INDEX                     992
INDEX PARTITION            25
LIBRARY                    62
LOB                        52
MATERIALIZED VIEW           2
OPERATOR                    2
PACKAGE                   321
PACKAGE BODY              313
PROCEDURE                 183
QUEUE                       8
RESOURCE PLAN               3
SEQUENCE                  168
SYNONYM                  1782
TABLE                     830
TABLE PARTITION            27
TRIGGER                    90
TYPE                      480
TYPE BODY                  21
VIEW                     2414

25 rows selected.

SQL> select object_type, count(*)
  2  from dba_objects
  3  group by object_type
  4  having count(*) > 50
  5  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
FUNCTION                  129
INDEX                     992
LIBRARY                    62
LOB                        52
PACKAGE                   321
PACKAGE BODY              313
PROCEDURE                 183
SEQUENCE                  168
SYNONYM                  1782
TABLE                     830
TRIGGER                    90
TYPE                      480
VIEW                     2414

13 rows selected.

SQL>