Не могу уйти от фуллскана таблицы / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не могу уйти от фуллскана таблицы

4 сообщений из 4, страница 1 из 1

Не могу уйти от фуллскана таблицы

#39334031

Pavel_PV

Гость

Всем доброго времени суток.
Коллеги, не могу уйти от фуллскана, стало интересно и решил разобраться. Но всё тщетно, только уж совсем недокументированный хинт precompute_subquery помогает. Но его использование не есть гуд, даже оракл не решается на этот подвиг ).
Итак, вот воссозданная ситуация:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

create table test1 as select level as a from dual connect by level<1e5;
create table test2 as select level as a from dual connect by level<10;
alter table test1 add B number;
alter table test1 add C varchar2(10);
alter table test1 add D varchar2(10);
update test1 set b=1 where b is null and rownum<10000;
update test1 set b=2 where b is null and rownum<10000;
update test1 set b=3 where b is null and rownum<10000;
update test1 set b=4 where b is null and rownum<10000;
update test1 set b=5 where b is null and rownum<10000;
update test1 set b=6 where b is null and rownum<10;
update test1 set b=7 where b is null and rownum<10;
commit;
create BITMAP index B_BIDX on test1(b);

На этом этапе понятно, что в таблице test1 содержится ровно по 9 записей с b=6 и b=7.

Теперь делаем так:

Код: plsql

1.
2.
3.
4.
5.

merge into (select * from test1 where b in (select * from test2 where a in (6,7))) T
using(select 'A' as "A" from dual) TMP
ON (T.D=TMP.A) 
when MATCHED then update set
    T.C = TMP.A;

План:

Код: plsql

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.

1	Plan hash value: 665526705
2	 
3	--------------------------------------------------------------------------------
4	| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
5	--------------------------------------------------------------------------------
6	|   0 | MERGE STATEMENT        |       |    34 |  1904 |    78   (3)| 00:00:01 |
7	|   1 |  MERGE                 | TEST1 |       |       |            |          |
8	|   2 |   VIEW                 |       |       |       |            |          |
9	|*  3 |    HASH JOIN           |       |    34 |  2210 |    78   (3)| 00:00:01 |
10	|   4 |     NESTED LOOPS       |       |     2 |    26 |     5   (0)| 00:00:01 |
11	|   5 |      FAST DUAL         |       |     1 |       |     2   (0)| 00:00:01 |
12	|   6 |      SORT UNIQUE       |       |     2 |    26 |     3   (0)| 00:00:01 |
13	|*  7 |       TABLE ACCESS FULL| TEST2 |     2 |    26 |     3   (0)| 00:00:01 |
14	|*  8 |     TABLE ACCESS FULL  | TEST1 |   169 |  8788 |    73   (3)| 00:00:01 |
15	--------------------------------------------------------------------------------
16	 
17	Predicate Information (identified by operation id):
18	---------------------------------------------------
19	 
20	   3 - access("B"="TEST2"."A")
21	   7 - filter("A"=6 OR "A"=7)
22	   8 - filter("TEST1"."D"='A' AND ("B"=6 OR "B"=7))
23	 
24	Note
25	-----
26	   - dynamic sampling used for this statement (level=2)

В плане видим фуллскан таблицы test1, но непонятно почему. У нас всего 18 строк для обноления, вполне можно пробежаться по битмапу и далее по rowid. Хочу чтобы было как-то так:

Код: plsql

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.

1	Plan hash value: 1268000155
2	 
3	------------------------------------------------------------------------------------------
4	| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
5	------------------------------------------------------------------------------------------
6	|   0 | MERGE STATEMENT                 |        |     1 |    43 |    61   (0)| 00:00:01 |
7	|   1 |  MERGE                          | TEST1  |       |       |            |          |
8	|   2 |   VIEW                          |        |       |       |            |          |
9	|   3 |    NESTED LOOPS                 |        |     1 |    52 |    58   (0)| 00:00:01 |
10	|   4 |     FAST DUAL                   |        |     1 |       |     2   (0)| 00:00:01 |
11	|*  5 |     TABLE ACCESS BY INDEX ROWID | TEST1  |     1 |    52 |    58   (0)| 00:00:01 |
12	|   6 |      BITMAP CONVERSION TO ROWIDS|        |       |       |            |          |
13	|*  7 |       BITMAP INDEX SINGLE VALUE | B_BIDX |       |       |            |          |
14	|*  8 |        TABLE ACCESS FULL        | TEST2  |     2 |    26 |     3   (0)| 00:00:01 |
15	------------------------------------------------------------------------------------------
16	 
17	Predicate Information (identified by operation id):
18	---------------------------------------------------
19	 
20	   5 - filter("TEST1"."D"='A')
21	   7 - access("B"= (SELECT "TEST2"."A" FROM "TEST2" "TEST2" WHERE "A"=6 OR "A"=7))
22	   8 - filter("A"=6 OR "A"=7)
23	 
24	Note
25	-----
26	   - dynamic sampling used for this statement (level=2)

такой план можно получить если в запросе поменять "where b in (select * from test2 where a in (6,7))" на "where b= (select * from test2 where a in (6,7))" или воспользоваться хинтом о котором я говорил выше. Но хочется найти или другой хинт или сделать какой-то другой финт, чтобы оракл понял как нужно действовать.

P.S> У меня там конечно всё сложнее и начитываются таблички немного иначе, в частности сначала у меня читается test1 и лишь затем test2, но в этом плане суть ясна. Думаю если удастся поставить запрос на нужный план в этом случае, то и в моём я так же смогу это сделать.

...

Рейтинг:

0 / 0

26.10.2016, 06:19

| Ответить | Цитировать | Написать

Не могу уйти от фуллскана таблицы

#39334082

landy

Участник

Сообщения: 1 531

Рейтинг: 0 / 0

Если Oracle 11, то как вариант через baseline привязать нужный план к вашему запросу

...

Рейтинг:

0 / 0

26.10.2016, 09:02

| Ответить | Цитировать | Написать

Не могу уйти от фуллскана таблицы

#39334086

SeaGate

Участник

Откуда: Новосибирск

Сообщения: 1 759

Рейтинг: 6 / 0

Pavel_PV,

Версия?
В 12.1.0.2.160419 все в порядке, outline data прилагается.

Код: plsql

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.

SQL> explain plan for
  2  merge into (select * from test1 where b in (select * from test2 where a in (6,7))) T
  3  using(select 'A' as "A" from dual) TMP
  4  ON (T.D=TMP.A)
  5  when MATCHED then update set
  6      T.C = TMP.A;

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display( format=> 'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 4120546325

--------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         |        |    10 |   110 |    45   (0)| 00:00:01 |
|   1 |  MERGE                                  | TEST1  |       |       |            |          |
|   2 |   VIEW                                  |        |       |       |            |          |
|*  3 |    HASH JOIN                            |        |    10 |    80 |    45   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |        |     2 |     6 |     5   (0)| 00:00:01 |
|   5 |      FAST DUAL                          |        |     1 |       |     2   (0)| 00:00:01 |
|   6 |      SORT UNIQUE                        |        |     2 |     6 |     3   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL                 | TEST2  |     2 |     6 |     3   (0)| 00:00:01 |
|   8 |     INLIST ITERATOR                     |        |       |       |            |          |
|*  9 |      TABLE ACCESS BY INDEX ROWID BATCHED| TEST1  |    10 |    50 |    40   (0)| 00:00:01 |
|  10 |       BITMAP CONVERSION TO ROWIDS       |        |       |       |            |          |
|* 11 |        BITMAP INDEX SINGLE VALUE        | B_BIDX |       |       |            |          |
--------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      SEMI_TO_INNER(@"SEL$AC7961AF" "TEST2"@"SEL$5")
      USE_HASH(@"SEL$AC7961AF" "TEST1"@"SEL$4")
      USE_NL(@"SEL$AC7961AF" "TEST2"@"SEL$5")
      LEADING(@"SEL$AC7961AF" "DUAL"@"SEL$6" "TEST2"@"SEL$5" "TEST1"@"SEL$4")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$AC7961AF" "TEST1"@"SEL$4")
      INDEX_COMBINE(@"SEL$AC7961AF" "TEST1"@"SEL$4" ("TEST1"."B"))
      FULL(@"SEL$AC7961AF" "TEST2"@"SEL$5")
      SEMI_TO_INNER(@"SEL$2EB1D98C" "TEST2"@"SEL$2")
      USE_MERGE_CARTESIAN(@"SEL$2EB1D98C" "TEST1"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SEL$2EB1D98C" "from$_subquery$_011"@"MRG$1")
      USE_MERGE_CARTESIAN(@"SEL$2EB1D98C" "TMP"@"MRG$1")
      LEADING(@"SEL$2EB1D98C" "TEST2"@"SEL$2" "TMP"@"MRG$1" "from$_subquery$_011"@"MRG$1"
              "TEST1"@"SEL$1")
      FULL(@"SEL$2EB1D98C" "TEST1"@"SEL$1")
      NO_ACCESS(@"SEL$2EB1D98C" "from$_subquery$_011"@"MRG$1")
      NO_ACCESS(@"SEL$2EB1D98C" "TMP"@"MRG$1")
      FULL(@"SEL$2EB1D98C" "TEST2"@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"MRG$1")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$76AA3327")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$6")
      MERGE(@"SEL$4")
      OUTLINE(@"SEL$0BBAD545")
      UNNEST(@"SEL$2")
      OUTLINE_LEAF(@"SEL$2EB1D98C")
      OUTLINE_LEAF(@"SEL$7")
      UNNEST(@"SEL$5")
      OUTLINE_LEAF(@"SEL$AC7961AF")
      NO_PARALLEL
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"="TEST2"."A")
   7 - filter("A"=6 OR "A"=7)
   9 - filter("TEST1"."D"='A')
  11 - access("B"=6 OR "B"=7)

...

Рейтинг:

0 / 0

26.10.2016, 09:09

| Ответить | Цитировать | Написать

Не могу уйти от фуллскана таблицы

#39335164

ITest

Гость

при увеличении test1

Код: plsql

create table test1 as select level as a from dual connect by level<1e6;

oracle подхватывает желаемый ТС план

однако, при сохранении пропорций

Код: plsql

1.
2.
3.
4.
5.

update test1 set b=1 where b is null and rownum<100000;
update test1 set b=2 where b is null and rownum<100000;
update test1 set b=3 where b is null and rownum<100000;
update test1 set b=4 where b is null and rownum<100000;
update test1 set b=5 where b is null and rownum<100000;

всё возвращается на круги своя

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

с ув. IT

...

Рейтинг:

0 / 0

27.10.2016, 11:42

| Ответить | Цитировать | Написать

4 сообщений из 4, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не могу уйти от фуллскана таблицы

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39334082&tid=1887136]:	0ms
get settings:	7ms
get forum list:	19ms
check forum access:	4ms
check topic access:	4ms
track hit:	192ms
get topic data:	9ms
get forum data:	3ms
get page messages:	42ms
get tp. blocked users:	2ms
others:	219ms

total:	501ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы