CODEWITHSUNDEEP
oracle-database
neha
neha

Reputation: 61

When the num_rows is null in all_tab_statistics in oracle

I want to know when the num_rows is set to null in all_tab_statistics table.

One of my queries is taking a bad plan because the table is having num_rows as blank.

The table is partitioned. So the table is freshly created and ideally, it should have 0 rows. What could be the reason for this and how to avoid this.

Also when we are running the job on the freshly created table which is having blank num_rows(in stats) the rows got inserted in between the process is more than 0 but since we can't analyze the table between the process it took wrong plan (because of blank num_rows)

Upvotes: 1

Views: 1465

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

I have created a table

CREATE table empty
(
  dummy NUMBER
);

First time running this query gives

select table_name, num_rows from sys.all_tab_statistics where owner = 'HR' and table_name = 'EMPTY';

TABLE_NAME  NUM_ROWS
----------  --------
EMPTY       <NULL>

Then I inserted some rows

INSERT INTO empty SELECT SALARY FROM EMPLOYEES;
COMMIT;

And got the same result

TABLE_NAME  NUM_ROWS
----------  --------
EMPTY       <NULL>

Running this statement populated the row for EMPTY table in all_tab_statistics

ANALYZE TABLE empty COMPUTE STATISTICS;

I got

TABLE_NAME  NUM_ROWS
----------  --------
EMPTY       107

Upvotes: 2

Related Questions