1 2 | DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); |
1 2 | SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) |
1 2 | (高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') (低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB') |
1 2 3 4 5 6 7 8 9 | SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC; |
1 | ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME> |
1 2 3 | (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO |
1 2 3 | (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); |
1 2 | 低效: SELECT … FROM DEPT WHERE SAL * 12 > 25000; |
1 2 | 高效: SELECT … FROM DEPT WHERE SAL > 25000/12; |
1 2 | 高效: SELECT * FROM EMP WHERE DEPTNO >=4 |
1 2 | 低效: SELECT * FROM EMP WHERE DEPTNO >3 |
1 2 3 4 5 6 7 8 | 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” |
1 2 3 4 | 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” |
1 2 | 低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 |
1 2 | 高效 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30); |
1 2 | 低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; |
1 2 | 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; |
1 2 3 4 5 6 7 8 | 低效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' |
1 2 3 4 5 6 7 8 | 高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' |
1 2 | 低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE |
1 2 | 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0 |
1 | SELECT … FROM EMP WHERE EMPNO = ‘123' |
1 | SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') |
1 | SELECT … FROM EMP WHERE EMP_TYPE = 123 |
1 | SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 |
1 2 3 4 5 6 | 低效: SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' |
1 2 3 4 5 6 | 高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB |
欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://p.delit.cn/) | Powered by Discuz! X3.2 |