fullscan с гистограммами возвращающий 0 строк

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

wurdu
Дата: 10.03.2011 14:50:09
Corner, боюсь что для b.is_nal = 0 у тебя будет тот же план...
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
_Nikotin
Дата: 10.03.2011 15:25:23
+ precompute_subquery
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as test_user
 
SQL> explain plan for
  2  select *
  3  from objects a,
  4       status b
  5  where a.status_id = b.status_id
  6    and a.status_id in (select /*+ precompute_subquery */ status_id from status where is_nal = 1);
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1146643834
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |    15 |  1905 |
|*  1 |  HASH JOIN                    |                       |    15 |  1905 |
|   2 |   INLIST ITERATOR             |                       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| OBJECTS               |    15 |  1815 |
|*  4 |     INDEX RANGE SCAN          | IDX_OBJECTS_ID_STATUS |    15 |       |
|   5 |   INLIST ITERATOR             |                       |       |       |
|   6 |    TABLE ACCESS BY INDEX ROWID| STATUS                |    30 |   180 |
|*  7 |     INDEX UNIQUE SCAN         | STATUS_ID_PK          |    24 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."STATUS_ID"="B"."STATUS_ID")
   4 - access("A"."STATUS_ID"=7 OR "A"."STATUS_ID"=8 OR "A"."STATUS_ID"=9 OR
              "A"."STATUS_ID"=10 OR "A"."STATUS_ID"=11 OR "A"."STATUS_ID"=12 OR
              "A"."STATUS_ID"=14 OR "A"."STATUS_ID"=15 OR "A"."STATUS_ID"=16 OR
              "A"."STATUS_ID"=18 OR "A"."STATUS_ID"=19 OR "A"."STATUS_ID"=20 OR
              "A"."STATUS_ID"=22 OR "A"."STATUS_ID"=23 OR "A"."STATUS_ID"=24 OR
              "A"."STATUS_ID"=26 OR "A"."STATUS_ID"=27 OR "A"."STATUS_ID"=28 OR
              "A"."STATUS_ID"=30)
   7 - access("B"."STATUS_ID"=7 OR "B"."STATUS_ID"=8 OR "B"."STATUS_ID"=9 OR
              "B"."STATUS_ID"=10 OR "B"."STATUS_ID"=11 OR "B"."STATUS_ID"=12 OR
              "B"."STATUS_ID"=14 OR "B"."STATUS_ID"=15 OR "B"."STATUS_ID"=16 OR
              "B"."STATUS_ID"=18 OR "B"."STATUS_ID"=19 OR "B"."STATUS_ID"=20 OR
              "B"."STATUS_ID"=22 OR "B"."STATUS_ID"=23 OR "B"."STATUS_ID"=24 OR
              "B"."STATUS_ID"=26 OR "B"."STATUS_ID"=27 OR "B"."STATUS_ID"=28 OR
              "B"."STATUS_ID"=30)
 
33 rows selected
SQL> explain plan for
  2  select *
  3  from objects a,
  4       status b
  5  where a.status_id = b.status_id
  6    and a.status_id in (select /*+ precompute_subquery */ status_id from status where is_nal = 0);
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 700801115
--------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |   400K|    48M|  1950   (
|*  1 |  HASH JOIN                    |              |   400K|    48M|  1950   (
|   2 |   INLIST ITERATOR             |              |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| STATUS       |    30 |   180 |     2   (
|*  4 |     INDEX UNIQUE SCAN         | STATUS_ID_PK |     6 |       |     1   (
|*  5 |   TABLE ACCESS FULL           | OBJECTS      |   400K|    46M|  1945   (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."STATUS_ID"="B"."STATUS_ID")
   4 - access("B"."STATUS_ID"=1 OR "B"."STATUS_ID"=2 OR "B"."STATUS_ID"=3 OR
              "B"."STATUS_ID"=4 OR "B"."STATUS_ID"=5 OR "B"."STATUS_ID"=6)
   5 - filter("A"."STATUS_ID"=1 OR "A"."STATUS_ID"=2 OR "A"."STATUS_ID"=3 OR
              "A"."STATUS_ID"=4 OR "A"."STATUS_ID"=5 OR "A"."STATUS_ID"=6)
 
21 rows selected
msgolubev
Дата: 10.03.2011 15:41:54
_Nikotin,
интересное предложение, спасибо, но всё же хотелось бы понять: можно ли как то статистикой (гистограммами) подкрутить?
wurdu
Дата: 10.03.2011 16:04:55
Собственно для таких случаев и существует Automatic Tuning Optimizer и SQL Profile как результат. Но я предпочитаю хинт.
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 тоже можно вспомнить(подсунуть план с хинтами), т.к. хинты это хорошо, но иногда есть моменты когда код поменять нельзя(причин может быть очень много и разноплановые), а план надо зафиксировать.
_Nikotin
Дата: 10.03.2011 18:33:04
Corner
Я чесно гворя не совсем понимаю почему вы так думаете??

...
Plan hash value: 4257666769
...

Разве другой :) ?
Corner
Дата: 10.03.2011 18:41:58
_Nikotin,

я чего то посчитал что wurdu сказал что план будет другой,вот поэтому и .. это я туплю.