Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Сортировка по неселективному индексу  [new]
gandalf-the-grey
Member

Откуда: НН
Сообщений: 179
Доброго! Подскажите, нормально ли такое поведение.
Есть табличка пусть нексколько тысяч записей, по col2 индекс, малоселективный (3-4 значения)

Есть 3 запроса:
1.
select rownum rn, trowid
from (select rowid trowid
from table1
where 1 = 1
order by col2)
2.
select rownum rn, trowid
from (select rowid trowid
from table1
where 1 = 1
order by col2)
where rownum <= &l_to_line
3.
select rownum rn, trowid
from (select rowid trowid
from table1
where 1 = 1
order by col2, rowid)
where rownum <= &l_to_line


Так вот запросы 1 и 2 возвращают РАЗНЫЕ rowid в первых строках. Если добавить rowid в сортировку - всё становится нормально ессно.
14 фев 17, 19:48    [20211801]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
Куча она такая...
Guest
gandalf-the-grey, а кто обещал доставать строки с одинаковыми col2 в одном и том же порядке?
14 фев 17, 20:36    [20211869]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26877
gandalf-the-grey
Так вот запросы 1 и 2 возвращают РАЗНЫЕ rowid в первых строках.
Результаты упорядочены по col2? - А большего ты не просил.
15 фев 17, 07:38    [20212412]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
gandalf-the-grey
Member

Откуда: НН
Сообщений: 179
Elic,

Да, я вчера слишком абстрагировался от конкретной проблемы, которая заключается в следующем:

реализую поиск неких данных по произвольному набору параметров с последующим постраничным отображением с сортировкой, воспользовавшись советами коллег с форума, за что им большое спасибо
про параметры
про пейджинг
И всё бы хорошо, но когда поле сортировки "неудачное" - получается фигня.
Неудачное - в смысле что оптимизатор выбирает TABLE ACCESS FULL
При выбраном методе доступа для запроса типа
select --+FIRST_ROWS(100) --+gather_plan_statistics --a2
rn, trowid 
from (select rownum rn, trowid
from (select t.rowid trowid
from ref_terminals t
where 1 = 1
order by t.current_state)
where rownum <= &l_to_line)
where rn >= &l_from_line
and rownum <= &l_to_line - &l_from_line + 1

с планом
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |      1 |        |       |       | 23276 (100)|          |    100 |00:00:01.84 |   46073 |      2 |       |       |          |
|*  1 |  COUNT STOPKEY            |               |      1 |        |       |       |            |          |    100 |00:00:01.84 |   46073 |      2 |       |       |          |
|*  2 |   VIEW                    |               |      1 |    400 | 10000 |       | 23276   (1)| 00:04:40 |    100 |00:00:01.84 |   46073 |      2 |       |       |          |
|*  3 |    COUNT STOPKEY          |               |      1 |        |       |       |            |          |    400 |00:00:01.84 |   46073 |      2 |       |       |          |
|   4 |     VIEW                  |               |      1 |   1984K|    22M|       | 23276   (1)| 00:04:40 |    400 |00:00:01.84 |   46073 |      2 |       |       |          |
|*  5 |      SORT ORDER BY STOPKEY|               |      1 |   1984K|    30M|    45M| 23276   (1)| 00:04:40 |    400 |00:00:01.84 |   46073 |      2 | 36864 | 36864 |     1/0/0|
|   6 |       TABLE ACCESS FULL   | REF_TERMINALS |      1 |   1984K|    30M|       | 12731   (1)| 00:02:33 |   1984K|00:00:01.26 |   46073 |      2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Получается казус, что в некоторых диапазонах значений переменных номеров записей возвращаются ОДНИ и ТЕ ЖЕ ROWID
Т.е., например, для диапазонов 201-300 и 501-600 в моём случае возвращается один набор ROWID (но с разными RN).

Можно как-то избежать этого, кроме как создавать условия для того, чтобы первым шагом всегда был INDEX SCAN?
15 фев 17, 13:13    [20213799]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
gandalf-the-grey
Member

Откуда: НН
Сообщений: 179
Хм... Я озадачен. Помогите, пожалуйста, решить шараду...
Первые два кейса, одинаковый запрос, разные переменные - индекс используется, но пейджинг возвращает одинаковые данные для нескольких разных страниц.

Третий вариант работает корректно для всех страниц.

Разница в INDEX FAST FULL SCAN против INDEX FULL SCAN
FAST FULL не гарантирует порядок чтения блоков в отличие от просто FULL

Как-то можно заставить его в общем случае ВСЕГДА использовать FULL INDEX SCAN?
Или нужно применить какие-то другие техники, когда нужно постраничное отображение с сортировкой, причем сортировка может быть по полю, содержащее одинаковые значения для всего возвращенного набора?

select --+FIRST_ROWS(100) --+gather_plan_statistics --a3
rn, trowid
from (select rownum rn, trowid
from (select t.rowid trowid
from ref_terminals t,
     ref_lookup_codes l
where 1 = 1
and l.lookup_name = 'TERM_STATES'
and l.num_value = t.current_state
order by l.lookup_value
)
where rownum <= &l_to_line)
where rn >= &l_from_line

для 401-500

SQL_ID  6f3gqs718j99b, child number 0
-------------------------------------
select --+FIRST_ROWS(100) --+gather_plan_statistics --a3 rn, trowid 
from (select rownum rn, trowid from (select t.rowid trowid from 
ref_terminals t,      ref_lookup_codes l where 1 = 1 and l.lookup_name 
= 'TERM_STATES' and l.num_value = t.current_state order by 
l.lookup_value ) where rownum <= 500) where rn >= 401

Plan hash value: 1167343809

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |      3 |        |       |       | 20695 (100)|          |    300 |00:00:04.74 |   17421 |       |       |          |
|*  1 |  VIEW                            |                      |      3 |    500 | 12500 |       | 20695   (1)| 00:04:09 |    300 |00:00:04.74 |   17421 |       |       |          |
|*  2 |   COUNT STOPKEY                  |                      |      3 |        |       |       |            |          |   1500 |00:00:04.74 |   17421 |       |       |          |
|   3 |    VIEW                          |                      |      3 |   1403K|    16M|       | 20695   (1)| 00:04:09 |   1500 |00:00:04.74 |   17421 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY        |                      |      3 |   1403K|    74M|    86M| 20695   (1)| 00:04:09 |   1500 |00:00:04.74 |   17421 | 48128 | 48128 |     2/0/0|
|*  5 |      HASH JOIN                   |                      |      3 |   1403K|    74M|       |  1573   (1)| 00:00:19 |   5952K|00:00:03.45 |   17421 |  1483K|  1483K|     3/0/0|
|*  6 |       TABLE ACCESS BY INDEX ROWID| REF_LOOKUP_CODES     |      3 |      7 |   280 |       |     3   (0)| 00:00:01 |     24 |00:00:00.01 |       9 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | REF_LOOKUP_CODES_UK1 |      3 |      8 |       |       |     2   (0)| 00:00:01 |     24 |00:00:00.01 |       6 |       |       |          |
|   8 |       INDEX FAST FULL SCAN       | REF_TERMINALS_STATE  |      3 |   1984K|    30M|       |  1565   (1)| 00:00:19 |   5952K|00:00:00.96 |   17412 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$3 / from$_subquery$_002@SEL$2
   4 - SEL$3
   6 - SEL$3 / L@SEL$3
   7 - SEL$3 / L@SEL$3
   8 - SEL$3 / T@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      FIRST_ROWS(100)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "L"@"SEL$3" ("REF_LOOKUP_CODES"."LOOKUP_NAME" "REF_LOOKUP_CODES"."LOOKUP_CODE"))
      INDEX_FFS(@"SEL$3" "T"@"SEL$3" ("REF_TERMINALS"."CURRENT_STATE"))
      LEADING(@"SEL$3" "L"@"SEL$3" "T"@"SEL$3")
      USE_HASH(@"SEL$3" "T"@"SEL$3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=401)
   2 - filter(ROWNUM<=500)
   4 - filter(ROWNUM<=500)
   5 - access("L"."NUM_VALUE"="T"."CURRENT_STATE")
   6 - filter("L"."NUM_VALUE" IS NOT NULL)
   7 - access("L"."LOOKUP_NAME"='TERM_STATES')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "RN"[NUMBER,22], "TROWID"[ROWID,10]
   2 - "TROWID"[ROWID,10], ROWNUM[4]
   3 - "TROWID"[ROWID,10]
   4 - (#keys=1) "L"."LOOKUP_VALUE"[VARCHAR2,100], "T".ROWID[ROWID,10]
   5 - (#keys=1) "L"."LOOKUP_VALUE"[VARCHAR2,100], "T".ROWID[ROWID,10]
   6 - "L"."LOOKUP_VALUE"[VARCHAR2,100], "L"."NUM_VALUE"[NUMBER,22]
   7 - "L".ROWID[ROWID,10]
   8 - "T".ROWID[ROWID,10], "T"."CURRENT_STATE"[NUMBER,22]

401         AAAaP9AAFAACE3VAAi
402         AAAaP9AAFAACE3OAAU
403         AAAaP9AAFAACE3LAAc
…


Для 101-200

SQL_ID  bq426nmxty62c, child number 0
-------------------------------------
select --+FIRST_ROWS(100) --+gather_plan_statistics --a3 rn, trowid 
from (select rownum rn, trowid from (select t.rowid trowid from 
ref_terminals t,      ref_lookup_codes l where 1 = 1 and l.lookup_name 
= 'TERM_STATES' and l.num_value = t.current_state order by 
l.lookup_value ) where rownum <= 200) where rn >= 101

Plan hash value: 1167343809

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |      1 |        |       |       | 20695 (100)|          |    100 |00:00:01.65 |    5807 |       |       |          |
|*  1 |  VIEW                            |                      |      1 |    200 |  5000 |       | 20695   (1)| 00:04:09 |    100 |00:00:01.65 |    5807 |       |       |          |
|*  2 |   COUNT STOPKEY                  |                      |      1 |        |       |       |            |          |    200 |00:00:01.65 |    5807 |       |       |          |
|   3 |    VIEW                          |                      |      1 |   1403K|    16M|       | 20695   (1)| 00:04:09 |    200 |00:00:01.65 |    5807 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY        |                      |      1 |   1403K|    74M|    86M| 20695   (1)| 00:04:09 |    200 |00:00:01.65 |    5807 | 13312 | 13312 |     1/0/0|
|*  5 |      HASH JOIN                   |                      |      1 |   1403K|    74M|       |  1573   (1)| 00:00:19 |   1984K|00:00:01.20 |    5807 |  1483K|  1483K|     1/0/0|
|*  6 |       TABLE ACCESS BY INDEX ROWID| REF_LOOKUP_CODES     |      1 |      7 |   280 |       |     3   (0)| 00:00:01 |      8 |00:00:00.01 |       3 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | REF_LOOKUP_CODES_UK1 |      1 |      8 |       |       |     2   (0)| 00:00:01 |      8 |00:00:00.01 |       2 |       |       |          |
|   8 |       INDEX FAST FULL SCAN       | REF_TERMINALS_STATE  |      1 |   1984K|    30M|       |  1565   (1)| 00:00:19 |   1984K|00:00:00.33 |    5804 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$3 / from$_subquery$_002@SEL$2
   4 - SEL$3
   6 - SEL$3 / L@SEL$3
   7 - SEL$3 / L@SEL$3
   8 - SEL$3 / T@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      FIRST_ROWS(100)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "L"@"SEL$3" ("REF_LOOKUP_CODES"."LOOKUP_NAME" "REF_LOOKUP_CODES"."LOOKUP_CODE"))
      INDEX_FFS(@"SEL$3" "T"@"SEL$3" ("REF_TERMINALS"."CURRENT_STATE"))
      LEADING(@"SEL$3" "L"@"SEL$3" "T"@"SEL$3")
      USE_HASH(@"SEL$3" "T"@"SEL$3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=101)
   2 - filter(ROWNUM<=200)
   4 - filter(ROWNUM<=200)
   5 - access("L"."NUM_VALUE"="T"."CURRENT_STATE")
   6 - filter("L"."NUM_VALUE" IS NOT NULL)
   7 - access("L"."LOOKUP_NAME"='TERM_STATES')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "RN"[NUMBER,22], "TROWID"[ROWID,10]
   2 - "TROWID"[ROWID,10], ROWNUM[4]
   3 - "TROWID"[ROWID,10]
   4 - (#keys=1) "L"."LOOKUP_VALUE"[VARCHAR2,100], "T".ROWID[ROWID,10]
   5 - (#keys=1) "L"."LOOKUP_VALUE"[VARCHAR2,100], "T".ROWID[ROWID,10]
   6 - "L"."LOOKUP_VALUE"[VARCHAR2,100], "L"."NUM_VALUE"[NUMBER,22]
   7 - "L".ROWID[ROWID,10]
   8 - "T".ROWID[ROWID,10], "T"."CURRENT_STATE"[NUMBER,22]

101         AAAaP9AAFAACE3VAAi
102         AAAaP9AAFAACE3OAAU
103         AAAaP9AAFAACE3LAAc
…


SQL_ID  0st7u3jxs8tq4, child number 0
-------------------------------------
select --+FIRST_ROWS(100) --+gather_plan_statistics --a4
rn, trowid 
from (select rownum rn, trowid
from (select t.rowid trowid
from ref_terminals t
where 1 = 1
order by t.current_state) 
where rownum <= 200)
where rn >= 101

Plan hash value: 1181353452

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |       |     3 (100)|          |    100 |00:00:00.01 |       3 |
|*  1 |  VIEW              |                     |      1 |    100 |  2500 |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       3 |
|*  2 |   COUNT STOPKEY    |                     |      1 |        |       |            |          |    200 |00:00:00.01 |       3 |
|   3 |    VIEW            |                     |      1 |    100 |  1200 |     3   (0)| 00:00:01 |    200 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN| REF_TERMINALS_STATE |      1 |   1984K|    30M|     3   (0)| 00:00:01 |    200 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$3 / from$_subquery$_002@SEL$2
   4 - SEL$3 / T@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      FIRST_ROWS(100)
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      INDEX(@"SEL$3" "T"@"SEL$3" ("REF_TERMINALS"."CURRENT_STATE"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=101)
   2 - filter(ROWNUM<=200)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "RN"[NUMBER,22], "TROWID"[ROWID,10]
   2 - "TROWID"[ROWID,10], ROWNUM[4]
   3 - "TROWID"[ROWID,10]
   4 - "T".ROWID[ROWID,10]
15 фев 17, 15:33    [20214495]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26877
gandalf-the-grey
причем сортировка может быть по полю, содержащее одинаковые значения для всего возвращенного набора?
Детерминировать сортировку.
15 фев 17, 15:51    [20214572]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
gandalf-the-grey
Member

Откуда: НН
Сообщений: 179
Elic
gandalf-the-grey
причем сортировка может быть по полю, содержащее одинаковые значения для всего возвращенного набора?
Детерминировать сортировку.

В каком смысле? Если добавить условия с сортировку, то вариант, я так понимаю, по каждому полю, по которому возможна сортировка, создавать индекс, дополнительно "детерминированный" чем-нибудь типа PK.
Только, мне кажется, это не решит проблему, когда таблица сортируется по значению внешнего лукапа, как в моем примере.
Это в любом случае приведет либо к TABLE ACCESS FULL, либо к INDEX FAST FULL SCAN основной таблицы-источника с последующей сортировкой. Это решит задачу отображения уникальных строк на разных страницах, но нивелирует то, что нужно для пейджинга: скорость при COUNT STOPKEY на INDEX FULL SCAN

А с сортировкой по собственным полям, имеющим NOT NULL или индекс с nvl() проблем и так не будет.

М.б. индексы на соединения? Или я ещё что-то упускаю?

Спасибо.
15 фев 17, 19:58    [20215367]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по неселективному индексу  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26877
gandalf-the-grey
нивелирует то, что нужно для пейджинга: скорость
До сих пор ты жаловался только на недетерминизм. Уж выбирай: шашечки или ехать.
gandalf-the-grey
по каждому полю, по которому возможна сортировка, создавать индекс
Идиотизм. Табличка только ради остраничивания? - Так не бывает.
16 фев 17, 07:52    [20216245]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить