木匠的微型博客 Charlie Twitter

    follow me on Twitter

    Tuesday, February 10, 2009

    新学到的一些 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) 这个航道是世界上最美丽的两个线路之一,另一个在欧洲啥地方.
    只可惜是阴天傍晚,拍不出它的壮美.

    No comments: