课程实践(一)续1

案例二

点击(此处)折叠或打开

1. --创建setup_rest.sh脚本
   
2. # setup_rest.sh
   
3. # !/bin/bash
   
4. cd $HOME/solutions/Common_Mistakes
   
5. sqlplus / as sysdba <<EOF
   
6. 
   
7. SET ECHO ON
   
8. DROP USER jfv CASCADE;
   
9. CREATE USER jfv IDENTIFIED BY jfv DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
   
10. GRANT CONNECT, RESOURCE, DBA TO jfv;
    
11. CONN jfv/jfv
    
12. 
    
13. DROP TABLE orders PURGE;
    
14. CREATE TABLE orders
    
15. (
    
16.   order_id_char VARCHAR2(50) PRIMARY KEY,
    
17.   order_total_number NUMBER,
    
18.   customer_name VARCHAR2(300)
    
19. );
    
20. 
    
21. BEGIN
    
22.   FOR i IN 1..500000 LOOP
    
23.     INSERT INTO orders VALUES
    
24.       (i, 100, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
25.                 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
26.                 aaaaaaaaaaaaaaaaaa\');
    
27.   END LOOP;
    
28.   COMMIT;
    
29. END;
    
30. /
    
31. 
    
32. BEGIN
    
33.   FOR i IN 1..500000 LOOP
    
34.     INSERT INTO orders VALUES
    
35.       (500000 + i, 100, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
36.                          aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
37.                          aaaaaaaaaaaaaaaaaa\');
    
38.   END LOOP;
    
39.   COMMIT;
    
40. END;
    
41. /
    
42. 
    
43. DROP TABLE employees PURGE;
    
44. DROP TABLE job_history PURGE;
    
45. CREATE TABLE employees
    
46. (
    
47.   employee_id NUMBER PRIMARY KEY,
    
48.   NAME VARCHAR2(500)
    
49. );
    
50. BEGIN
    
51.   FOR i IN 1..500000 LOOP
    
52.     INSERT INTO employees VALUES
    
53.       (i, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
54.            aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
55.            aaaaaaaaaaaaaaaaaa\');
    
56.   END LOOP;
    
57.   COMMIT;
    
58. END;
    
59. /
    
60. 
    
61. CREATE TABLE job_history
    
62. (
    
63.   employee_id NUMBER,
    
64.   job VARCHAR2(500)
    
65. );
    
66. BEGIN
    
67.   FOR i IN 1..500000 LOOP
    
68.     INSERT INTO job_history VALUES
    
69.       (MOD(i, 1000), \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
70.                       aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
71.                       aaaaaaaaaaaaaaaaaa\');
    
72.   END LOOP;
    
73.   COMMIT;
    
74. END;
    
75. /
    
76. CREATE INDEX idx_job_history_empid ON job_history(employee_id);
    
77. 
    
78. DROP TABLE OLD PURGE;
    
79. DROP TABLE NEW PURGE;
    
80. CREATE TABLE OLD
    
81. (
    
82.   NAME VARCHAR2(10),
    
83.   other VARCHAR2(500)
    
84. );
    
85. 
    
86. CREATE TABLE NEW
    
87. (
    
88.   NAME VARCHAR2(10),
    
89.   other VARCHAR2(500)
    
90. );
    
91. 
    
92. BEGIN
    
93.   FOR i IN 1..500000 LOOP
    
94.     INSERT INTO OLD
    
95.       VALUES(i, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
96.                  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    
97.                  aaaaaaaaaaaaaaaaaa\');
    
98.   END LOOP;
    
99.   COMMIT;
    
100. END;
     
101. /
     
102. 
     
103. BEGIN
     
104.   FOR i IN 1..500000 LOOP
     
105.     INSERT INTO NEW
     
106.       VALUES(i + 500000, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
     
107.                           aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
     
108.                           aaaaaaaaaaaaaaaaaa\');
     
109.   END LOOP;
     
110.   COMMIT;
     
111. END;
     
112. /
     
113. EOF
     
114. --执行setup_rest.sh脚本建立环境, 以jfv用户进入SQL*Plus
     
115. --2.1 bad
     
116. select count(*) from job_history jh, employees e
     
117. where substr(to_char(e.employee_id), 1) = substr(to_char(jh.employee_id), 1);
     
118. 
     
119.   COUNT(*)
     
120. ----------
     
121.     499500
     
122. 
     
123. Elapsed: 00:00:08.19
     
124. 
     
125. --2.2 good
     
126. select count(*) from job_history jh, employees e where e.employee_id = jh.employee_id;
     
127. 
     
128.   COUNT(*)
     
129. ----------
     
130.     499500
     
131. 
     
132. Elapsed: 00:00:02.40

结论：在WHERE条件中不要在索引列上进行计算和使用函数

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

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