數(shù)據(jù)庫(kù)開發(fā)規(guī)范-文庫(kù)吧
2025-07-11 11:19 本頁(yè)面
【正文】 2張行數(shù)不一致的表連接 表TAB1行數(shù)：16,384行 表TAB2行數(shù)：1行SELECT COUNT(*) FROM TAB2, TAB1?！餝ELECT COUNT(*) FROM TAB1, TAB2。2）3張表連接 SELECT * FROM EMP E, LOC L, CAT C WHERE BETWEEN 1000 AND 2000 AND = AND = ?！餝ELECT * FROM LOC L, CAT C, EMP E WHERE BETWEEN 1000 AND 2000 AND = AND = 。 Where語(yǔ)句順序的效率1) 使用索引引起的where語(yǔ)句效率使用AND語(yǔ)句時(shí)行數(shù)多的放在前面SELECT * FROM emp E WHERE emp_sal 50000 AND emp_type = 39。MANAGER39。 AND 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = )?！餝ELECT * FROM emp E WHERE 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = ) AND emp_sal 50000 AND emp_type = 39。MANAGER39。使用OR語(yǔ)句時(shí)，行數(shù)多的放在后面SELECT * FROM emp E WHERE 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = ) OR (emp_sal 50000 AND emp_type = 39。MANAGER39。)。○SELECT * FROM emp E WHERE (emp_sal 50000 AND emp_type = 39。MANAGER39。) OR 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = )。2）ROWID的使用 使用ROWID的WHERE語(yǔ)句效率最高。 SELECT ROWID, ... INTO :emp_rowid, ... FROM emp WHERE = 56722 FOR UPDATE。UPDATE emp SET = ... , WHERE ROWID = :emp_rowid。 3）減少訪問(wèn)次數(shù)SELECT emp_name, sal, grade FROM emp WHERE emp_no = 0342。SELECT emp_name, sal, grade FROM emp WHERE emp_no = 0291?！餝ELECT , , , , , FROM emp A, emp B WHERE = 0342 AND = 0291。 4）Where語(yǔ)句的索引的使用 （1）SUBSTRSELECT acc_name, trans_date, amount FROM transaction WHERE SUBSTR(account_name,1,7) = 39。CAPITAL39。 ○SELECT acc_name, trans_date, amount FROM transaction WHERE account_name LIKE 39。CAPITAL%39。 （2）！= SELECT acc_name, trans_date, amount FROM transaction WHERE amount != 0?！ ?SELECT acc_name, trans_date, amount FROM transaction WHERE amount 0。 （3）TRUNCSELECT acc_name, trans_date, amount FROM transaction WHERE TRUNC(trans_date) = TRUNC(SYSDATE)。 ○SELECT acc_name, trans_date, amount FROM transaction WHERE trans_date BETW
環(huán)評(píng)公示相關(guān)推薦
鄂ICP備17016276號(hào)-1