Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как оптимизировать конкретный запрос / 12 сообщений из 12, страница 1 из 1
10.12.2002, 06:56
    #32077615
Ulba_2002
Ulba_2002
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Подскажите, можно ли как-то оптимально, по другому написать:

SELECT * FROM tab1 a WHERE a.pole_1 IN (SELECT b.pole FROM tab2 b START WITH b.keypole = 'XXX' CONNECT BY PRIOR b.keypole = b.keyparent) OR a.pole_2 IN (SELECT b.pole FROM tab2 b START WITH b.keypole = 'XXX' CONNECT BY PRIOR b.keypole = b.keyparent)

Т.е. в одном и том же подзапросе проверяется значение поля pole_1 или значение поля pole_2. Можно ли написать не указывая дважды подзапрос? А если сделать как указано, то подзапрос будет дважды выполняться? или оптимизатор разберется сам и выполнит один раз? (Oracle 8.1.6i)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2002, 08:10
    #32077629
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Оптимизатор не будет разбираться один подзапрос ему выполнять или два
поскольку результат зависит от данных в таблице на текущий момент.

По поводу оптимизации, несколько лучше выглядит запрос

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
SELECT * 
FROM tab1 a
WHERE exists  (SELECT null
                     FROM tab2 b 
                     where b.pole = a.pole_1 or b.pole = a.pole_2 
                     START WITH b.keypole = 'XXX' 
                     CONNECT BY PRIOR b.keypole = b.keyparent)

т.к. подзапрос будет выполнен только 1 раз всегда
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2002, 09:45
    #32077666
softy
softy
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
to ShgGena: Если раньше оба подзапроса выполнялись всего один раз для основного запроса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT 
  * 
 FROM 
  tab1 a 
 WHERE 
  a.pole_1 IN 
         (SELECT b.pole FROM tab2 b START WITH b.keypole = 'XXX' CONNECT BY
 PRIOR b.keypole = b.keyparent) OR 
  a.pole_2 IN (SELECT b.pole FROM tab2 b START WITH b.keypole = 'XXX'
 CONNECT BY PRIOR b.keypole = b.keyparent)



То теперь в вашем варианте, подзапрос стал коррелированным, и подзапрос выполняется для каждой строки основного запроса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
SELECT * 
FROM tab1 a
WHERE exists  (SELECT null
                     FROM tab2 b 
                     where b.pole = a.pole_1 or b.pole = a.pole_2 
                     START WITH b.keypole = 'XXX' 
                     CONNECT BY PRIOR b.keypole = b.keyparent)


То есть дерево строится каждый раз заново, для каждой строки.
В первоначальном варианте, если условие "a.pole_1 IN..." выполняется, то второе условие уже нет. А в твоём дерево строится каждый раз независимо от этого. Хотя само дерево абсолютно не зависит от таблицы tab1.

Вызывает большое сомнение, что этот запрос более оптимизированный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.12.2002, 19:50
    #32078199
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Код: plaintext
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.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
SQL> select * from a_tree;

   NODE_ID    ROOT_ID
 ---------- ----------
 
	  1 	     0 
	  2 	     1 
	  3 	     1 
	  4 	     1 
	  5 	     2 
	  6 	     2 
	  7 	     3 
	  8 	     3 
	  9 	     3 
	 10 	     4 
	 11 	     4 
	 12 	     4 
	 13 	     7 
	 14 	     7 
	 15 	    10 
	 16 	    10 
	 17 	    10 
	 18 	    12 
	 19 	    12 

 19  rows selected.

SQL> select * from b_tree;

   NODE_ID    ROOT_ID
 ---------- ----------
 
	  4 	     0 
	 11 	     4 
	 12 	     4 
	 20 	     4 
	 21 	    11 
	 22 	    11 
	 18 	    12 
	 23 	    12 
	 24 	    12 
	 25 	    20 

 10  rows selected.


set autotrace on;

select * from a_tree a
where exists (select null from b_tree b
              where b.node_id = a.node_id or b.node_id = a.root_id
              connect by prior b.node_id=b.root_id
	      start with b.node_id =  4 )
;

Execution Plan
 ----------------------------------------------------------
 
    0 	  SELECT STATEMENT Optimizer=CHOOSE (Cost= 1  Card= 1  Bytes= 4 )
    1 	 0    FILTER
    2 	 1      TABLE ACCESS (FULL) OF 'A_TREE' (Cost= 1  Card= 1  Bytes= 4 )
    3 	 1      FILTER
    4 	 3 	CONNECT BY
    5 	 4 	  INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE)
    6 	 4 	  TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
    7 	 4 	  TABLE ACCESS (BY INDEX ROWID) OF 'B_TREE' (Cost= 2  Card= 3  Bytes= 12 )
    8 	 7 	    INDEX (RANGE SCAN) OF 'I_B_ROOT' (NON-UNIQUE) (Cost= 1  Card= 3 )


Statistics
 ----------------------------------------------------------
 
	   0   recursive calls
	   4   db block gets
	 413   consistent gets
	   0   physical reads

select * from a_tree a
where
a.node_id in (select b.node_id from b_tree b
              connect by prior b.node_id=b.root_id
	      start with b.node_id =  4 )
or
a.root_id in (select b.node_id from b_tree b
              connect by prior b.node_id=b.root_id
	      start with b.node_id =  4 )
;

Execution Plan
 ----------------------------------------------------------
 
    0 	  SELECT STATEMENT Optimizer=CHOOSE (Cost= 1  Card= 2  Bytes= 8 )
    1 	 0    FILTER
    2 	 1      TABLE ACCESS (FULL) OF 'A_TREE' (Cost= 1  Card= 2  Bytes= 8 )
    3 	 1      FILTER
    4 	 3 	CONNECT BY
    5 	 4 	  INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE)
    6 	 4 	  TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
    7 	 4 	  TABLE ACCESS (BY INDEX ROWID) OF 'B_TREE' (Cost= 2  Card= 3  Bytes= 12 )
    8 	 7 	    INDEX (RANGE SCAN) OF 'I_B_ROOT' (NON-UNIQUE) (Cost= 1  Card= 3 )
    9 	 1      FILTER
   10 	 9 	CONNECT BY
   11     10 	  INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE)
   12     10 	  TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
   13     10 	  TABLE ACCESS (BY INDEX ROWID) OF 'B_TREE' (Cost= 2  Card= 3  Bytes= 12 )
   14     13 	    INDEX (RANGE SCAN) OF 'I_B_ROOT' (NON-UNIQUE) (Cost= 1  Card= 3 )

Statistics
 ----------------------------------------------------------
 
	   0   recursive calls
	   4   db block gets
	 660   consistent gets
	   0   physical reads


rezultat odinakov dlya dvux zaprosob

   NODE_ID    ROOT_ID
 ---------- ----------
 
	  4 	     1 
	 10 	     4 
	 11 	     4 
	 12 	     4 
	 18 	    12 
	 19 	    12

proshu obratit vnimanie na:
-- vneshniy cikl TABLE ACCESS (FULL) OF 'A_TREE' dlya obouh zaprocov
(a in (select ...)) --> vizivaet "psevdo korrelirovanniy podzapros)
-- consistent gets 413 protiv 660 --> poskoliku inogda nuzno proverat i vtoroe uslovie
to uvelicherie na 50% kolichestva logicheskih chteniy

tak chto ne vse tak procto.

NOTE : obe tablicy analizirovanny, eto vidno iz (Cost= ...)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 02:14
    #32078236
vskv
vskv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
2ShgGena: На табличке с всего 19 записей Оракул всегда будет выбирать FULL_SCAN. Дешевле всего, потому что...

2ShgGena, softbuilder: Это у вас уже дискуссия на тему, что быстрее EXISTS или IN. Не очень давно на пробегал линк на статью, где всё это уже обсуждалось -- http://ln.com.ua/~openxs/projects/oracle/ora018.html .

2Ulba_2002: Как показывает статистика от ShgGena, к сожалению в варианте с IN и OR подзапрос будет выполнен дважды. Но с высокой степенью вероятности, его "цена" будет ниже за счёт того, что все данные будут в buffer cache. Ну а выбор, что в вашем случае быстрее EXISTS или IN за вами -- смотрите линк выше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 03:25
    #32078238
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Код: plaintext
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.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
SQL> truncate table a_tree;

Table truncated.

SQL> exec tree_builder('a_tree',  4 );

PL/SQL procedure successfully completed.

SQL> select count(*) from a_tree;

  COUNT(*)
 ----------
 
      88912 

SQL>
SQL> analyze table a_tree compute statistics;

Table analyzed.

SQL> set autotrace traceonly;
SQL> set timing on;
SQL>
SQL> select * from a_tree a
   2   where exists (select null from b_tree b
   3                 where b.node_id = a.node_id or b.node_id = a.root_id
   4                 connect by prior b.node_id=b.root_id
   5            start with b.node_id =  4 )
   6   ;

no rows selected

Elapsed:  00 : 00 : 02 . 00 

Execution Plan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE (Cost= 8911  Card= 4446  Bytes
          = 31122 )

    1      0    FILTER
    2      1      TABLE ACCESS (FULL) OF 'A_TREE' (Cost= 19  Card= 4446  Bytes
          = 31122 )

    3      1      FILTER
    4      3        CONNECT BY (WITH FILTERING)
    5      4          NESTED LOOPS
    6      5            INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE) (Cost=
           1  Card= 1  Bytes= 13 )

    7      5            TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
    8      4          HASH JOIN
    9      8            CONNECT BY PUMP
   10      8            TABLE ACCESS (FULL) OF 'B_TREE' (Cost= 2  Card= 82  By
          tes= 2132 )





Statistics
 ----------------------------------------------------------
 
         307   recursive calls
           0   db block gets
       89146   consistent gets
           6   physical reads
           0   redo size
         278   bytes sent via SQL*Net to client
         368   bytes received via SQL*Net from client
           1   SQL*Net roundtrips to/from client
       88918   sorts (memory)
           0   sorts (disk)
           0   rows processed

SQL> select * from a_tree a
   2   where
   3   a.node_id in (select b.node_id from b_tree b
   4                 connect by prior b.node_id=b.root_id
   5            start with b.node_id =  4 )
   6   or
   7   a.root_id in (select b.node_id from b_tree b
   8                 connect by prior b.node_id=b.root_id
   9            start with b.node_id =  4 )
  10   ;

no rows selected

Elapsed:  00 : 00 : 02 . 00 

Execution Plan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE (Cost= 19  Card= 8669  Bytes= 6 
           0683 )

    1      0    FILTER
    2      1      TABLE ACCESS (FULL) OF 'A_TREE' (Cost= 19  Card= 8669  Bytes
          = 60683 )

    3      1      FILTER
    4      3        CONNECT BY (WITH FILTERING)
    5      4          NESTED LOOPS
    6      5            INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE) (Cost=
           1  Card= 1  Bytes= 13 )

    7      5            TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
    8      4          HASH JOIN
    9      8            CONNECT BY PUMP
   10      8            TABLE ACCESS (FULL) OF 'B_TREE' (Cost= 2  Card= 82  By
          tes= 2132 )

   11      1      FILTER
   12     11        CONNECT BY (WITH FILTERING)
   13     12          NESTED LOOPS
   14     13            INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE) (Cost=
           1  Card= 1  Bytes= 13 )

   15     13            TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
   16     12          HASH JOIN
   17     16            CONNECT BY PUMP
   18     16            TABLE ACCESS (FULL) OF 'B_TREE' (Cost= 2  Card= 82  By
          tes= 2132 )





Statistics
 ----------------------------------------------------------
 
           0   recursive calls
           0   db block gets
       91512   consistent gets
           0   physical reads
           0   redo size
         278   bytes sent via SQL*Net to client
         368   bytes received via SQL*Net from client
           1   SQL*Net roundtrips to/from client
       91328   sorts (memory)
           0   sorts (disk)
           0   rows processed

SQL> select * from a_tree a
   2   where exists (select null from b_tree b
   3                 where b.node_id = a.node_id or b.node_id = a.root_id
   4                 connect by prior b.node_id=b.root_id
   5            start with b.node_id =  4 )
   6   ;

no rows selected

Elapsed:  00 : 00 : 01 . 09 

Execution Plan
 ----------------------------------------------------------
 
    0       SELECT STATEMENT Optimizer=CHOOSE (Cost= 8911  Card= 4446  Bytes
          = 31122 )

    1      0    FILTER
    2      1      TABLE ACCESS (FULL) OF 'A_TREE' (Cost= 19  Card= 4446  Bytes
          = 31122 )

    3      1      FILTER
    4      3        CONNECT BY (WITH FILTERING)
    5      4          NESTED LOOPS
    6      5            INDEX (UNIQUE SCAN) OF 'PK_B_TREE' (UNIQUE) (Cost=
           1  Card= 1  Bytes= 13 )

    7      5            TABLE ACCESS (BY USER ROWID) OF 'B_TREE'
    8      4          HASH JOIN
    9      8            CONNECT BY PUMP
   10      8            TABLE ACCESS (FULL) OF 'B_TREE' (Cost= 2  Card= 82  By
          tes= 2132 )





Statistics
 ----------------------------------------------------------
 
           0   recursive calls
           0   db block gets
       89096   consistent gets
           0   physical reads
           0   redo size
         278   bytes sent via SQL*Net to client
         368   bytes received via SQL*Net from client
           1   SQL*Net roundtrips to/from client
       88912   sorts (memory)
           0   sorts (disk)
           0   rows processed

SQL>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 03:52
    #32078240
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Прошу извинения за предидущий длинный пост. Просто вместо ПРОСМОТР нажал ОТПРАВИТЬ,
а корректировать нельзя.

Тем не менее:
1) о FULL SCAN TABLE - эта операция остается в любом случае поскольку:
-- в основном запросе стоит select * ... но до выборки строки невозможно принять
решение о результате операции сравнения в where (ДЛЯ ОБОИХ ЗАПРОСОВ)

2) по поводу обшей производительности запроса
основным критерием времени его выполнения будет не IN или EXISTS в данном случае а:
-- количество сортировок и длина сортируемой последовательности в подзапросах
при этом если -- длина последовательности не влезает в sort_area_size то мы получим
огромную деградацию производительности особенно если внутренний(ние) подзапросы
работают с "широким" деревом у которого мало уровней и много строк на каждом уровне
при одинаковом обшем количестве строк в таблице.

поскльку мой вариант хоть как-то сокращает количество сортировок то я его считаю
более оптимальным (с этим можно конечно не соглашаться)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 11:28
    #32078347
softy
softy
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Насчёт твоей последней статистки не понял, что ты хотел этим сказать. Для твоего запроса стоимость 8911, для первоначального 19. Потом запросы не возвращает строк, это тоже не интересно.
А для проверки под RULE, я советую тебе для сессии сделать alter session set optimizer_mode=RULE; Так как у тебя CHOOSE стоит. И непонятно какой реальный метод используется
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 13:33
    #32078462
Lazy
Lazy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
2ShgGena: Судя по всему твой вариант меньше ест ресурсов и быстрее выполняется. Последнее устойчиво или нет, и какие времена при другом порядке выполнения вариантов (2-1-2) ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.12.2002, 18:48
    #32078714
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
2 softbuilder@inbox.ru

1. Po povodu sravneniya COST v zaprosah
-- COST - velichina otnositelnya i TOLKO otnositelno raznih planov vipolneniya etogo i tolko ETOGO zaprosa.
-- izmeneniya v where ili v spiske poley - eto NOVIY ZAPROS i sravnenie cost dbuh raznih zaprosov prosto BESSMISLENO
poetomu sravnenie 19 i 8911 eto kak sravnenie apelsinov s taburetkami.
2. test s RULE sdelau pozge, no polagau chto rezultat budet analogichniy ili blizkiy

3. po povodu 0 row selected -- eto hudshiy predelniy variant dly OBOIH zaprosov poetomu i ego vitolnil.

4. echo raz podcherkivau chto v HIERARCHICAL zaprosah osnovnim factorom vremeni vipolneniya yavlyaetsya:

KOLICHESTVO i RAZMER vnutrennich sortirovok no nikak ne IN ili EXISTS.
I vipolnyal desyatki testov s raznimi dereviyami i statistika ochen postoiannaya, pochti formulnaya.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.12.2002, 15:29
    #32078862
.dba
.dba
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
>1. Po povodu sravneniya COST v zaprosah
>-- COST - velichina otnositelnya i TOLKO otnositelno raznih planov vipolneniya
>etogo i tolko ETOGO zaprosa.
>-- izmeneniya v where ili v spiske poley - eto NOVIY ZAPROS i sravnenie cost
>dbuh raznih zaprosov prosto BESSMISLENO
>poetomu sravnenie 19 i 8911 eto kak sravnenie apelsinov s taburetkami.

Не думаю, что cost - величина относительная. Т.к. при изменении запроса происходит новый парсинг и план выполнения оценивается по новому, а не относительно старого.

Другое дело было бы неплохо, если была бы возможность посмотреть приблизительно одинаковые по стоимости планы выполнения. Т.к. стоимоть может отличаться на одну, две единицы, а запрос с худшей стоимостью будет выполняться в десятки раз быстрее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.12.2002, 15:51
    #32078868
ShgGena
ShgGena
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать конкретный запрос
Я я что написал. Cost и сравнение cost имеет смысл только для конкретного запроса
и только для сравнения разных планов этого запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как оптимизировать конкретный запрос / 12 сообщений из 12, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 483ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1992474]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 157ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 239ms
total:  483ms
x
x
Закрыть


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