Example of Oracle high water level line shrinking

Example 1 of Oracle high water level line shrinking. background We know this situation. In oracle, assume that table A originally had rows of data, and then deleted rows of data, leaving only rows of data, the time for full table scan to Table A has not changed (before and after deletion ). This is caused by the high water level line in oracle. In layman's terms, if we want to install 1000 l of water, we need 999 barrels. Then I dumped barrels of water, but I didn't recycle them. At this time, I still need to find water in buckets one by one. The example below is to describe such a phenomenon and introduce how to solve it (that is, to recycle the bucket. step 1. log on to the CLI as a sys user. CREATE test TABLE [SQL] -- CREATE test TABLE T DROP TABLE t; CREATE TABLE t (id NUMBER, n1 NUMBER, n2 NUMBER, pad VARCHAR2 (4000) tablespace users; -- INSERT data INTO 10000 rows insert into t SELECT rownum AS id, 1 + mod (rownum, 251) AS n1, 1 + mod (rownum, 251) AS n2, dbms_random.string ('P', 255) AS pad FROM dual connect by level <= 10000 order by dbms_random.value; 3. collect statistics [SQL] -- collect Set table T object statistics BEGIN dbms_stats.gather_table_stats (ownname => user, tabname => 'T', estimate_percent => 100, method_opt => 'for all columns size skewonly ', cascade => TRUE); END;/-- collect plan_executetion_statistics (information about execution plan execution) alter session set statistics_level = all; 4. the number of data rows and logical readings obtained during the first full table scan [SQL] SELECT/* + full (t) */* FROM t WHERE n2 = 19; select * from v $ SQL sqls where sqls. SQL _TEXT like '% SELECT /* + Full (t) */* FROM t WHERE n2 = 19% '-- the parameter is the SQL _id select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets from v $ SQL _plan_statistics stat where stat. SQL _ID = 'chk7agdpy3uqh': last_output_rows: 40, last_cr_buffer_gets: 436, which indicates that 40 rows of data are returned, 436 logical reads are generated (Here we assume that a logical read is almost a block, that is, the prefetch parameter is set to a large value, so that a block is read successfully once ), that is to say, we read about 436 blocks. 5. DELETE most of the data in the table (about 9960 rows) and re-query the number of rows and logical reading [SQL] DELETE t WHERE n2 <> 19; SELECT/* + full (t) */* FROM t WHERE n2 = 19; -- the parameter is SQL _id select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets from v $ SQL _plan_statistics stat where stat. SQL _ID = 'chk7agdpy3uqh' the display on my computer is the same as that on the previous one, indicating that I still read 436 blocks. However, there are already a vast majority of blocks without data, and there is no need to read these blocks without data. 6. SHRINK the high water level line [SQL] ALTER TABLE t ENABLE ROW MOVEMENT; ALTER TABLE t SHRINK SPACE; 7. perform the third full table scan and re-query the number of rows and logical reading [SQL] SELECT/* + full (t) */* FROM t WHERE n2 = 19; select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets from v $ SQL _plan_statistics stat where stat. SQL _ID = 'chk7agdpy3uqh' my computer displays last_output_rows: 40, last_cr_buffer_gets: 4. This time, only four logical reads are performed, and all the data blocks deleted are released. 8. drop Test TABLE [SQL] DROP TABLE t; PURGE TABLE t;