Всем привет!
Кто-то может подсказать, как так получается, что в рекурсивных запросах Oracle 12.1.0.2.0 идет ссылка на колонку, которой нет?
Пример:
1.
2.
3.
4.
5.
6.
7.
SELECT /* DS_SVC */
/*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", INDEX_SKIP_SCAN, "O#11", "I_OBJ2", ROWS=2.289375) OPT_ESTIMATE(@"innerQuery", TABLE, "O#11", ROWS=2.289375) OPT_ESTIMATE(@"innerQuery", INDEX_SKIP_SCAN, "O#11", "I_OBJ5", ROWS=2.289375) */
SUM (C1)
FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O#11") */
1 AS C1
FROM "SYS"."OBJ$" SAMPLE BLOCK (21.84, 8) SEED (1) "O#11", "SYS"."USER$" "U#16"
WHERE ("U"."NAME" = :B1) AND ("O"."NAME" = :B2) AND ("O"."OWNER#" = "U"."USER#") AND (BITAND ("O"."FLAGS", 128) = 0)) innerQuery
Здесь вообще таблицы "O" нет.
1.
2.
3.
4.
5.
6.
7.
SELECT /* DS_SVC */
/*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", TABLE, "O#5", ROWS=2.289375) OPT_ESTIMATE(@"innerQuery", INDEX_SKIP_SCAN, "O#5", "I_OBJ2", ROWS=2.289375) OPT_ESTIMATE(@"innerQuery", INDEX_SKIP_SCAN, "O#5", "I_OBJ5", ROWS=2.289375) */
SUM (C1)
FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O#5") */
1 AS C1
FROM "SYS"."OBJ$" SAMPLE BLOCK (21.84, 8) SEED (1) "O#5", "SYS"."USER$" "U#10"
WHERE ("U"."NAME" = :B1) AND ("O"."NAME" = :B2) AND ("O"."OWNER#" = "U"."USER#") AND (BITAND ("O"."FLAGS", 128) = 0)) innerQuery
То же самое.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
SELECT /* DS_SVC */
/*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", TABLE, "O#4", ROWS=9.1575) */
SUM (C1)
FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O#4") */
1 AS C1
FROM ( (SELECT DECODE (BITAND ("T"."PROPERTY", 8192), 8192, 'NESTED TABLE', 'TABLE') "DECODE(BITAND(T.PROPERTY,8192)",
2 "2",
5 "5",
"T"."OBJ#" "OBJ#",
"T"."FILE#" "FILE#",
"T"."BLOCK#" "BLOCK#",
"T"."TS#" "TS#"
FROM "SYS"."TAB$" "T"
WHERE BITAND ("T"."PROPERTY", 1024) = 0
AND ( DECODE (BITAND ("T"."PROPERTY", 8192), 8192, 'NESTED TABLE', 'TABLE') = 'LOBINDEX'
OR DECODE (BITAND ("T"."PROPERTY", 8192), 8192, 'NESTED TABLE', 'TABLE') = 'LOBSEGMENT'
OR DECODE (BITAND ("T"."PROPERTY", 8192), 8192, 'NESTED TABLE', 'TABLE') = 'LOB PARTITION'))
UNION ALL
(SELECT 'TABLE PARTITION' "'TABLEPARTITION'",
19 "19",
5 "5",
"TP"."OBJ#" "OBJ#",
"TP"."FILE#" "FILE#",
"TP"."BLOCK#" "BLOCK#",
"TP"."TS#" "TS#"
FROM "SYS"."TABPART$" "TP"
WHERE NULL IS NOT NULL)
UNION ALL
(SELECT 'CLUSTER' "'CLUSTER'",
3 "3",
5 "5",
"C"."OBJ#" "OBJ#",
"C"."FILE#" "FILE#",
"C"."BLOCK#" "BLOCK#",
"C"."TS#" "TS#"
FROM "SYS"."CLU$" "C"
WHERE NULL IS NOT NULL)
UNION ALL
(SELECT DECODE ("I"."TYPE#", 8, 'LOBINDEX', 'INDEX') "DECODE(I.TYPE#,8,'LOBINDEX','I",
1 "1",
6 "6",
"I"."OBJ#" "OBJ#",
"I"."FILE#" "FILE#",
"I"."BLOCK#" "BLOCK#",
"I"."TS#" "TS#"
FROM "SYS"."IND$" "I"
WHERE ( "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#" = 8
OR "I"."TYPE#" = 9)
AND ( DECODE ("I"."TYPE#", 8, 'LOBINDEX', 'INDEX') = 'LOBINDEX'
OR DECODE ("I"."TYPE#", 8, 'LOBINDEX', 'INDEX') = 'LOBSEGMENT'
OR DECODE ("I"."TYPE#", 8, 'LOBINDEX', 'INDEX') = 'LOB PARTITION'))
UNION ALL
(SELECT 'INDEX PARTITION' "'INDEXPARTITION'",
20 "20",
6 "6",
"IP"."OBJ#" "OBJ#",
"IP"."FILE#" "FILE#",
"IP"."BLOCK#" "BLOCK#",
"IP"."TS#" "TS#"
FROM "SYS"."INDPART$" "IP"
WHERE NULL IS NOT NULL)
UNION ALL
(SELECT 'LOBSEGMENT' "'LOBSEGMENT'",
21 "21",
8 "8",
"L"."LOBJ#" "LOBJ#",
"L"."FILE#" "FILE#",
"L"."BLOCK#" "BLOCK#",
"L"."TS#" "TS#"
FROM "SYS"."LOB$" "L"
WHERE BITAND ("L"."PROPERTY", 64) = 0 OR BITAND ("L"."PROPERTY", 128) = 128)
UNION ALL
(SELECT 'TABLE SUBPARTITION' "'TABLESUBPARTITION'",
34 "34",
5 "5",
"TSP"."OBJ#" "OBJ#",
"TSP"."FILE#" "FILE#",
"TSP"."BLOCK#" "BLOCK#",
"TSP"."TS#" "TS#"
FROM "SYS"."TABSUBPART$" "TSP"
WHERE NULL IS NOT NULL)
UNION ALL
(SELECT 'INDEX SUBPARTITION' "'INDEXSUBPARTITION'",
35 "35",
6 "6",
"ISP"."OBJ#" "OBJ#",
"ISP"."FILE#" "FILE#",
"ISP"."BLOCK#" "BLOCK#",
"ISP"."TS#" "TS#"
FROM "SYS"."INDSUBPART$" "ISP"
WHERE NULL IS NOT NULL)
UNION ALL
(SELECT DECODE ("LF"."FRAGTYPE$", 'P', 'LOB PARTITION', 'LOB SUBPARTITION') "DECODE(LF.FRAGTYPE$,'P','LOBPA",
DECODE ("LF"."FRAGTYPE$", 'P', 40, 41) "DECODE(LF.FRAGTYPE$,'P',40,41)",
8 "8",
"LF"."FRAGOBJ#" "FRAGOBJ#",
"LF"."FILE#" "FILE#",
"LF"."BLOCK#" "BLOCK#",
"LF"."TS#" "TS#"
FROM "SYS"."LOBFRAG$" "LF"
WHERE DECODE ("LF"."FRAGTYPE$", 'P', 'LOB PARTITION', 'LOB SUBPARTITION') = 'LOBINDEX'
OR DECODE ("LF"."FRAGTYPE$", 'P', 'LOB PARTITION', 'LOB SUBPARTITION') = 'LOBSEGMENT'
OR DECODE ("LF"."FRAGTYPE$", 'P', 'LOB PARTITION', 'LOB SUBPARTITION') = 'LOB PARTITION')) "SO#2",
"SYS"."OBJ$" SAMPLE BLOCK (21.84, 8) SEED (1) "O#4"
WHERE ("O#4"."NAME" = 'SYS_IL0000297239C00007$$' OR "O#4"."NAME" = 'SYS_LOB0000297239C00007$$')
AND ("O#4"."OBJ#" = "SO#2"."OBJECT_ID")
AND ("O#4"."TYPE#" = "SO#2"."OBJECT_TYPE_ID")) innerQuery
А здесь проблема с колонками SO#2.object_id и SO#2.object_type_id |
