課程實踐(一)續1

LuiseDalian發表於2014-01-18
案例二

點選(此處)摺疊或開啟

  1. --建立setup_rest.sh指令碼
  2. # setup_rest.sh
  3. # !/bin/bash
  4. cd $HOME/solutions/Common_Mistakes
  5. sqlplus / as sysdba <<EOF

  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

  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. );

  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. /

  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. /

  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. /

  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);

  78. DROP TABLE OLD PURGE;
  79. DROP TABLE NEW PURGE;
  80. CREATE TABLE OLD
  81. (
  82.   NAME VARCHAR2(10),
  83.   other VARCHAR2(500)
  84. );

  86. CREATE TABLE NEW
  87. (
  88.   NAME VARCHAR2(10),
  89.   other VARCHAR2(500)
  90. );

  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. /

  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);

  119.   COUNT(*)
  120. ----------
  121.     499500

  123. Elapsed: 00:00:08.19

  125. --2.2 good
  126. select count(*) from job_history jh, employees e where e.employee_id = jh.employee_id;

  128.   COUNT(*)
  129. ----------
  130.     499500

  132. Elapsed: 00:00:02.40
結論：在WHERE條件中不要在索引列上進行計算和使用函式

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

相關文章