新学到的一些 SQL hints

今年买了四本Oracle数据库方面的新书,其中Richard J. Niemiec的Oracle Database 10g Performance Tuning Tips & Techniques 比较旧,讲10gR2, 有1000多页,只能走马观花的快速浏览了.

我刚刚读完前7章, 感觉内容平平,
Chapter 1: Oracle Database 10g New Features (DBA and Developer) . . . 蜻蜓点水
Chapter 2: Basic Index Principles (Beginner Developer and Beginner DBA) . . . 跳过
Chapter 3: Disk Implementation Methodology and ASM (DBA) . . .飞速翻阅
Chapter 4: Tuning the Database with Initialization Parameters (DBA) . . .没啥新鲜的
Chapter 5: Enterprise Manager and Grid Control . . .跳过
Chapter 6: Using EXPLAIN and STORED OUTLINES . . . 过时的内容,跳过
Chapter 7: Basic Hint Syntax (Developer and DBA) . . .点到为止
希望在后面这几章里找到一些实用新鲜的知识.
8: Query Tuning: Developer and Beginner DBA
9: Table Joins and Other Advanced Tuning (Advanced DBA and Developer)
11:Using PL/SQL to Enhance Performance (Developer and DBA)
15:Performing a Quick System Review (DBA)

下面看看我新学的3个SQL Hint,

• NO_EXPAND

当我们的查询条件中有多个OR组合或者IN-list时, 阻止优化器将SQL转换成多个Union All查询.

select * from emp
where (emp_no = 1 or emp_no = 2 or emp_no = 3);


USE_CONCAT Hint的作用正好相反.

• PUSH_SUBQ

当子查询返回较少数据时,并且有利于整个SQL的优化(比如降低逻辑读,减少表连接结果集,等等),优先考虑执行子查询,返回子查询的结果集.

• QB_NAME

当我们的查询看起来凌乱不堪,丑陋恶心时,QB_Name可以帮助你精确定位和指定需要的Hint.

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
FROM employees e
WHERE last_name = 'Smith';

参考:
Oracle® Database SQL Language Reference
11g Release 1 (11.1) Comments一节


维多利亚的Swartz Bay码头,从这里搭船去温哥华,各种小汽车,货车就这样排着队,开进轮船的肚子里.

这回坐上了一艘最大渡轮.轮船驶出码头,开往温哥华.


Vancouver - Victoria (Tsawwassen-Swartz Bay) 这个航道是世界上最美丽的两个线路之一,另一个在欧洲啥地方.
只可惜是阴天傍晚,拍不出它的壮美.