為什麼SELECT查詢中應避免使用*?

LuiseDalian發表於2014-01-13

當想在SELECT語句中列出所有的列時,使用*非常方便。但這是一種低效的方法。

Oracle在解析的過程中會將*依次轉換為具體的列名,這是通過查詢資料字典來完成的。

點選(此處)摺疊或開啟

  1. --開啟時間顯示
  2. 08:13:12 scott@TESTDB11>set timing on;
  3. --使用*
  4. 08:14:25 scott@TESTDB11>select * from emp;

  5.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  6. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
  7.       7369 SMITH CLERK 7902 17-DEC-80 800 20
  8.       7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
  9.       7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
  10.       7566 JONES MANAGER 7839 02-APR-81 2975 20
  11.       7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
  12.       7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
  13.       7782 CLARK MANAGER 7839 09-JUN-81 2450 10
  14.       7788 SCOTT ANALYST 7566 19-APR-87 3000 20
  15.       7839 KING PRESIDENT 17-NOV-81 5000 10
  16.       7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
  17.       7876 ADAMS CLERK 7788 23-MAY-87 1100 20
  18.       7900 JAMES CLERK 7698 03-DEC-81 950 30
  19.       7902 FORD ANALYST 7566 03-DEC-81 3000 20
  20.       7934 MILLER CLERK 7782 23-JAN-82 1300 10

  21. 14 rows selected.

  22. Elapsed: 00:00:00.01

  23. --使用表名修飾所有的列名
  24. 08:14:28 scott@TESTDB11>select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno from emp;

  25.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
  26. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
  27.       7369 SMITH CLERK 7902 17-DEC-80 800 20
  28.       7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
  29.       7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
  30.       7566 JONES MANAGER 7839 02-APR-81 2975 20
  31.       7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
  32.       7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
  33.       7782 CLARK MANAGER 7839 09-JUN-81 2450 10
  34.       7788 SCOTT ANALYST 7566 19-APR-87 3000 20
  35.       7839 KING PRESIDENT 17-NOV-81 5000 10
  36.       7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
  37.       7876 ADAMS CLERK 7788 23-MAY-87 1100 20
  38.       7900 JAMES CLERK 7698 03-DEC-81 950 30
  39.       7902 FORD ANALYST 7566 03-DEC-81 3000 20
  40.       7934 MILLER CLERK 7782 23-JAN-82 1300 10

  41. 14 rows selected.

  42. Elapsed: 00:00:00.00

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17013648/viewspace-1069861/,如需轉載,請註明出處,否則將追究法律責任。

相關文章