Hi Tom.

I notice very strange thing about indexes on table with no rows.
Here is listing:



SQL> SET AUTOTRACE OFF
SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> CREATE TABLE t1 NOLOGGING AS SELECT CAST(MOD(ROWNUM, 2) AS NUMBER) col1 FROM all_tab_columns;

Table created.

SQL>
SQL> CREATE INDEX ind1 ON t1(col1) NOLOGGING;

Index created.

SQL>
SQL> SET AUTOTRACE ON EXPLAIN STATISTICS
SQL>
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 0;

COUNT(*)
----------
53706


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
99 consistent gets
98 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


>>> 99 consistent gets. It is OK. But after delete......


SQL>
SQL> DELETE FROM t1;

107412 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'T1'
2 1 TABLE ACCESS (FULL) OF 'T1'




Statistics
----------------------------------------------------------
162 recursive calls
328838 db block gets
250 consistent gets
104 physical reads
44464008 redo size
620 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
107412 rows processed

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 0;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
99 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


>>>>> Wow! To read 0 rows index we need again 99 consistent gets!


SQL>
SQL> ALTER INDEX ind1 REBUILD;

Index altered.

SQL>
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 0;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL>

I understand why only 4 consistent gets in last select (after rebuild) but why there are 99 after delete?
Why empty index read requires same amount of consistent gets as the full one?

We need to clean many tables with foreign keys to each other. Can not use truncate - we clean 90-99% of table data. Do we need to rebuild indexes after delete to speedup further FK check on next table delete?