msgolubev
Дата: 10.03.2011 14:15:09
Проблема следующего характера:
Есть две таблицы одна большая (400к) - единицы учета, вторая маленькая (30 строк) - справочник статусов единиц учета. В справочнике статусов есть признак, отвечающий за то в наличии единица учета, находящаяся в данном статусе или нет. Проблема заключается в том, что из 30 статусов, записей имеющих признак "в наличии" много и оптимизатор думает, что проще сделать фулскан по большой таблице.
Скрипт для наглядности:
drop table objects;
drop table status;
create table status as
select rownum status_id, 0 as is_nal
from dual
connect by level <= 30;
update status a
set a.is_nal = 1
where a.status_id > 6;
alter table status add constraint status_ID_PK primary key (status_id);
create table objects as
select
rownum id, 'name_'||rownum text,
case when rownum <= 10 then 1
when rownum <= 1000 then 2
when rownum <= 40000 then 3
when rownum <= 60000 then 4
else 5
end status_id
from dual
connect by level <= 400000;
alter table objects add constraint objects_PK primary key (ID);
alter table objects add constraint objects_status_FK foreign key (status_id) references status (status_id);
create index idx_objects_id_status on objects (status_id);
---
begin
dbms_stats.gather_table_stats(user,'STATUS', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true);
dbms_stats.gather_table_stats(user,'OBJECTS',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true);
end;
Запрос при этом показывает fullscan по таблице objects, а при выполнении по факту не выбирает ни одной записи:
select *
from objects a,
status b
where a.status_id = b.status_id and
b.is_nal = 1
Прошу подсказать, как с этим можно бороться? Понятное дело, что можно поставить хинт, но хотелось бы как то более красиво выкрутиться.
Corner
Дата: 10.03.2011 14:27:55
покажите план и версию.
SQL> create table status as
2 select rownum status_id, 0 as is_nal
3 from dual
4 connect by level <= 30;
Table created.
SQL> update status a
2 set a.is_nal = 1
3 where a.status_id > 6;
24 rows updated.
SQL> alter table status add constraint status_ID_PK primary key (status_id);
Table altered.
SQL> create table objects as
2 select
3 rownum id, 'name_'||rownum text,
4 case when rownum <= 10 then 1
5 when rownum <= 1000 then 2
6 when rownum <= 40000 then 3
7 when rownum <= 60000 then 4
8 else 5
9 end status_id
10 from dual
11 connect by level <= 400000;
Table created.
SQL> alter table objects add constraint objects_PK primary key (ID);
Table altered.
SQL> alter table objects add constraint objects_status_FK foreign key (status_id) references status (s
Table altered.
SQL> create index idx_objects_id_status on objects (status_id);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(user,'STATUS', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=
3 dbms_stats.gather_table_stats(user,'OBJECTS',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> explain plan for
2 select *
3 from objects a,
4 status b
5 where a.status_id = b.status_id and
6 b.is_nal = 1;
Explained.
SQL>
SQL>
SQL> set linesize 500
SQL> set pagesize 500
SQL>
SQL>
SQL> @?\rdbms\admin\utlxpls;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 4257666769
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 391K| 9944K| 4913 (1)| 00:00:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 391K| 9944K| 4913 (1)| 00:00:59 |
|* 3 | TABLE ACCESS FULL | STATUS | 24 | 144 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_OBJECTS_ID_STATUS | 80000 | | 159 (2)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 16667 | 325K| 434 (1)| 00:00:06 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."IS_NAL"=1)
4 - access("A"."STATUS_ID"="B"."STATUS_ID")
18 rows selected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
msgolubev
Дата: 10.03.2011 15:18:20
SQL> explain plan for
2 select *--
3 from objects a,
4 status b
5 where a.status_id = b.status_id and
6 b.is_nal = 1
7
SQL>
Explained
SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2407768300
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 505K| 36M| 284 (4)| 00:00:04 |
|* 1 | HASH JOIN | | 505K| 36M| 284 (4)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| STATUS | 24 | 624 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OBJECTS | 505K| 24M| 278 (3)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."STATUS_ID"="B"."STATUS_ID")
2 - filter("B"."IS_NAL"=1)
Note
-----
- 'PLAN_TABLE' is old version
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement
21 rows selected
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
msgolubev
Дата: 10.03.2011 15:41:54
_Nikotin,
интересное предложение, спасибо, но всё же хотелось бы понять: можно ли как то статистикой (гистограммами) подкрутить?
Corner
Дата: 10.03.2011 17:56:48
wurdu |
---|
Corner, боюсь что для b.is_nal = 0 у тебя будет тот же план... |
Я чесно гворя не совсем понимаю почему вы так думаете??
SQL> explain plan for
2 select *
3 from objects a,
4 status b
5 where a.status_id = b.status_id and
6 b.is_nal = 0;
Explained.
SQL> set linesize 500
SQL> set pagesize 500
SQL>
SQL> @?\rdbms\admin\utlxpls;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
Plan hash value: 4257666769
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400K| 9M| 1231 (1)| 00:00:15 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 400K| 9M| 1231 (1)| 00:00:15 |
|* 3 | TABLE ACCESS FULL | STATUS | 6 | 36 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_OBJECTS_ID_STATUS | 80000 | | 159 (2)| 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| OBJECTS | 66667 | 1302K| 434 (1)| 00:00:06 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."IS_NAL"=0)
4 - access("A"."STATUS_ID"="B"."STATUS_ID")
wurdu |
---|
Собственно для таких случаев и существует Automatic Tuning Optimizer и SQL Profile как результат. Но я предпочитаю хинт. |
ну и про outline тоже можно вспомнить(подсунуть план с хинтами), т.к. хинты это хорошо, но иногда есть моменты когда код поменять нельзя(причин может быть очень много и разноплановые), а план надо зафиксировать.