|
Меняет ли CBO сам запрос или только план его выполнения?
#32169873
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Санкт-Петербург
Сообщения: 8 239
|
|
Столкнулся с вещью, которая поневоле вызывает вопрос про сабж. Oracle 9.2.0.1 Standart Edition, optimizer_mode=FIRST_ROWS. Именно Standart, поскольку на Enterprise подобного повторить не удалось.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create table themes (
code number( 9 )
, theme varchar2( 2000 )
, weight number
, constraint pk_themes primary key (code, theme)
);
/
insert into themes(code, theme, weight)
select rownum, 'Val1', trunc(dbms_random.value() * 100 ) from all_objects where rownum <= 20
union all
select rownum, 'Val2', trunc(dbms_random.value() * 100 ) from all_objects where rownum <= 40
union all
select rownum, 'Val3', trunc(dbms_random.value() * 100 ) from all_objects where rownum <= 60
/
commit;
Теперь выполняем такие запросы:
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.
dan@oraspb>; select code, sum(weight1) weight1, sum(weight2) weight2, sum(weight3) weight3
2 from (select code, weight weight1, 0 weight2, 0 weight3 from themes where theme = 'Val1'
3 union all
4 select code, 0 weight1, weight weight2, 0 weight3 from themes where theme = 'Val2'
5 union all
6 select code, 0 weight1, 0 weight2, weight weight3 from themes where theme = 'Val3'
7 )
8 where 1 = 1
9 and code in (
10 select code
11 from themes
12 having count(code) = 3
13 group by code
14 )
15 group by code
16 order by code
17 /
select code, 0 weight1, 0 weight2, weight weight3 from themes where theme = 'Val3'
*
ERROR at line 6 :
ORA- 00928 : missing SELECT keyword
dan@oraspb>;
dan@oraspb>; select --+ ALL_ROWS
2 code, sum(weight1) weight1, sum(weight2) weight2, sum(weight3) weight3
3 from (select code, weight weight1, 0 weight2, 0 weight3 from themes where theme = 'Val1'
4 union all
5 select code, 0 weight1, weight weight2, 0 weight3 from themes where theme = 'Val2'
6 union all
7 select code, 0 weight1, 0 weight2, weight weight3 from themes where theme = 'Val3'
8 )
9 where 1 = 1
10 and code in (
11 select code
12 from themes
13 having count(code) = 3
14 group by code
15 )
16 group by code
17 order by code
18 /
select code, 0 weight1, 0 weight2, weight weight3 from themes where theme = 'Val3'
*
ERROR at line 7 :
ORA- 00907 : missing right parenthesis
Заметил: ошибки сыпятся из-за наличия условия WHERE, причем когда в нем используется та же самая таблица, что и в подзапросе.
Но при указании хинта CHOOSE или RULE запрос отрабатывает без проблем.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
dan@oraspb>; select --+ CHOOSE
2 code, sum(weight1) weight1, sum(weight2) weight2, sum(weight3) weight3
3 from (select code, weight weight1, 0 weight2, 0 weight3 from themes where theme = 'Val1'
4 union all
5 select code, 0 weight1, weight weight2, 0 weight3 from themes where theme = 'Val2'
6 union all
7 select code, 0 weight1, 0 weight2, weight weight3 from themes where theme = 'Val3'
8 )
9 where 1 = 1
10 and code in (
11 select code
12 from themes
13 having count(code) = 3
14 group by code
15 )
16 group by code
17 order by code
18 /
20 rows selected.
А почему так? |
|
|
