为什么SELECT查询中应避免使用*？

当想在SELECT语句中列出所有的列时，使用*非常方便。但这是一种低效的方法。

Oracle在解析的过程中会将*依次转换为具体的列名，这是通过查询数据字典来完成的。

点击(此处)折叠或打开

1. --打开时间显示
   
2. 08:13:12 scott@TESTDB11>set timing on;
   
3. --使用*
   
4. 08:14:25 scott@TESTDB11>select * from emp;
   
5. 
   
6.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
   
7. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
   
8.       7369 SMITH CLERK 7902 17-DEC-80 800 20
   
9.       7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
   
10.       7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    
11.       7566 JONES MANAGER 7839 02-APR-81 2975 20
    
12.       7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    
13.       7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    
14.       7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    
15.       7788 SCOTT ANALYST 7566 19-APR-87 3000 20
    
16.       7839 KING PRESIDENT 17-NOV-81 5000 10
    
17.       7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    
18.       7876 ADAMS CLERK 7788 23-MAY-87 1100 20
    
19.       7900 JAMES CLERK 7698 03-DEC-81 950 30
    
20.       7902 FORD ANALYST 7566 03-DEC-81 3000 20
    
21.       7934 MILLER CLERK 7782 23-JAN-82 1300 10
    
22. 
    
23. 14 rows selected.
    
24. 
    
25. Elapsed: 00:00:00.01
    
26. 
    
27. --使用表名修饰所有的列名
    
28. 08:14:28 scott@TESTDB11>select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno from emp;
    
29. 
    
30.      EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    
31. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    
32.       7369 SMITH CLERK 7902 17-DEC-80 800 20
    
33.       7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    
34.       7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    
35.       7566 JONES MANAGER 7839 02-APR-81 2975 20
    
36.       7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    
37.       7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
    
38.       7782 CLARK MANAGER 7839 09-JUN-81 2450 10
    
39.       7788 SCOTT ANALYST 7566 19-APR-87 3000 20
    
40.       7839 KING PRESIDENT 17-NOV-81 5000 10
    
41.       7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    
42.       7876 ADAMS CLERK 7788 23-MAY-87 1100 20
    
43.       7900 JAMES CLERK 7698 03-DEC-81 950 30
    
44.       7902 FORD ANALYST 7566 03-DEC-81 3000 20
    
45.       7934 MILLER CLERK 7782 23-JAN-82 1300 10
    
46. 
    
47. 14 rows selected.
    
48. 
    
49. Elapsed: 00:00:00.00

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1069861/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1069861/