Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не могу уйти от фуллскана таблицы / 4 сообщений из 4, страница 1 из 1
26.10.2016, 06:19:17
    #39334031
Pavel_PV
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, 09:02:33
    #39334082
landy
landy
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не могу уйти от фуллскана таблицы
Если Oracle 11, то как вариант через baseline привязать нужный план к вашему запросу
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2016, 09:09:01
    #39334086
SeaGate
SeaGate
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не могу уйти от фуллскана таблицы
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
| Ответить | Цитировать | Написать  
27.10.2016, 11:42:59
    #39335164
ITest
ITest
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не могу уйти от фуллскана таблицы
при увеличении test1
Код: plsql
1.
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
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не могу уйти от фуллскана таблицы / 4 сообщений из 4, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 475ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1887136]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 193ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 26ms
get tp. blocked users:
 1ms
others: 220ms
total:  475ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]