In the above phenomena, it is difficult to understand why oracle uses full table scanning and Hash Join connection methods, we may wish to make a 10053 Event, see what Oracle did.
SQL> Set Autothasql> Alter Session Set Events '10053 Trace Name Context ForeVer, Level 1'
Session altered.
SQL> SELECT T.TOPIC_ID, T.TOPIC_TYPE, T.TOPIC_DISTILLATE, T.TOPIC_VOTE, T.TOPIC_STATUS, T.TOPIC_MOVED_ID, 2 TO_CHAR (T.Topic_Time, 'YYYY-MM-DD HH24: MI: SS') Topic_Time, 3 t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, 4 t.topic_poster fROM forum_topics t 5 where rowid in 6 (select rid from 7 (select a.rowid rid, row_number () over (order by a.topic_type DESC, A.TOPIC_LAST_POST_ID DESC) RN 8 from Forum_TOPICS A 9 WHERE A.forum_ID = 40 10 and a.topic_type <2 11 and a.topic_status <> 3 12) WHERE RN <50 and rn> = 1);
49 rows selected.
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 869 Card = 1678 BYtes = 194648) 1 0 Hash Join (SEMI) (COST = 869 Card = 1678 BYtes = 194648) 2 1 TABLE Access Full) = 444 card = 221324 BYtes = 24124316) 3 1 View of 'VW_NSO_1' (COST = 17 Card = 1678 BYtes = 11746) 4 3 View (COST = 17 Card = 1678 BYtes = 33560) 5 4 WINDOW (COST = 17 Card = 1678 BYTES = 31882) 6 5 INDEX (RANGE SCAN) of 'Ind_forum_top_for_tp_st_id' (cost = 2 card = 1678 bytes = 31882)
Statistics --------------------------------------------------- -------- 0 Recursive Calls 0 DB Block Gets 4613 Consistent Gets 0 Physical Reads 0 Redo Size 5576 Bytes Sent Via SQL * Net To Client 537 BYtes Received Via SQL * Net from Clom Clom Client 5 SQL * NET ROUNDTRIPS TO / From Clom Client 1 Sorts (Memory) 0 Sorts (Disk) 49 Rows ProcessedSQL> ALTER Session Set Events '10053 Trace Name Context Off'
Session altered.
Let's analyze tracking files. Here is the second part of the tracking file, which determines why Hash connection or NESTED connection is used.
General plans ***********************
# You can see, here is a comparison of the first connection order JOIN ORDER [1]: forum_topics [t] vw_nso_1 [vw_nso_1] now joining: VW_NSO_1 [VW_NSO_1] ****** NL JOIN #NESTED LOOP connection Outer Table: Cost: 444 CDN: 221324 RCZ: 109 Resp: 444 Inner Table: VW_NSO_1 # Internal Table is VW_NSO_1, that is, the child query Access Path: TSC RESC: 16 JOIN: RESC: 3652290 Resp: 3652290Column: Rid Col #: 1 TABLE : from $ _SUBQUERY $ _002 Alias: from $ _SUBQUERY $ _002 no statistics (using defaults) # Because it is a subquery, return is RowID, so there is no statistics NDV: 221324 Nulls: 0 Dens: 4.5183e-06 # Here NO histogram: #bkt: 0 #val: 0Column: $ nso_col_1 col #: 1 table: vw_nso_1 alias: vW_NSO_1 No Statistics (USING DEFAULTS) NDV: 221324 NULLS: 0 DENS: 4.5183E-06 No Histogram: #bkt: 0 #val: 0semi-join cardinality: 1678 = Oter (221324) * SEL (7.5816e-03) [FLAG = 12] # Cost calculation Best NL COST: 3652290 # It can be seen that such a connection order is definitely not,
Cost too large SM Join #Semi Merge Join Connection Outer Table: Resc: 444 CDN: 221324 RCZ: 109 Deg: 1 Res: 444 Inner Table: VW_NSO_1 RESC: 16 CDN: 1678 RCZ: 7 Deg: 1 Res: 16 Using Join : 1 distribution: 2 #groups: 1 SORT resource Sort statistics Sort width: 152 Area size: 1048576 Max Area size: 53686272 Degree: 1 Blocks to Sort: 3523 Row size: 130 Rows: 221324 Initial runs: 2 Merge passes: 1 IO cost / pass: 3883 Total IO sort cost: 3703 Total CPU sort cost: 0 Total Temp space used: 62530000 SORT resource Sort statistics Sort width: 152 Area size: 1048576 Max Area size: 53686272 Degree: 1 Blocks to Sort: 4 Row size : 18 Rows: 1678 Initial Runs: 1 Merge Pass: 1 IO COST / Pass: 19 Total Io Sort Cost: 12 Total CPU Sort Cost: 0 Total Temp Space Used: 0 Merge Join Cost: 4175 Resp: 41 75 # End Cost Ha Join #hash Join Connection Outer Table: Res: 444 CDN: 221324 RCZ: 109 Deg: 1 Res: 444 Inner Table: VW_NSO_1 RESC: 16 CDN: 1678 RCZ: 7 Deg: 1 Resp: 16 Using Join : 8 Distribution: 2 # Count: 1 Hash Join One PTN Resc: 408 Deg: 1 Hash_area: 256 (max = 13107) BuildFrag: 3270 ProbeFrag: 4 PPasses: 1 Hash Join Resc: 868 Resp: 868Join Result: 20: 869 CDN : 1678 RCZ: 116 # Visible in the above three connections, the cost of HASH is the lowest. Best So Far: Table #: 0 CST: 444 CDN: 221324 BYtes: 24124316Best So Far: Table #: 1 CST: 869 CDN: 1678 BYtes: 194648 **************** ******
# 下 下 下 是 序 5 5: 10 5: 53686272 Degree: 1048576 MAX Area Size: 1048576 Max Area Size: 1048576 Max Area Size: 53686272 Degree: 1 Blocks to Sort: 4 Row Size: 18 ROWS: 1 MERGE PASS: 1 IO COST / Pass: 19 Total Io Sort Cost: 12 Total CPU Sort Cost: 0 Total Temp Space Used: 0 Now Joining: Forum_topics [T *** **** NL JOIN #, first is Nested Loop connection Outer Table: Cost: 28 CDN: 1678 RCZ: 7 Resp: 28 Inner Table: Forum_topics Access Path: TSC RESC: 444 JOIN: RESC: 745060 RESP: 745060 Inner Table: Forum_TOPICS Access Path: Rowid Res: 1 Join: Resc: 1706 Resp: 1706Join Cardinal: 1678 = Outer (1678) * Inner (221324) * SEL (4.5183e-06) [Flag =
0] # Calculation Best NL COST: 1706 Res: 1706 # This cost is much smasteous than the cost of the previous connection order: Resc: 28 CDN: 1678 RCZ: 7 Deg: 1 Res: 28 Inner table: FORUM_TOPICS resc: 444 cdn: 221324 rcz: 109 deg: 1 resp: 444 using join: 1 distribution: 2 #groups: 1 SORT resource Sort statistics Sort width: 152 Area size: 1048576 Max Area size: 53686272 Degree: 1 Blocks To Sort: 4 ROW SIZE: 18 ROWS: 1678 Initial Runs: 1 Merge Pass: 1 IO COST / Pass: 19 Total Io Sort Cost: 12 Total CPU Sort Cost: 0 Total Temp Spect Statistics Sort WidtH: 0 Sort Resource Sort Statistics Sort Width: 152 Area Size: 1048576 Max Area Size: 53686272 Degree: 1 Blocks to Sort: 3523 Row Size: 130 ROWS: 221324 Initial Runs: 2 Merge Pass: 1 IO Cost / Pass: 3883 Total Io Sort Cost: 3703 Total CPU SORT COST: 0 Total Temp Space Used: 6 2530000 Merge Join Cost: 4186 Resp: 4186ha Join Outer Table: Resc: 28 CDN: 1678 RCZ: 7 Deg: 1 Res: 28 Inner Table: Forum_topics Resc: 444 CDN: 221324 RCZ: 109 Deg: 1 Resp: 444 Using Join: 8 Distribution: 2 # Count: 1 Hash Join One PTN Resc: 7 Deg: 1 Hash_area: 256 (max = 13107) BuildFrag: 4 ProbeFrag: 3270 PPasses: 1 Hash Join Resc: 479 Resp: 479 # See, HASH connection The cost is lower, final, according to the above calculations, Oracle chooses a cost CST: 869 CDN: 1678 RSC: 868 RSP: 868 CPU-RSC: 868 CPU-RSC: 868 CPU-RSC: 0 CPU-RSP: 0
# So, the final execution plan is Plancost of Plan: 869Operation ......... Object Name ..... Options ......... ID ... PID..Select Statement 0hash Join SEMI 1TABLE Access Forum_topics Full 2 1View VW_NSO_1 3 1View 4 3window Sort Pushed Rank 5 4INDEX IND_FORUM_TOP_FORANGE SCAN 6 5 In the above steps, you can see, no matter how to connect, Nested Loop's connection cost is higher than the cost of Hash, we Choose a low NL cost calculation to analyze:
1678 = Outer (1678) * Inner (221324) * SEL (4.5183e-06) [FLAG = 0]
Here the external table is a subquery, Inner (221324) * SEL (4.5183e-06) is = 1, the decision cost is the value of the external table, actually 1678, obtained by analyzing, almost equal to the child query does not add RN The number of records is limited, that is, the number of records of the entire subquery, Oracle has problems here, no restrictions on the RN page condition, and directly uses the records of the child queries as cost calculations, when it thinks 1678 lines and 221324 lines Hash is the best when the table is associated.
So, as long as the subquery record is changed, the implementation plan may change. Through the experiment, if the number of records of the subquery is less than a certain level (such as 600 or less), there is a NESTED LOOP connection method.
The following is an official explanation of Oracle:
The database is CBO based which means Oracle will choose the execution plan with less cost.In oracle 9i, HJ has lower cost comparing to NL (normally), so oracle will always choose HJ instead of NL.If you really need less logical read in The Query, you shouth force the database choise nl instead of hj.thanks and regardspaulo
