案例二

点击(此处)折叠或打开

--创建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

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

ITPub博客

课程实践(一)续1

最新文章