Про индексы 2 FFS

AlexGru
Дата: 18.11.2008 09:21:11
2 простые таблицы обе с пк, из одной граничение фк на другую.
Соединяю таблицы по фк, + простой фильтр на одной таблице (выбирается 1130 записей из 72000). Если в части SELECT только T1,ID, T2.ID (первичные ключи),
то стоимость маленькая и план

SELECT STATEMENT	
HASH JOIN	
HASH JOIN	
TABLE ACCESS	BY INDEX ROWID
INDEX	RANGE SCAN
INDEX	FAST FULL SCAN
INDEX	FAST FULL SCAN


Если добавить в SELECT ещё поле какое-нибудь не входящее в ПК, то план сразу становится таким
SELECT STATEMENT	
HASH JOIN	
HASH JOIN	
TABLE ACCESS	BY INDEX ROWID
INDEX	RANGE SCAN
TABLE ACCESS	FULL
INDEX	FAST FULL SCAN


Как я понимаю в первом случае вообще достаточно только индекса и обращение к таблице не требуется???
Вопрошатель
Дата: 18.11.2008 09:30:50
а что собственно удивляет/непонятно?
stax..
Дата: 18.11.2008 09:47:38
Вопрошатель
а что собственно удивляет/непонятно?

иногда удивляет неоправданная любов оракля к HASH JOIN

часто замена на банальний NL в разы быстрее

.....
stax
AlexGru
Дата: 18.11.2008 14:17:52
Есть табличка ID (уник. ПК), ключ, значение

ключ    кол-во записей
21	340
22	362
23	412
24	373
25	386
26	353
27	356
28	375
29	381
30	367
31	359
32	392
33	383
34	379
35	366
36	407
37	359
38	383
39	380
40	407
41	313
42	358
43	397
44	415
45	377
46	352
47	420
48	368
49	387
50	350
51	394
52	377
53	360
54	370
55	375
56	374
57	378
58	390
59	384
60	357
61	364
62	371
63	388
64	373
65	360
66	348
67	380
68	368
69	390
70	374
71	390
72	368
73	370
74	367
75	424
76	352
77	351
78	370
79	374
80	397
81	393
82	411
83	391
84	439
85	396
86	392
87	371
88	369
89	359
90	378
91	405
92	365
93	357
94	370
95	381
96	358
97	388
98	353
99	413
100	361
101	371
102	369
103	383
104	361
105	364
106	362
107	366
108	391
109	394
110	365
111	357
112	381
113	385
114	383
115	340
116	369
117	378
118	402
119	385
120	360
121	392
122	362
123	388
124	351
125	366
126	388
127	386
128	390
129	385
130	411
131	395
132	356
133	369
134	348
135	375
136	407
137	375
138	375
139	376
140	374
141	384
142	360
143	377
144	386
145	391
146	367
147	366
148	376
149	369
150	369
151	395
152	396
153	369
154	336
155	356
156	353
157	385
158	381
159	354
160	402
161	379
162	368
163	339
164	349
165	359
166	393
167	382
168	350
169	368
170	388
171	384
172	371
173	378
174	366
175	374
176	374
177	388
178	403
179	401
180	379
181	350
182	346
183	373
184	367
185	362
186	405
187	368
188	376
189	366
190	352
191	353
192	391
193	378
194	364
195	364


Это примерное распределение

SELECT *
FROM
TBL WHERE TBL.ключ=21

Будет использоваться фс или простой индекс по столбцу (ключ)

??????????????7
August
Дата: 18.11.2008 14:33:49
ну - в идеале будет поиск по индексу - но это не обязательно
ЗЫ
а что у вас в примере варианты заканчиваются именно на 195? можно же было и до 1000 дойти
Я и ёжик
Дата: 18.11.2008 14:55:09
AlexGru
Будет использоваться фс или простой индекс по столбцу (ключ)

??????????????7

Это зависит от ...
фактора кластеризации таблицы по ключу индекса, числа блоков в таблице и ндексе (вернее статистики об этих параметрах) и еще кучи факторов в зависимости от используемой модели стоимости, выбраного режимаоптимизатора и т.д.
AlexGru
Дата: 18.11.2008 15:00:37
Я никак не могу понять почему он не хочет индекс юзать.
Фактор кластеризации 230, Кол-во блоков 247.
Кол-во записей 72000.
Может как раз ф.к. примерно равен кол-ву блоков, т.е. данные плотно уложены. И поэтому он выбирает полное сканирование. Хотя.
А как можно бысто сделать так чтобы ф.к. оказался примерно равным кол-ву записей.
Много Update, delete, insert???????
Может после этого он индекс будет юзать.
_xcb
Дата: 18.11.2008 15:29:58
Извините навеяло
Петька, приборы
45
Что 45?
а что приборы?

то Вы постите планы, без числовых колонок, то некие цифры из статистики...
Думаете кто то будет пытаться это все просчитать?

Неизвестных (режим работы отптимизатора, параметры сеанса) пока больше чем известных получается...
AlexGru
Дата: 19.11.2008 07:06:37
_xcb
Извините навеяло
Петька, приборы
45
Что 45?
а что приборы?

то Вы постите планы, без числовых колонок, то некие цифры из статистики...
Думаете кто то будет пытаться это все просчитать?

Неизвестных (режим работы отптимизатора, параметры сеанса) пока больше чем известных получается...


Oracle 10.2.0

Скрипт таблицы
create table MAIN_TABLE
(
  ID            NUMBER not null,
  ID_MED_INST   NUMBER not null,
  BIRTHDATE     DATE not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    minextents 1
    maxextents unlimited
  );

alter table MAIN_TABLE add constraint PK_MAIN_TABLE primary key (ID);

create index UNIQUE_ID_IDMEDINST on MAIN_TABLE (ID_MED_INST);

Статистика.
     begin
      dbms_stats.GATHER_TABLE_STATS('STROKE_TEST','MAIN_TABLE',cascade=>TRUE);
     end; 

72000 записей.

select * from main_table t
where t.id_med_inst=122
Возвращает 362 записи


Вопрос, почему при таком запросе
          
 SELECT 
      MT.ID,
      MT.BIRTHDATE
  FROM
      MAIN_TABLE  MT
 WHERE 
      MT.ID_MED_INST=122

Используется полное сканирование таблицы.


Вот трассы:
1)
SELECT /*+ INDEX (MT UNIQUE_ID_IDMEDINST)*/
      MT.ID,
      MT.BIRTHDATE
  FROM
      MAIN_TABLE  MT
 WHERE 
      MT.ID_MED_INST=122

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         55          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         55          0         100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 195  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS BY INDEX ROWID MAIN_TABLE (cr=55 pr=0 pw=0 time=729 us)
    100   INDEX RANGE SCAN UNIQUE_ID_IDMEDINST (cr=2 pr=0 pw=0 time=19 us)(object id 167236)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       13      0.00       0.01          0          0          0           0
Execute     13      0.00       0.01          0          0          0           8
Fetch        3      0.00       0.00          0         55          0         102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29      0.00       0.03          0         55          0         110

Misses in library cache during parse: 4
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      16        0.00          0.00
  SQL*Net message from client                    16        3.13          5.87


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          2          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1

   14  user  SQL statements in session.
    1  internal SQL statements in session.
   15  SQL statements in session.
********************************************************************************

2)

SELECT 
      MT.ID,
      MT.BIRTHDATE
  FROM
      MAIN_TABLE  MT
 WHERE 
      MT.ID_MED_INST=122

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         67          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         67          0         100

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 195  

Rows     Row Source Operation
-------  ---------------------------------------------------
    100  TABLE ACCESS FULL MAIN_TABLE (cr=67 pr=0 pw=0 time=1358 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       13      0.00       0.00          0          0          0           0
Execute     13      0.00       0.00          0          0          0           8
Fetch        3      0.00       0.00          0         67          0         102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       29      0.00       0.00          0         67          0         110

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      16        0.00          0.00
  SQL*Net message from client                    16        2.13          4.25


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

   14  user  SQL statements in session.
    0  internal SQL statements in session.
   14  SQL statements in session.
********************************************************************************

Хочется именно понять почему Oracle считает (может таки есть на самом деле ИМХО) что полное сканирование эффективнее?????

Спасибо.
AlexGru
Дата: 19.11.2008 07:33:54
Провёл ещё тест чтобы время засечь.

34.84 s
declare 
 CURSOR C IS
  SELECT /* INDEX (MT UNIQUE_ID_IDMEDINST)*/
      MT.ID,
      MT.BIRTHDATE
  FROM
      MAIN_TABLE  MT
 WHERE 
      MT.ID_MED_INST=122;
 type type_tbl is table of C%ROWTYPE;
 tbl  type_tbl;
 t number := dbms_utility.get_time;
begin


 FOR i in 1..10000 LOOP
 OPEN C;
 FETCH C BULK COLLECT INTO tbl;
 CLOSE C;
 END LOOP; 
 
 dbms_output.put_line(ROUND((dbms_utility.get_time-t)/100,3)||' s'); 
end;



4.78 s
declare 
 CURSOR C IS
  SELECT /*+ INDEX (MT UNIQUE_ID_IDMEDINST)*/
      MT.ID,
      MT.BIRTHDATE
  FROM
      MAIN_TABLE  MT
 WHERE 
      MT.ID_MED_INST=122;
 type type_tbl is table of C%ROWTYPE;
 tbl  type_tbl;
 t number := dbms_utility.get_time;
begin


 FOR i in 1..10000 LOOP
 OPEN C;
 FETCH C BULK COLLECT INTO tbl;
 CLOSE C;
 END LOOP; 
 
 dbms_output.put_line(ROUND((dbms_utility.get_time-t)/100,3)||' s'); 
end;


+ если использовать RULE, используется индекс.

В чём проблема. Какие-то параметры сбивают CBO????
С чего начать поиск???