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

Source: Internet
Author: User
Tags dba hash join joins sorts

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/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.