Oracle : Выкарыстанне ўкладзенага запыту супраць выкарыстання адлюстравання (Oracle : Using nested query vs using mapping)

問題描述

Oracle : Выкарыстанне ўкладзенага запыту супраць выкарыстання адлюстравання (Oracle : Using nested query vs using mapping)

I am not sure if I have named this question right but the explanation may help explaining my problem to you.

I have a main table, say PROJ_MASTER consisting millions of records. It contains columns that have codes which map to other tables for the detail of these codes, say PROJ_SUB_1 & PROJ_SUB_2.

What is the best way to write a query on the master table 1) mapping the columns

SELECT PM.SOME_COL_1,
       PM1.COL1,
       PM1.COL2
  FROM PROJ_MASTER PM, PROJ_SUB_1 PS1, PROJ_SUB_2 PS2
 WHERE PM1.COL1 = PS1.COL1
   AND PM1.COL2 = PS2.COL2

2) or using nested query

SELECT PM.SOME_COL_1,
       (SELECT DISTINCT PM1.COL1
         FROM PROJ_SUB_1 PS1
       WHERE PM.COL1 = PS1.COL1) AS COL1
       (SELECT DISTINCT PM1.COL2
         FROM PROJ_SUB_2 PS2
       WHERE PM.COL2 = PS2.COL2) AS COL2
  FROM PROJ_MASTER PM

I made some mistakes in using the alias(PM1 & PM2 instead of PM).

I am displaying the query plan 1st query)

SELECT STATEMENT, GOAL = ALL_ROWS   Cost=23827  Cardinality=1037978 Bytes=76810372
 HASH UNIQUE                Cost=23827  Cardinality=1037978 Bytes=76810372
  HASH JOIN             Cost=5638   Cardinality=1037978 Bytes=76810372
   TABLE ACCESS FULL    Object owner=USER   Object name=PROJ_MASTER 
                                    Cost=5  Cardinality=557         Bytes=17267
   HASH JOIN                Cost=5619   Cardinality=872654  Bytes=37524122
    TABLE ACCESS FULL   Object owner=USER   Object name=PROJ_SUB_1

                                    Cost=28 Cardinality=9827    Bytes=294810
    TABLE ACCESS FULL   Object owner=USER   Object name=PROJ_SUB_2

                                    Cost=5579   Cardinality=872654  Bytes=11344502

2nd query)

SELECT STATEMENT, GOAL = ALL_ROWS     Cost=9810 Cardinality=872654  Bytes=11344502
 TABLE ACCESS FULL  Object owner=USER   Object name=PROJ_SUB_1

                      Cost=5    Cardinality=1           Bytes=31
 TABLE ACCESS FULL  Object owner=USER   Object name=PROJ_SUB_2

                      Cost=28   Cardinality=1           Bytes=30
 HASH UNIQUE                  Cost=9810 Cardinality=872654  Bytes=11344502
  TABLE ACCESS FULL Object owner=USER   Object name=PROJ_MASTER 
                      Cost=5579 Cardinality=872654  Bytes=11344502

‑‑‑‑‑

參考解法

方法 1:

The first way is the best for many reasons. It is simpler to read and understand. According to functionality and performance the first is better too. In huge amount of data you can fill the difference. It is my experience.

方法 2:

Databases like Oracle were born to join. Your first query is the 'normal' way to do what you are trying to do, and will be fast.

Having said that, a few points:

Most people prefer ANSI join syntax, so this will be easier to read:

SELECT PM.SOME_COL_1,
       COL1,
       COL2
  FROM PROJ_MASTER PM 
       JOIN PROJ_SUB_1 USING (COL1) 
       JOIN PROJ_SUB_2 USING (COL2)

Of course you'll probably be including some columns(s) from PROJ_SUB_1 and PROJ_SUB_2.

The second query likewise should probably be selecting something from the SUB tables or it would be pretty pointless, but I'm assuming this is a typo. In this case, the functional difference is that:

• the query will fail if there are ever multiple matching rows (but from the question it seem natural to assume a FK relationship between the tables)
• this query is generally more verbose, slower and less readable than the first, but it has one particular property: you can see at a glance that exactly one row will be returned for each row in PROJ_SUB_2 if the query succeeds, without knowing looking up the keys and relationships. 

Finally, the DISTINCT is redundant if there really is a FK/PK relationship enforced.

(by Rohan、Doctor、user533832)

參考文件

1. Oracle : Using nested query vs using mapping (CC BY‑SA 3.0/4.0)