Oracle Database Discussions

1 select count(*)
2* from big_emp e where hiredate >= to_date('1980-01-01', 'YYYY-MM-DD') and hiredate <= to_date('1983-12-31', 'YYYY-MM-DD')

COUNT(*)
----------
11971

---------------------------------------------------------

SQL> create index i_big_emp_hiredate on big_emp(hiredate);

Index created.

SQL> set autot trace
SQL> select empno, ename, hiredate
2 from big_emp e where hiredate >= to_date('1980-01-01', 'YYYY-MM-DD') and hiredate <= to_date('1983-12-31', 'YYYY-MM-DD') ;

11971 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11766 | 218K| 19 |
|* 1 | TABLE ACCESS FULL| BIG_EMP | 11766 | 218K| 19 |
-------------------------------------------------------------

SQL> drop index i_big_emp_hiredate;

Index dropped.

SQL> create index i_big_emp_hiredate on big_emp(hiredate desc);

Index created.

SQL> select empno, ename, hiredate
2 from big_emp e where hiredate >= to_date('1980-01-01', 'YYYY-MM-DD') and hiredate <= to_date('1983-12-31', 'YYYY-MM-DD') ;

11971 rows selected.


Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 551 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 29 | 551 | 4 |
|* 2 | INDEX RANGE SCAN | I_BIG_EMP_HIREDATE | 53 | | 2 |
----------------------------------------------------------------------------------

***********************************************************************************************

i have 2 questions

1. In "Expert one-on-one Oracle", Tom said, there is no deference between ASC and DESC index in case of one column because Oracle can just read in reverse order. but my test made me confused. why Oracle did "full table scan" only in ASC index???

2. using "set autot trace" command. i believed the the "Rows" column mean the rows that Oracle access. Can you explain why the rows are 29(DESC) and 11766(ASC) in spite of the result is 11971. what is the exact meaning of "Rows" column in execution plan