We have deleted the all records of the table and after that we execute the select * from that table. Why this query takes time..?
2 Answers
If you are planning deleting all records in your table, "TRUNCATE" might be an alternative. Just remember that truncate will do an implicit commit (A DDL command).
The High Watermark Syndrome (HWM)
You are experiencing the "HWM syndrome". If you insert one million rows into a table and do a select count(*) from table. Thereafter you delete all rows. When you now do a new select count(*) from table, it takes just as long time as with a million rows in it. What!! How is this possible??
"Hey, there is nothing in there - and it takes forever. This database is terrible, Oracle isn't performing, MariaDB is so much better!" (The HWM syndrome)
As always it boils down to understanding how the Oracle machinery is working.
No blocks are deleted when doing delete from table. They are saved for future inserts and updates. When doing a full table scan (no index scan) all blocks will will be read from the beginning until HWM. A truncate operation will reset the HWM.
You can read more about HWM here
Best of luck!
do select in ALL_TABLES and check the row_count , if it is not 0 then looks like stats are not updated. If not 0 then run
EXEC DBMS_STATS.GATHER_TABLE_STATS ('Schema_NAME', 'TABLE_NAME');
SELECT * from all_Tables where table_name = 'table_name'
then run the select statment
Related