softwarerТы что с чем сравнивал, уважаемый проверяющий? :)
Поскольку уважаемый Бобр занят важными делами, позволю себе краткую иллюстрацию. Сделано на Oracle, но принципы одинаковы для любой СУБД.
Создадим условия для проверки. Таблица с данными по разным "филиалам", один большой - Москва, остальные маленькие.
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.
SQL> create table data (
2 id integer not null,
3 server_id integer not null,
4 data_date date not null,
5 data_value varchar2( 10 ));
Table created
Executed in 0 , 016 seconds
SQL> insert into data
2 select rownum, case when mod (rownum, 100 ) < 50 then 0 else mod (rownum, 100 ) end,
3 sysdate + rownum/ 1440 , 'aaaaaaaa'
4 from dual
5 connect by level <= 1000000 ;
1000000 rows inserted
Executed in 4 , 984 seconds
SQL> alter table data add primary key (id);
Table altered
Executed in 1 , 531 seconds
SQL> create index i_data_server on data (server_id);
Index created
Executed in 0 , 844 seconds
SQL> create index i_data_date on data (data_date);
Index created
Executed in 3 , 125 seconds
SQL> exec dbms_stats.gather_schema_stats (ownname => user);
PL/SQL procedure successfully completed
Executed in 6 , 515 seconds
Посмотрим, как будет выполняться обычный запрос какой-нибудь списочной формы, "документы по этому серверу в порядке по дате". Чтобы максимизировать шансы server_id, без других фильтров.
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.
SQL> explain plan for
2 select /*+ first_rows(100) */ *
3 from data
4 where server_id = 0
5 order by data_date;
Explained
Executed in 0 , 016 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1426867570
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 2424 | 37 ( 0 )| 00 : 00 : 01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DATA | 19638 | 460K| 37 ( 0 )| 00 : 00 : 01 |
| 2 | INDEX FULL SCAN | I_DATA_DATE | 5151 | | 16 ( 0 )| 00 : 00 : 01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SERVER_ID"= 0 )
14 rows selected
Executed in 0 , 015 seconds
Вот незадача - сервер почему-то не хочет использовать "отдельное поле server_id", ему пофиг, что по нему быстро фильтруется. Почему же это? Заставим его таки использовать индекс:
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.
SQL> explain plan for
2 select /*+ first_rows(100) index(data i_data_server)*/ *
3 from data
4 where server_id = 0
5 order by data_date;
Explained
Executed in 0 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3277673813
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19638 | 460K| | 4255 ( 1 )| 00 : 00 : 52 |
| 1 | SORT ORDER BY | | 19638 | 460K| 1400K| 4255 ( 1 )| 00 : 00 : 52 |
| 2 | TABLE ACCESS BY INDEX ROWID| DATA | 19638 | 460K| | 4112 ( 1 )| 00 : 00 : 50 |
|* 3 | INDEX RANGE SCAN | I_DATA_SERVER | 19638 | | | 40 ( 3 )| 00 : 00 : 01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SERVER_ID"= 0 )
15 rows selected
Executed in 0 , 016 seconds
Ну вот и ответ, почему. Потому что с точки зрения сервера так будет в 50 раз дольше. Проверим ещё для немосковского филиала:
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.
SQL> explain plan for
2 select /*+ first_rows(100) */ *
3 from data
4 where server_id = 55
5 order by data_date;
Explained
Executed in 0 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1426867570
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 2424 | 37 ( 0 )| 00 : 00 : 01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DATA | 19638 | 460K| 37 ( 0 )| 00 : 00 : 01 |
| 2 | INDEX FULL SCAN | I_DATA_DATE | 5151 | | 16 ( 0 )| 00 : 00 : 01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SERVER_ID"= 55 )
14 rows selected
Executed in 0 , 016 seconds
Ну да, конечно, никакой разницы. Ладно, предположим, что мы криво собрали статистику, и сервер не любит server_id из-за этого. Попробуем учесть перекос в данных:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SQL> exec dbms_stats.gather_table_stats (ownname => user, tabname => 'DATA',
2 method_opt => 'for all columns size auto');
PL/SQL procedure successfully completed
Executed in 3 , 891 seconds
SQL> select column_name, num_buckets from user_tab_columns where table_name = 'DATA';
COLUMN_NAME NUM_BUCKETS
------------------------------ -----------
ID 1
SERVER_ID 51
DATA_DATE 1
DATA_VALUE 1
Executed in 0 , 015 seconds
Отлично, есть гистограммы по server_id, вот теперь-то он точно будет использоваться!
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.
SQL> explain plan for
2 select /*+ first_rows(100) */ *
3 from data
4 where server_id = 0
5 order by data_date;
Explained
Executed in 0 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1426867570
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 4 ( 0 )| 00 : 00 : 01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DATA | 497K| 11M| 4 ( 0 )| 00 : 00 : 01 |
| 2 | INDEX FULL SCAN | I_DATA_DATE | 201 | | 3 ( 0 )| 00 : 00 : 01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SERVER_ID"= 0 )
14 rows selected
Executed in 0 , 015 seconds
SQL> explain plan for
2 select /*+ first_rows(100) index(data i_data_server)*/ *
3 from data
4 where server_id = 0
5 order by data_date;
Explained
Executed in 0 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3277673813
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 497K| 11M| | 107K ( 1 )| 00 : 21 : 36 |
| 1 | SORT ORDER BY | | 497K| 11M| 34M| 107K ( 1 )| 00 : 21 : 36 |
| 2 | TABLE ACCESS BY INDEX ROWID| DATA | 497K| 11M| | 104K ( 1 )| 00 : 20 : 54 |
|* 3 | INDEX RANGE SCAN | I_DATA_SERVER | 497K| | | 958 ( 2 )| 00 : 00 : 12 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SERVER_ID"= 0 )
15 rows selected
Executed in 0 , 016 seconds
SQL> explain plan for
2 select /*+ first_rows(100) */ *
3 from data
4 where server_id = 55
5 order by data_date;
Explained
Executed in 0 , 015 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1426867570
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 72 ( 2 )| 00 : 00 : 01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DATA | 9813 | 229K| 72 ( 2 )| 00 : 00 : 01 |
| 2 | INDEX FULL SCAN | I_DATA_DATE | 10175 | | 29 ( 0 )| 00 : 00 : 01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SERVER_ID"= 55 )
14 rows selected
Executed in 0 , 016 seconds
SQL> explain plan for
2 select /*+ first_rows(100) index(data i_data_server)*/ *
3 from data
4 where server_id = 55
5 order by data_date;
Explained
Executed in 0 seconds
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 3277673813
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9813 | 229K| | 2136 ( 1 )| 00 : 00 : 26 |
| 1 | SORT ORDER BY | | 9813 | 229K| 712K| 2136 ( 1 )| 00 : 00 : 26 |
| 2 | TABLE ACCESS BY INDEX ROWID| DATA | 9813 | 229K| | 2063 ( 1 )| 00 : 00 : 25 |
|* 3 | INDEX RANGE SCAN | I_DATA_SERVER | 9813 | | | 21 ( 0 )| 00 : 00 : 01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SERVER_ID"= 55 )
15 rows selected
Executed in 0 , 015 seconds
Странно.... стало ещё хуже. Сервер по-прежнему использует совсем другой индекс, да ещё и пугает совсем дикими цифрами.
Дальнейшие пояснения требуются?
