ocp1z0-047: Table joins-inline view used as a table

Log in with the SYS user, authorize the DBA to the OE user so that the execution plan can be viewed with the OE user:

Oe@ocm> Conn/as SYSDBA

Connected.

Sys@ocm> Grant DBA to OE;

Grant succeeded.

Sys@ocm> Conn Oe/oe

Connected.

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;  
      
      
Selected rows. Execution Plan----------------------------------------------------------The 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 Inform  
      
 ation (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 recurs Ive calls 0 db block gets 135 consistent gets 0 physical reads 0 redo Si Ze 14913 bytes sent via sql*net to client 733 bytes-received via sql*net from client SQ L*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) all rows processed

Take a look at the execution plan and learn the steps of SQL execution.

Answer a is correct: the query results in 288 rows, indicating that the results can be performed correctly and returned

Answer b is incorrect: we can think of inline view as a separate table that can be connected to other tables.

Answer c is incorrect: the alias of an inline view aggregate function can be viewed as a column name show

Answer d is incorrect: Group by can be used in inline view

The correct answer is: A

This topic is too simple, there is nothing to say, the person who wrote the SQL, directly can pass.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/