oracle join驱动表,left join 为什么影响到被驱动表的计划

最新推荐文章于 2024-05-27 20:49:04 发布

CHENG XIE

最新推荐文章于 2024-05-27 20:49:04 发布

阅读量408

点赞数

文章标签： oracle join驱动表

SQL> explain plan for select l.*,o.object_name from v$locked_object l

2 left join all_objects o on l.object_id=o.object_id;

Explained.

SQL> select * from table(dbms_xplan.display)

2 ;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------

Plan hash value: 1864004074

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 179 | 159 (14)| 00:00:02 |

|* 1 | HASH JOIN OUTER | | 1 | 179 | 159 (14)| 00:00:02 |

|* 2 | HASH JOIN | | 1 | 149 | 1 (100)| 00:00:01 |

|* 3 | HASH JOIN | | 1 | 90 | 1 (100)| 00:00:01 |

|* 4 | FIXED TABLE FULL | X$KTCXB | 1 | 60 | 0 (0)| 00:00:01 |

| 5 | FIXED TABLE FULL | X$KTADM | 100 | 3000 | 0 (0)| 00:00:01 |

| 6 | FIXED TABLE FULL | X$KSUSE | 100 | 5900 | 0 (0)| 00:00:01 |

| 7 | VIEW | ALL_OBJECTS | 24539 | 718K| 157 (13)| 00:00:02 |

|* 8 | FILTER | | | | | |

|* 9 | HASH JOIN | | 51486 | 4122K| 157 (13)| 00:00:02 |

| 10 | TABLE ACCESS FULL | USER$ | 56 | 168 | 2 (0)| 00:00:01 |

|* 11 | TABLE ACCESS FULL | OBJ$ | 51486 | 3972K| 153 (12)| 00:00:02 |

|* 12 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 |

|* 13 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |

| 14 | NESTED LOOPS | | 6 | 138 | 2 (0)| 00:00:01 |

|* 15 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 10 | 2 (0)| 00:00:01 |

|* 16 | FIXED TABLE FULL | X$KZSRO | 6 | 78 | 0 (0)| 00:00:01 |

|* 17 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 18 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 19 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 20 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 21 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 22 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 23 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 24 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 25 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 26 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 27 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 28 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 29 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 30 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 31 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 32 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 33 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 34 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 35 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 36 | FIXED TABLE FULL | X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

|* 37 | FIXED TABLE FULL| X$KZSPR | 1 | 26 | 0 (0)| 00:00:01 |

| 38 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |

| 39 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("L"."KTADMTAB"="O"."OBJECT_ID"(+))

2 - access("X"."KTCXBSES"="S"."ADDR")

3 - access("X"."KTCXBXBA"="L"."KSSOBOWN")

4 - filter("X"."INST_ID"=USERENV('INSTANCE'))

8 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM

"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR

"I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND

(("O"."OWNER#"=USERENV('SCHEMAID') OR "O"."OWNER#"=1) OR "O"."TYPE#"<>11 AND "O"."TYPE#"<>14 AND

EXISTS (SELECT 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO" "X$KZSRO" WHERE

"GRANTEE#"="KZSROROL" AND "OBJ#"=:B2 AND ("PRIVILEGE#"=3 OR "PRIVILEGE#"=6 OR "PRIVILEGE#"=7 OR

"PRIVILEGE#"=9 OR "PRIVILEGE#"=10 OR "PRIVILEGE#"=11 OR "PRIVILEGE#"=12 OR "PRIVILEGE#"=16 OR

"PRIVILEGE#"=17 OR "PRIVILEGE#"=18)) OR ("O"."TYPE#"=1 OR "O"."TYPE#"=2 OR "O"."TYPE#"=3 OR

"O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=19 OR "O"."TYPE#"=20 OR "O"."TYPE#"=34 OR

"O"."TYPE#"=35) AND EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE

"INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR

(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))) OR ("O"."TYPE#"=7 OR

"O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=28 OR "O"."TYPE#"=29 OR "O"."TYPE#"=30 OR

"O"."TYPE#"=56) AND EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-144) OR

(-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=13 AND EXISTS (SELECT 0

FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181)) AND

"INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=11 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR"

"X$KZSPR" WHERE (-"KZSPRPRV")=(-141) AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=12 AND

EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=(-152) AND

"INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=14 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR"

"X$KZSPR" WHERE (-"KZSPRPRV")=(-181) AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=6 AND

EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=(-109) AND

"INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=22 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR"

"X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-190)

OR (-"KZSPRPRV")=(-191) OR (-"KZSPRPRV")=(-192))) OR "O"."TYPE#"=33 AND EXISTS (SELECT 0 FROM

SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-200) OR

(-"KZSPRPRV")=(-201) OR (-"KZSPRPRV")=(-202) OR (-"KZSPRPRV")=(-203) OR (-"KZSPRPRV")=(-204))) OR

"O"."TYPE#"=32 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE

"INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-206) OR

(-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208))) OR "O"."TYPE#"=46 AND EXISTS (SELECT 0 FROM

SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-251) OR

(-"KZSPRPRV")=(-252) OR (-"KZSPRPRV")=(-253) OR (-"KZSPRPRV")=(-254))) OR ("O"."TYPE#"=69 OR

"O"."TYPE#"=72 OR "O"."TYPE#"=74) OR ("O"."TYPE#"=67 OR "O"."TYPE#"=79) AND EXISTS (SELECT 0

FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND

"INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=23 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR"

"X$KZSPR" WHERE ((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTANCE'))

OR "O"."TYPE#"=42 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE

"INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR

(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))) OR "O"."TYPE#"=44 AND EXISTS

(SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND

"INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=48 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR"

"X$KZSPR" WHERE (-"KZSPRPRV")=12 AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=59 AND EXISTS

(SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND

((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-259) OR (-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261)))

OR "O"."TYPE#"=62 AND EXISTS (SELECT 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INS)

9 - access("O"."OWNER#"="U"."USER#")

11 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND

"O"."LINKNAME" IS NULL)

12 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6

OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)

13 - access("I"."OBJ#"=:B1)

15 - access("OBJ#"=:B1)

filter("PRIVILEGE#"=3 OR "PRIVILEGE#"=6 OR "PRIVILEGE#"=7 OR "PRIVILEGE#"=9 OR

"PRIVILEGE#"=10 OR "PRIVILEGE#"=11 OR "PRIVILEGE#"=12 OR "PRIVILEGE#"=16 OR "PRIVILEGE#"=17 OR

"PRIVILEGE#"=18)

16 - filter("GRANTEE#"="KZSROROL")

17 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR

(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))

18 - filter(((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTANCE'))

19 - filter(((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181)) AND "INST_ID"=USERENV('INSTANCE'))

20 - filter((-"KZSPRPRV")=(-141) AND "INST_ID"=USERENV('INSTANCE'))

21 - filter((-"KZSPRPRV")=(-152) AND "INST_ID"=USERENV('INSTANCE'))

22 - filter((-"KZSPRPRV")=(-181) AND "INST_ID"=USERENV('INSTANCE'))

23 - filter((-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV('INSTANCE'))

24 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-190) OR

(-"KZSPRPRV")=(-191) OR (-"KZSPRPRV")=(-192)))

25 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-200) OR (-"KZSPRPRV")=(-201) OR

(-"KZSPRPRV")=(-202) OR (-"KZSPRPRV")=(-203) OR (-"KZSPRPRV")=(-204)))

26 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-206) OR

(-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208)))

27 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-252) OR

(-"KZSPRPRV")=(-253) OR (-"KZSPRPRV")=(-254)))

28 - filter(((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND "INST_ID"=USERENV('INSTANCE'))

29 - filter(((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTANCE'))

30 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR

(-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))

31 - filter(((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND "INST_ID"=USERENV('INSTANCE'))

32 - filter((-"KZSPRPRV")=12 AND "INST_ID"=USERENV('INSTANCE'))

33 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-259) OR

(-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261)))

34 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-246) OR (-"KZSPRPRV")=(-247) OR

(-"KZSPRPRV")=(-248) OR (-"KZSPRPRV")=(-249)))

35 - filter((-"KZSPRPRV")=(-265) AND "INST_ID"=USERENV('INSTANCE'))

36 - filter(((-"KZSPRPRV")=(-268) OR (-"KZSPRPRV")=(-267)) AND "INST_ID"=USERENV('INSTANCE'))

37 - filter(((-"KZSPRPRV")=(-277) OR (-"KZSPRPRV")=(-278)) AND "INST_ID"=USERENV('INSTANCE'))

139 rows selected.

SQL> explain plan for select l.*,o.object_name

2 from v$locked_object l,all_objects o where l.object_id=o.object_id;

Explained.

SQL> select * from table(dbms_xplan.display)

2 ;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------

Plan hash value: 4263222263

-------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 202 | 3 (34)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | NESTED LOOPS | | 1 | 202 | 3 (34)| 00:00:01 |

| 3 | NESTED LOOPS | | 1 | 199 | 2 (50)| 00:00:01 |

| 4 | VIEW | V_$LOCKED_OBJECT | 1 | 120 | 1 (100)| 00:00:01 |

|* 5 | HASH JOIN | | 1 | 149 | 1 (100)| 00:00:01 |

|* 6 | HASH JOIN | | 1 | 90 | 1 (100)| 00:00:01 |

|* 7 | FIXED TABLE FULL | X$KTCXB | 1 | 60 | 0 (0)| 00:00:01 |

| 8 | FIXED TABLE FULL | X$KTADM | 100 | 3000 | 0 (0)| 00:00:01 |

| 9 | FIXED TABLE FULL | X$KSUSE | 100 | 5900 | 0 (0)| 00:00:01 |

|* 10 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 79 | 1 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| 00:00:01 |

| 12 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 |

|* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |

|* 14 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 |

|* 15 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |

| 16 | NESTED LOOPS | | 2 | 46 | 2 (0)| 00:00:01 |

|* 17 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 10 | 2 (0)| 00:00:01 |

|* 18 | FIXED TABLE FULL | X$KZSRO | 2 | 26 | 0 (0)| 00:00:01 |