I have emp table (empno, ename, job, mgr, hire_date, sal, deptno) with no index at all. If i run
select * from emp
i get the rows ordered by empno.
Now i create a clustered index like this:
Create clustered index name on emp(ename)
If i run select * again, the rows are now ordered by ename.
Now if i drop the index. drop index name on emp
I would expect to see the rows ordered back by empno, like before creating the index.
Instead I still see the rows ordered by ename, as if the index were still there.
Is there a way to see the rows ordered back by empno as before without using "order by empno?".
I tried to log off and connect again to make a refresh, but did not work.
The only way was to drop and recreate the table.
Maybe is there a parameter in the table definition that is changed when the index was created, and it is not updated when the index is deleted?
It seems to me that there is a default method for displaying the data before the index, which is not preserved / restored when the index is deleted