[每日一题]OCP1z0-047 :2013-07-30表连接――内联视图当作表使用

用sys用户登录，给oe用户授权dba，以便可以用oe用户查看执行计划：

oe@OCM> conn / as sysdba
Connected.
sys@OCM> grant dba to oe;

Grant succeeded.

sys@OCM> conn oe/oe
Connected.
[html] 
oe@OCM>  set autot traceonly;  
oe@OCM> SELECT p.product_name,i.item_cnt  
  2  FROM (SELECT product_id,COUNT(*) item_cnt  
  3  FROM order_items  
  4  GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id=p.product_id;  
  
  
288 rows selected.  
  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1905807913  
  
-----------------------------------------------------------------------------------------------  
| Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT        |                     |   288 | 24768 |    97   (3)| 00:00:02 |  
|*  1 |  HASH JOIN RIGHT OUTER  |                     |   288 | 24768 |    97   (3)| 00:00:02 |  
|   2 |   VIEW                  | index$_join$_006    |   288 | 11520 |    88   (2)| 00:00:02 |  
|*  3 |    HASH JOIN            |                     |       |       |            |          |  
|*  4 |     INDEX FAST FULL SCAN| PRD_DESC_PK         |   288 | 11520 |    41   (0)| 00:00:01 |  
|   5 |     INDEX FAST FULL SCAN| PROD_NAME_IX        |   288 | 11520 |    68   (0)| 00:00:01 |  
|*  6 |   HASH JOIN RIGHT OUTER |                     |   288 | 13248 |     9  (12)| 00:00:01 |  
|   7 |    VIEW                 |                     |   185 |  4810 |     3   (0)| 00:00:01 |  
|   8 |     HASH GROUP BY       |                     |   185 |   740 |     3   (0)| 00:00:01 |  
|   9 |      INDEX FULL SCAN    | ITEM_PRODUCT_IX     |   665 |  2660 |     3   (0)| 00:00:01 |  
|  10 |    TABLE ACCESS FULL    | PRODUCT_INFORMATION |   288 |  5760 |     5   (0)| 00:00:01 |  
-----------------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   1 - access("D"."PRODUCT_ID"(+)="I"."PRODUCT_ID")  
   3 - access(ROWID=ROWID)  
   4 - filter("D"."LANGUAGE_ID"=SYS_CONTEXT('USERENV','LANG'))  
   6 - access("I"."PRODUCT_ID"(+)="I"."PRODUCT_ID")  
  
  
Statistics  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
        135  consistent gets  
          0  physical reads  
          0  redo size  
      14913  bytes sent via SQL*Net to client  
        733  bytes received via SQL*Net from client  
         21  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
        288  rows processed

可以看看执行计划，了解一下sql 执行的步骤。

答案A是正确的：查询出结果288 行，说明可以正常执行并返回结果

答案B是不正确的：我们可以把内联视图看成是一个独立的表，它是可以与其它表做外连接的。

答案C是不正确的：内联视图聚合函数的别名可以看作一个列名展显出来

答案D是不正确的：group by可以用在内联视图

正确答案是：A

这道题目太简单了，没什么好说的，写过SQL的人，直接可以PASS了。