Oracle: TABLE ACCESS FULL with Primary key?

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 开发者_如何学JAVAversion

11 rows selected.

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

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.

To answer this more simply without mumbo-jumbo, the reason is:

Clustered Index = Table

That's by definition in SQL Server. If this is not clear, look up the definition.

To be absolutely clear once again, since most people seem to miss this, the Clustered Index IS the table itself. It therefore follows that "Clustered Index Scan" is another way of saying "Table Scan". Or what Oracle calls "TABLE ACCESS FULL"