oracle query explain plan

A useful sample of query explain plan with oracle; usefull to find how optimizer works with queries.

Here my table structure

CREATE TABLE "schema_REP"."LOGICAL_AGGREGATION"
  (
    "ID"                   NUMBER(19,0) NOT NULL ENABLE,
    "FULLQUALIFIED_NAME"   VARCHAR2(100 CHAR) NOT NULL ENABLE,
    "NAME"                 VARCHAR2(100 CHAR) NOT NULL ENABLE,
    "TYPE"                 VARCHAR2(100 CHAR),
    "SUB_TYPE"             VARCHAR2(100 CHAR),
    "FAMILY"               VARCHAR2(100 CHAR),
    "VERSION"              VARCHAR2(100 CHAR),
    "LOCATION"             VARCHAR2(100 CHAR),
    "STATO_AMMINISTRATIVO" VARCHAR2(100 CHAR),
    "SPEC_INFO"            VARCHAR2(255 CHAR),
    "MODEL"                VARCHAR2(50 CHAR),
    "VENDOR"               VARCHAR2(50 CHAR),
    "CREATING_STATUS"      VARCHAR2(50 CHAR),
    PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EMXDATA01" ENABLE,
    UNIQUE ("FULLQUALIFIED_NAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EMXDATA01" ENABLE
  )


Here sample data :
1    sbc21102011    sbc21102011    SBC        ENTITY    4.2        ON        6.1.0    ACME    Finished
2    issw21102011    issw21102011    ISSW        ENTITY    22.20.64        OFF    issw21102011    ISSW    ITALTEL    Finished
3    MGX1    MGX1    MGX_Site        ENTITY    xx        OFF        MGX    CISCO    Under Configuration
4    CAT001    CAT001    CAT        ENTITY    12.1(14)        OFF        C3550    CISCO    Finished

Now connect as sys   user and type 1th query:

explain plan for (select * from schema_rep.logical_aggregation where fullqualified_name like '%SBC%')

> plan FOR riuscito/a.

And see PLAN_TABLE$ under sys expecially columns operation and optimizer cpu io

 You will find a full table scan .

if query changes as follow

explain plan for (select * from schema_rep.logical_aggregation where fullqualified_name = 'SBC')

You will find a index scan by row id (much faster)