Oracle: TABLE ACCESS FULL with Primary key?

Question

There is a table:

CREATE TABLE temp
(
   IDR decimal(9) NOT NULL,
   IDS decimal(9) NOT NULL,
   DT date NOT NULL,
   VAL decimal(10) NOT NULL,
   AFFID decimal(9),
   CONSTRAINT PKtemp PRIMARY KEY (IDR,IDS,DT)
)
;     

Let's see the plan for select star query:

SQL>explain plan for select * from temp;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |     1 |    61 |     2   (0)|
|   1 |  TABLE ACCESS FULL| TEMP |     1 |    61 |     2   (0)|
---------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

11 rows selected.

SQL server 2008 shows in the same situation Clustered index scan. What is the reason?

Answer 1

select * with no where clause -- means read every row in the table, fetch every column.

What do you gain by using an index? You have to go to the index, get a rowid, translate the rowid into a table offset, read the file.

What happens when you do a full table scan? You go the th first rowid in the table, then read on through the table to the end.

Which one of these is faster given the table you have above? Full table scan. Why? because it skips having to to go the index, retreive values, then going back to the other to where the table lives and fetching.