[ORACLE] select * from t1 sort; sort ????

 수업시간에 select * from t1 sort; 라는 문구를 수행 했을때 결과 값이 제대로 나온다는 이상한 이야기가 나왔다. sort 라는 syntax가 있던가;;; 왜 되냐고 -ㅅ-;;;;

일단 상황 발생

SCOTT@192.168.0.10:1521/orcl> drop table t1 purge;

테이블이 삭제되었습니다.

SCOTT@192.168.0.10:1521/orcl> create table t1 (no number);

테이블이 생성되었습니다.

SCOTT@192.168.0.10:1521/orcl> insert into t1 values (3);

1 개의 행이 만들어졌습니다.

SCOTT@192.168.0.10:1521/orcl> insert into t1 values (1);

1 개의 행이 만들어졌습니다.

SCOTT@192.168.0.10:1521/orcl> insert into t1 values (2);

1 개의 행이 만들어졌습니다.

SCOTT@192.168.0.10:1521/orcl> commit;

커밋이 완료되었습니다.

SCOTT@192.168.0.10:1521/orcl> select * from t1 ;

        NO
---------
         3
         1
         2



SCOTT@192.168.0.10:1521/orcl> select * from t1 sort;

        NO
----------
         3
         1
         2



SCOTT@192.168.0.10:1521/orcl> create index t1_no_idx on t1(no);
인덱스가 생성되었습니다.

SCOTT@192.168.0.10:1521/orcl> select * from t1 sort;
        NO
----------
         3
         1
         2


SCOTT@192.168.0.10:1521/orcl> alter table t1 add primary key(no);
테이블이 변경되었습니다.

SCOTT@192.168.0.10:1521/orcl> select * from t1 sort;
        NO
----------
         1
         2
         3

결론은... sort 는 의미 있는 단어가 아닌 t1테이블의 alias 였다는거다. 헐.. 

SQL> explain plan for select * from t1 sort;

Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 3637456918

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

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |              |     3 |    39 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | T1111_NO_IDX |     3 |    39 |     1   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / SORT@SEL$1

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "SORT"@"SEL$1" ("T1"."NO"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Column Projection Information (identified by operation id):

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

   1 - "SORT"."NO"[NUMBER,22]

Note

-----

   - dynamic sampling used for this statement

35 rows selected.

SQL> explain plan for select * from t1;

Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 3637456918

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

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |              |     3 |    39 |     1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | T1111_NO_IDX |     3 |    39 |     1   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / T1@SEL$1

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."NO"))

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Column Projection Information (identified by operation id):

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

   1 - "T1"."NO"[NUMBER,22]

Note

-----

   - dynamic sampling used for this statement

35 rows selected.