新一代智能sql語句優(yōu)化技術(編輯修改稿)
2024-11-15 19:55 本頁面
【文章內容簡介】 ET JTC ON SET TABLE COUNT Forces Where the experts go for performance 使用 FORCEPLAN 強制選項強制使用特殊的表操縱路徑 (driving path)。 原來的方法 /*禁止使用 B的索引來改變表操縱路徑 (B drive A)*/ SELECT * FROM A, B WHERE = ISNULL(,) 用 FORCEPLAN重寫后 SET FORCEPLAN ON GO SELECT * FROM B, A WHERE = GO Joins — Change Table Driving Path Where the experts go for performance 強制索引 (Force Index) SELECT * FROM EMPLOYEE WHERE EMP_ID 73712 Force Index 備注： 僅在事先知道使用使用索引 x 將會較快的情況下采用 (INDEX EMP_ID_INX) QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. FROM TABLE EMPLOYEE Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. Total estimated I/O cost for statement 1 (at line 1): 20600. QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 he type of query is SELECT. FROM TABLE EMPLOYEE Nested iteration. Index : EMP_ID_INX Forward scan. Positioning by key. Keys are: EMP_ID ASC Using I/O Size 2 Kbytes for index leaf pages. With LRU Buffer Replacement Strategy for index leaf pages. Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. Total estimated I/O cost for statement 1 (at line 1): 23382. Where the experts go for performance 變量使用上的普遍問題 SELECT * FROM employee WHERE (emp_id @range_lower OR @range_lower IS NULL) AND (emp_id @range_upper OR @range_upper IS NULL) 使用配合輸入范圍邊界的索引范圍搜索 (index range search) SELECT * FROM employee WHERE emp_id ISNULL(@range_lower , min of emp_id) AND emp_id ISNULL(@range_upper, max of emp_id) Where the experts go for performance SELECT * FROM A WHERE EXISTS (SELECT ‘x’ FROM B WHERE = ) SELECT * FROM A a1 WHERE EXISTS IN (SELECT ‘x’ FROM A a2, B WHERE = AND = ) 復雜的 SQL轉化 語義上等同于 : Where the experts go for performance 這條 SQL語句有多少個不同的等價寫法 ? SELECT * FROM DEPARTMENT WHERE DPT_ID IN (SELECT EMP_DEPT FROM EMPLOYEE WHERE EMP_ID 50000) 更多的信息： DEPARTMENT EMPLOYEE 記錄數(shù) = 410 記錄數(shù) = 18370 索引 DPT_ID 索引 EMP_ID Where the experts go for performance 最新的 SQL優(yōu)化技術 基于人工智能 (.)的 SQL 轉換技術 使用人工智能 (.)來重寫 SQL語句 Where the experts go for performance ESR 人工智能引擎 優(yōu)化的 SQL 1 優(yōu)
教學課件相關推薦
鄂ICP備17016276號-1