TUNING FULL TABLE SCANS

CREATE TABLE sh.MY_SALES_ALL AS
SELECT ROWNUM AS ID, X.* FROM sh.SALES X;


CREATE TABLE sh.MY_SALES_2 AS
SELECT * FROM sh.MY_SALES_ALL NOLOGGING;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_ALL',
estimate_percent => 100,
method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
estimate_percent => 100,
method_opt => 'for all columns size 1');

SELECT BLOCKS FROM DBA_TABLES

WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');

DELETE FROM sh.MY_SALES_2 WHERE MOD(ID,100) <> 0;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
estimate_percent => 100,
method_opt => 'for all columns size 1');

SELECT BLOCKS FROM DBA_TABLES
WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');

SET AUTOT TRACE EXP STAT

SELECT * FROM sh.MY_SALES_ALL
WHERE TIME_ID > TO_DATE('20011220', 'YYYYMMDD');

SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_SALES_2
WHERE TIME_ID > TO_DATE('20011220', 'YYYYMMDD');

SET AUTOT OFF

SELECT BLOCKS FROM DBA_TABLES
WHERE TABLE_NAME IN ('MY_SALES_ALL', 'MY_SALES_2');

Shrink space on the MY_SALES_2 table:

ALTER TABLE sh.MY_SALES_2 ENABLE ROW MOVEMENT;

ALTER TABLE sh.MY_SALES_2 SHRINK SPACE;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_SALES_2',
estimate_percent => 100,

method_opt => 'for all columns size 1');

You can see reduced number of physical reads.