案例二
结论:在WHERE条件中不要在索引列上进行计算和使用函数
点击(此处)折叠或打开
- --创建setup_rest.sh脚本
- # setup_rest.sh
- # !/bin/bash
- cd $HOME/solutions/Common_Mistakes
- sqlplus / as sysdba <<EOF
-
- SET ECHO ON
- DROP USER jfv CASCADE;
- CREATE USER jfv IDENTIFIED BY jfv DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
- GRANT CONNECT, RESOURCE, DBA TO jfv;
- CONN jfv/jfv
-
- DROP TABLE orders PURGE;
- CREATE TABLE orders
- (
- order_id_char VARCHAR2(50) PRIMARY KEY,
- order_total_number NUMBER,
- customer_name VARCHAR2(300)
- );
-
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO orders VALUES
- (i, 100, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
-
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO orders VALUES
- (500000 + i, 100, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
-
- DROP TABLE employees PURGE;
- DROP TABLE job_history PURGE;
- CREATE TABLE employees
- (
- employee_id NUMBER PRIMARY KEY,
- NAME VARCHAR2(500)
- );
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO employees VALUES
- (i, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
-
- CREATE TABLE job_history
- (
- employee_id NUMBER,
- job VARCHAR2(500)
- );
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO job_history VALUES
- (MOD(i, 1000), \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
- CREATE INDEX idx_job_history_empid ON job_history(employee_id);
-
- DROP TABLE OLD PURGE;
- DROP TABLE NEW PURGE;
- CREATE TABLE OLD
- (
- NAME VARCHAR2(10),
- other VARCHAR2(500)
- );
-
- CREATE TABLE NEW
- (
- NAME VARCHAR2(10),
- other VARCHAR2(500)
- );
-
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO OLD
- VALUES(i, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
-
- BEGIN
- FOR i IN 1..500000 LOOP
- INSERT INTO NEW
- VALUES(i + 500000, \'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
- aaaaaaaaaaaaaaaaaa\');
- END LOOP;
- COMMIT;
- END;
- /
- EOF
- --执行setup_rest.sh脚本建立环境, 以jfv用户进入SQL*Plus
- --2.1 bad
- select count(*) from job_history jh, employees e
- where substr(to_char(e.employee_id), 1) = substr(to_char(jh.employee_id), 1);
-
- COUNT(*)
- ----------
- 499500
-
- Elapsed: 00:00:08.19
-
- --2.2 good
- select count(*) from job_history jh, employees e where e.employee_id = jh.employee_id;
-
- COUNT(*)
- ----------
- 499500
-
- Elapsed: 00:00:02.40
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072399/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1072399/