记录一次sql优化记录（in变为exists和union）

插入300万条数据

declare
i integer; --定义变量
begin
i := 2000001;
loop
/* 插入数据 */
insert into T_TEST_LARGE_DATA (ID, USERNAME, PASSWD, DEPARTMENTID, DEPARTMENTNAME, TITLE, HIREDATE)
values
(i,
'username',
'password',
3,
'department-3',
'视觉设计',
sysdate);
/* 参数递增 */
i := i + 1;
/* 停止条件 */
exit when i > 3000000;
end loop;
commit;
end;

数据库扩容

 SELECT T.TABLESPACE_NAME,
           D.FILE_NAME,
           D.AUTOEXTENSIBLE,
           D.BYTES,
           D.MAXBYTES,
           D.STATUS
      FROM DBA_TABLESPACES T, DBA_DATA_FILES D
     WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
     ORDER BY TABLESPACE_NAME, FILE_NAME;

ALTER TABLESPACE SYSTEM ADD DATAFILE
'E:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM_2.DBF' SIZE 1024M AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;

创建索引

 create INDEX index_deptid on T_TEST_LARGE_DATA(DEP