Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Сразу прошу прощения, возможно такая тема поднималась, но форум Oracle тут выдает по запросу ORDER BY почти 11 тыс. записей - просматривать не вариант.

Суть задачи.

Есть большая и тяжелая таблица (с блобами).
Надо сделать отсортированную выборку из нее всех записей.
Поле, по которому идет сортировка - с уникальным индексом.
Собственно, всё.

Суть проблемы.

Запрос такого типа выполняется чрезвычайно долго (Запрос №1):
SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID";

(для моих данных более 200 секунд!)

При этом, тот же запрос, но без ORDER BY выполняется молниеносно (Запрос №2):
SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE";

(для моих данных около 0.1 сек)

Пробую извращаться. Вот такой запрос дает нужный результат и выполняется в сотню раз быстрее чем первый (Запрос №3):
SELECT t2.* FROM 
(SELECT "OBJECTID" FROM "MYTABLE" ORDER BY "OBJECTID") t1
LEFT OUTER JOIN 
(SELECT "OBJECTID",  "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE") t2
ON t1."OBJECTID" = t2."OBJECTID";

(для моих данных около 1,5 сек)

Но это ж реальное извращение!


Смотрю план выполнения. Я не спец в Oracle, но, походу, при выполнении запроса с сортировкой Oralce ворочает записи целиком, выстраивая нужный порядок???

План без сортировки (Запрос №2):
+
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
>>Query Run In:Query Result 1
Plan hash value: 688422923

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34642 | 11M| 1061 (1)| 00:00:13 |
| 1 | TABLE ACCESS FULL| MYTABLE | 34642 | 11M| 1061 (1)| 00:00:13 |
---------------------------------------------------------------------------------

Statistics
-----------------------------------------------------------
12 SQL*Net roundtrips to/from client
262 bytes received via SQL*Net from client
22163 bytes sent via SQL*Net to client
1 calls to get snapshot scn: kcmgss
1 execute count
13 non-idle wait count
1 opened cursors cumulative
1 opened cursors current
1 parse count (total)
1 sorts (memory)
588 sorts (rows)
12 user calls




План с сортировкой (Запрос №1):
+
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
>>Query Run In:Query Result 2
Plan hash value: 4080983627

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34642 | 11M| | 3710 (1)| 00:00:45 |
| 1 | SORT ORDER BY | | 34642 | 11M| 14M| 3710 (1)| 00:00:45 |
| 2 | TABLE ACCESS FULL| MYTABLE | 34642 | 11M| | 1061 (1)| 00:00:13 |
------------------------------------------------------------------------------------------

Statistics
-----------------------------------------------------------
6131 CPU used by this session
6131 CPU used when call started
20400 DB time
14 SQL*Net roundtrips to/from client
69284 SQL*Net roundtrips to/from dblink
12126 application wait time
4024 buffer is not pinned count
974 bytes received via SQL*Net from client
13441096 bytes received via SQL*Net from dblink
82990 bytes sent via SQL*Net to client
150100112 bytes sent via SQL*Net to dblink
658061 calls to get snapshot scn: kcmgss
1 calls to kcmgas
11 calls to kcmgcs
27148288 cell physical IO interconnect bytes
480686 consistent changes
128640 consistent gets
94 consistent gets - examination
128640 consistent gets from cache
126412 consistent gets from cache (fastpath)
480686 db block changes
1964336 db block gets
1964336 db block gets from cache
22017 dirty buffers inspected
6 enqueue releases
6 enqueue requests
2 execute count
17825432 file io wait time
57697 free buffer inspected
158389 free buffer requested
6211 hot buffers moved to head of LRU
193 index crx upgrade (positioned)
193 index scans kdiixs1
39519 lob reads
138568 lob writes
138568 lob writes unaligned
77 messages sent
3836 no work - consistent read gets
279944 non-idle wait count
13909 non-idle wait time
2 opened cursors cumulative
2 opened cursors current
2 parse count (total)
1941 physical read IO requests
27148288 physical read bytes
1941 physical read total IO requests
27148288 physical read total bytes
2 physical read total multi block requests
3314 physical reads
3314 physical reads cache
1373 physical reads cache prefetch
1 pinned cursors current
277138 recursive calls
1212 recursive cpu usage
94 rows fetched via callback
2092976 session logical reads
193 shared hash latch upgrades - no wait
2 sorts (memory)
35230 sorts (rows)
94 table fetch by rowid
3836 table scan blocks gotten
34642 table scan rows gotten
1 table scans (short tables)
2097152 temp space allocated (bytes)
1783 user I/O wait time
14 user calls
1 write clones created in foreground




План с запроса с моими извращениями (Запрос №3):
+
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
>>Query Run In:Query Result 3
Plan hash value: 2651211032

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34642 | 11M| | 3732 (1)| 00:00:45 |
| 1 | MERGE JOIN OUTER | | 34642 | 11M| | 3732 (1)| 00:00:45 |
| 2 | SORT JOIN | | 34642 | 169K| | 22 (14)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| R99_SDE_ROWID_UK | 34642 | 169K| | 19 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 34642 | 11M| 28M| 3710 (1)| 00:00:45 |
| 5 | TABLE ACCESS FULL | MYTABLE | 34642 | 11M| | 1061 (1)| 00:00:13 |
---------------------------------------------------------------------------------------------------

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

4 - access("OBJECTID"="OBJECTID"(+))
filter("OBJECTID"="OBJECTID"(+))

Statistics
-----------------------------------------------------------
12 SQL*Net roundtrips to/from client
262 bytes received via SQL*Net from client
22163 bytes sent via SQL*Net to client
1 calls to get snapshot scn: kcmgss
1 execute count
13 non-idle wait count
1 opened cursors cumulative
1 opened cursors current
1 parse count (total)
1 sorts (memory)
588 sorts (rows)
12 user calls




Доп.информация.

Пересборка индекса не помогает.

Сам индекс по полю "OBJECTID" выглядит так:
CREATE UNIQUE INDEX "SDE"."R99_SDE_ROWID_UK" ON "SDE"."MYTABLE" ("OBJECTID") 
  PCTFREE 0 INITRANS 4 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MYTABLESPACE " ;




Вопрос.

Где оптимизатор запросов?
Можно ли как-то заставить Oracle выполнять запрос по уму - сначала сортировку, а уже затем выборку больших данных?
12 окт 17, 16:52    [20864915]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
DВА
Member

Откуда:
Сообщений: 5439
за 0.1 секунду вам прилетают вот так вот все записи с блобами?
ню-ню ))
12 окт 17, 17:00    [20864952]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
j2k
Member

Откуда: Новосибирск
Сообщений: 550
MustDie, вы все данные отфетчили во втором запросе?
12 окт 17, 17:02    [20864959]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Я открываю данные из своего приложения, через OCI.

Указанное время задержки - это OCIStmtFetch2 на первую запись, то есть, собственно, открытие набора.

Речи про получение результатов выборки на клиента тут нет. Это уже будут вопросы не к оптимизатору запросов.


Повторил те же запросы в SQL Developer. Он фетчит, как Вы говорите, по 50 записей.
Как заставить SQL Developer отфетчить сразу всю выборку, я не знаю.
Хотя, есть ли в этом смысл? Видно же, что и все три запроса на тех же 50 записях дают разный результат и план.
12 окт 17, 17:50    [20865241]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2787
MustDie,

імхо
третий запрос не гарантирует сортировку по OBJECTID

ps
какое время выполнения запроса если не указывать лоб поля ?

....
stax
12 окт 17, 18:03    [20865303]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Стоп кей
Guest
MustDie
по 50 записей.
Ну так и сравнивай запросы с fetch first/rownum
12 окт 17, 18:18    [20865340]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Stax
MustDie,
імхо
третий запрос не гарантирует сортировку по OBJECTID


Я тоже придерживаюсь такого же мнения. Просто попробовал смоделировать отдельно сортировку и отдельно выборку по сортированному списку - ради эксперимента.


Stax
MustDie,
какое время выполнения запроса если не указывать лоб поля ?

маленькое, очень близкое к выборке только одного OBJECTID сортированного по OBJECTID.
12 окт 17, 18:32    [20865366]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Стоп кей
MustDie
по 50 записей.
Ну так и сравнивай запросы с fetch first/rownum


Спасибо за интересную мысль!

У меня 11g, поэтому fetch first не катит.
Запросы с ROWNUM < 1 летают.

Попробовал так:
SELECT t1.* FROM 
(
SELECT row_number() over (order by "OBJECTID") as rn, "OBJECTID", "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID"
) t1
WHERE t1.rn < 100 

- летает...

+
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
>>Query Run In:Query Result 5
Plan hash value: 1236896756

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34642 | 80M| 1061 (1)| 00:00:13 |
|* 1 | VIEW | | 34642 | 80M| 1061 (1)| 00:00:13 |
|* 2 | WINDOW SORT PUSHED RANK| | 34642 | 11M| 1061 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL | DUONGBINHDO | 34642 | 11M| 1061 (1)| 00:00:13 |
----------------------------------------------------------------------------------------

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

1 - filter("T2"."RN"<100)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECTID")<100)

Statistics
-----------------------------------------------------------
17 CPU used by this session
17 CPU used when call started
18 DB time
14 SQL*Net roundtrips to/from client
104 SQL*Net roundtrips to/from dblink
7 application wait time
3836 buffer is not pinned count
1085 bytes received via SQL*Net from client
20176 bytes received via SQL*Net from dblink
83239 bytes sent via SQL*Net to client
218427 bytes sent via SQL*Net to dblink
1040 calls to get snapshot scn: kcmgss
11 calls to kcmgcs
572 consistent changes
4053 consistent gets
4053 consistent gets from cache
4053 consistent gets from cache (fastpath)
572 db block changes
2236 db block gets
2236 db block gets from cache
2 execute count
156 free buffer requested
102 lob reads
208 lob writes
208 lob writes unaligned
3836 no work - consistent read gets
438 non-idle wait count
8 non-idle wait time
2 opened cursors cumulative
2 opened cursors current
2 parse count (total)
1 pinned cursors current
416 recursive calls
6289 session logical reads
2 sorts (memory)
35230 sorts (rows)
3836 table scan blocks gotten
34642 table scan rows gotten
1 table scans (short tables)
14 user calls



А вот такая выборка всего 1 записи
SELECT t1.* FROM 
(
SELECT row_number() over (order by "OBJECTID") as rn, "OBJECTID", "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID"
) t1
WHERE t1.rn > 10000 and t1.rn < 10002

- уходит в ступор на несколько минут!

+
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
>>Query Run In:Query Result 6
Plan hash value: 1236896756

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34642 | 80M| 1061 (1)| 00:00:13 |
|* 1 | VIEW | | 34642 | 80M| 1061 (1)| 00:00:13 |
|* 2 | WINDOW SORT PUSHED RANK| | 34642 | 11M| 1061 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL | DUONGBINHDO | 34642 | 11M| 1061 (1)| 00:00:13 |
----------------------------------------------------------------------------------------

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

1 - filter("T2"."RN">10000 AND "T2"."RN"<10002)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECTID")<10001)

Statistics
-----------------------------------------------------------
1016 CPU used by this session
1016 CPU used when call started
6415 DB time
14 SQL*Net roundtrips to/from client
20004 SQL*Net roundtrips to/from dblink
5615 application wait time
4036 buffer is not pinned count
1126 bytes received via SQL*Net from client
3880776 bytes received via SQL*Net from dblink
23003 bytes sent via SQL*Net to client
44111351 bytes sent via SQL*Net to dblink
200052 calls to get snapshot scn: kcmgss
11 calls to kcmgcs
1482752 cell physical IO interconnect bytes
205591 consistent changes
42399 consistent gets
240 consistent gets - examination
42399 consistent gets from cache
41910 consistent gets from cache (fastpath)
205591 db block changes
732940 db block gets
732940 db block gets from cache
1 enqueue releases
1 enqueue requests
2 execute count
450844 file io wait time
50698 free buffer requested
68 index crx upgrade (positioned)
70 index fetch by key
68 index scans kdiixs1
11715 lob reads
40008 lob writes
40008 lob writes unaligned
3836 no work - consistent read gets
80393 non-idle wait count
5660 non-idle wait time
2 opened cursors cumulative
2 opened cursors current
1 parse count (hard)
2 parse count (total)
3 parse time elapsed
181 physical read IO requests
1482752 physical read bytes
181 physical read total IO requests
1482752 physical read total bytes
181 physical reads
181 physical reads cache
80017 recursive calls
217 recursive cpu usage
100 rows fetched via callback
1 session cursor cache hits
775339 session logical reads
68 shared hash latch upgrades - no wait
2 sorts (memory)
35230 sorts (rows)
100 table fetch by rowid
3836 table scan blocks gotten
34642 table scan rows gotten
1 table scans (short tables)
45 user I/O wait time
14 user calls




Посмотрел DDL таблицы.
  CREATE TABLE "SDE"."MYTABLE" 
   (	"OBJECTID" NUMBER(*,0) NOT NULL ENABLE, 
	"FIELD1" NVARCHAR2(16), 
	"FIELD2" DATE, 
	"FIELD3" "SDE"."ST_GEOMETRY" 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 0 PCTUSED 40 INITRANS 4 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MYTABLESPACE " 
 LOB ("SHAPE"."POINTS") STORE AS BASICFILE (
  TABLESPACE "MYTABLESPACE" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 1
  CACHE 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;




Получается, что сегментированное хранение BLOB влияет на скорость открытия SQL-запроса если требуется сортировка выборки?! Повторю - на скорость открытия, на не выборку данных!
Выборка - не так критично.
Но ждать несколько минут пока что-то там на сервере варится - это жесть...
12 окт 17, 19:16    [20865454]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
DВА
Member

Откуда:
Сообщений: 5439
MustDie,

на скорость выполнения запроса если требуется сортировка влияет сортировка.
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?
12 окт 17, 23:19    [20865896]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
MustDie,

просто добавь в первый свой запрос хинт first_rows(50)...
12 окт 17, 23:41    [20865917]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
DВА
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?

При соблюдении некоторых широко известных в узком кругу условий можно получить упорядоченный набор без непосредственно сортировки.
Но перед тем, как переходить к подобным "тонкостям", желательно научить ТС хотя бы отличать время реакции (возвращения первой строки) от времени выполнения (возвращения полного набора) запроса - не говорю уже про необходимость формирования хотя бы самого базового представления о том, что физически происходит на фазах execute и fetch курсора в зависимости от плана запроса и только затем рассказывать о методах доступа к данным, влияния на вроде бы требуемый ТС эффект DDL таблицы, NLS-окружения и корректно сформулированного запроса.
12 окт 17, 23:50    [20865931]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
MustDie
Суть задачи.

Есть большая и тяжелая таблица (с блобами).
Надо сделать отсортированную выборку из нее всех записей.
Поле, по которому идет сортировка - с уникальным индексом.
Собственно, всё.


А собственно что еще нужно? Первым запросом отберите только совё уникальное поле и отсортеруй его. Вторым доставайте все записи из таблицы по Уникальному полу или ROWID ... по 1-му или по 100 тут уже Вам решать. У Вас уже будет набор отсортированных значений.
13 окт 17, 08:15    [20866082]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2787
MaximaXXL,
странно что оракля берется за сортировку,
ведь есть индекс по сортируемому полю и достаточно INDEX FULL SCAN

поле не not null, добавить левое условие OBJECTID>-1 (OBJECTID is not null)
по идее сортировка должна уйти из плана

......
stax
13 окт 17, 09:10    [20866198]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Stax,

Я так думаю, если бы добавить ограничение поиска (возможно даже тривиального "OBJECTID" > -1), или натравить на индекс ...
А так, выборка из таблицы без условий и последующая сортировка ...

MustDie,
В этих выборках Вам не нужна доп. сортировка
SELECT t1.* FROM 
(
SELECT row_number() over (order by "OBJECTID") as rn, "OBJECTID", "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID"
) t1
WHERE t1.rn > 10000 and t1.rn < 10002
13 окт 17, 09:32    [20866234]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
DВА
MustDie,
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?


представляю примерно так, как я сделал в запросе под кодовым названием "извращение".
выполняется сортировка по указанному полю (кроме того являющемуся индексированным), это происходит при первом фетче одной записи. оракл при этом зачем-то ворочает туда-сюда тяжелые записи с блобами.
в результате OCIStmtFetch2 на первую запись висит несколько минут, не возвращая управление.


MaximaXXL
А собственно что еще нужно? Первым запросом отберите только совё уникальное поле и отсортеруй его. Вторым доставайте все записи из таблицы по Уникальному полу или ROWID ... по 1-му или по 100 тут уже Вам решать. У Вас уже будет набор отсортированных значений.


Как вариант. Уже думал об этом.


andrey_anonymous
DВА
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?

При соблюдении некоторых широко известных в узком кругу условий можно получить упорядоченный набор без непосредственно сортировки.
Но перед тем, как переходить к подобным "тонкостям", желательно научить ТС хотя бы отличать время реакции (возвращения первой строки) от времени выполнения (возвращения полного набора) запроса - не говорю уже про необходимость формирования хотя бы самого базового представления о том, что физически происходит на фазах execute и fetch курсора в зависимости от плана запроса и только затем рассказывать о методах доступа к данным, влияния на вроде бы требуемый ТС эффект DDL таблицы, NLS-окружения и корректно сформулированного запроса.


Как Вы любите говорить загадками... Речь именно про "время реакции". Может не так выразился, но говоря о OCIStmtFetch2 на первую запись, я имел в виду именно это. Как в данном конкретном случае влияет NLS-окружение, честно говоря, не понял.
В Oracle не силен, сразу в этом признался. Избалован Майкрософтом...


Stax
MaximaXXL,
странно что оракля берется за сортировку,
ведь есть индекс по сортируемому полю и достаточно INDEX FULL SCAN
поле не not null, добавить левое условие OBJECTID>-1 (OBJECTID is not null)
по идее сортировка должна уйти из плана

не спасло...
В плане все равно SORT ORDER BY + TABLE ACCESS FULL.
Что-то не так с индексом?

MaximaXXL
MustDie,
В этих выборках Вам не нужна доп. сортировка

Да, спасибо. Я знаю. Это все проклятый копи-паст...
13 окт 17, 15:06    [20867889]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2787
MustDie
Stax
MaximaXXL,
странно что оракля берется за сортировку,
ведь есть индекс по сортируемому полю и достаточно INDEX FULL SCAN
поле не not null, добавить левое условие OBJECTID>-1 (OBJECTID is not null)
по идее сортировка должна уйти из плана

не спасло...
В плане все равно SORT ORDER BY + TABLE ACCESS FULL.
Что-то не так с индексом?


а если принудительно поставить хинт /*+ index(t i) */

если есть подходящий индекс и услвие выборки с "not null", то не должен бы сортировать

......
stax
13 окт 17, 16:19    [20868153]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
DВА
Member

Откуда:
Сообщений: 5439
MustDie
DВА
MustDie,
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?


представляю примерно так, как я сделал в запросе под кодовым названием "извращение".
выполняется сортировка по указанному полю (кроме того являющемуся индексированным), это происходит при первом фетче одной записи. оракл при этом зачем-то ворочает туда-сюда тяжелые записи с блобами.
в результате OCIStmtFetch2 на первую запись висит несколько минут, не возвращая управление.



В своем извращении вы позволили ораклу вместо таблицы отсортировать индекс и таким образом сократить время выполнения.
Еще одним извращением - указанием хинта index или first_rows(1), вы можете заставить оракл использовать вместо фулскана доступ по уже отсортированному индексу, и тогда тоже первую запись вы получите по моментально
13 окт 17, 16:48    [20868237]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
DВА
Member

Откуда:
Сообщений: 5439
Stax
MustDie
пропущено...

не спасло...
В плане все равно SORT ORDER BY + TABLE ACCESS FULL.
Что-то не так с индексом?


а если принудительно поставить хинт /*+ index(t i) */

если есть подходящий индекс и услвие выборки с "not null", то не должен бы сортировать

......
stax


ну так оракл откуда знает, что автору нужна одна строка?
он считает, что фетчиться будут все записи, и их легче профулсканить и отсортировать, чем читать сначала индекс, а потом еще и таблицу.
Указывать нада явно чего хотется - все или первую строку
13 окт 17, 16:50    [20868247]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
MustDie,
не зная броду Вы батенька полезли копать одну из самых сложных тем в оракле... Пейджинг требует времени и понимания механизмов.
хотя бы первые три ссылки для начала осильте
13 окт 17, 17:25    [20868359]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
DВА
ну так оракл откуда знает, что автору нужна одна строка?
он считает, что фетчиться будут все записи, и их легче профулсканить и отсортировать, чем читать сначала индекс, а потом еще и таблицу.
Указывать нада явно чего хотется - все или первую строку


Фетчится будут все записи - с первой по последнюю (и желательно, чтобы это было в порядке сортировки).
Совсем, думаю, не легче профулсканить и отсортировать таблицу с блоб-полями, чем читать сначала индекс, а потом еще и таблицу.

На счёт использования хинтов - спасибо за подсказку. Как-то вылетела из готовы эта возможность. Попробую. Правда, никогда раньше из OCI-приложений такого не делал. Да и вообще считаю что у штатного ораклового оптимизатора эвристики должны быть умнее меня и вмешиваться в план выполнения - это моветон.
Кроме того, для этого мне придется в своем OCI-приложении еще "поприседать" - полазить по системным таблицам, вытащить имена индексов для таблиц.

Ну, во всяком случае, применение хинта в тесте, выполняемом в SQL Developere, помогло.
План для "OBJECTID">-1 изменился на INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID.
Задержки на открытие минимальны.
:) Если поставить казалось бы абсурдное "OBJECTID" IS NOT NULL, то получается INDEX FULL SCAN.
13 окт 17, 18:01    [20868417]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Vint
MustDie,
не зная броду Вы батенька полезли копать одну из самых сложных тем в оракле... Пейджинг требует времени и понимания механизмов.
хотя бы первые три ссылки для начала осильте


Нет, я как та украинская девочка - ничего не хочу, хочу кружевные трусики и в евросоюз :)

Не хочу никаких пейджингов. Хочу быстро получить отсортированную выборку по проиндексированному полю. Не хочу ни ROW_NUMBER, ни RANK, ни каких-то других сложностей.

Поэтому не совсем понял, как изучение информации по ссылкам мне поможет.
13 окт 17, 18:14    [20868443]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
MustDie
Member

Откуда: Подмосковье
Сообщений: 229
Все оказалось гораздо банальнее.
Я в силу своей неопытности в Oracle не с того начал.
Спасибо Тому Кайту.
Его "случай 6" из главы "Почему мой индекс не используется":
analyze table "MYTABLE" compute statistics;
analyze table "MYTABLE" compute statistics for all indexes;


привело к желаемому INDEX FULL SCAN
для
SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID";


Большое спасибо всем, кто поучаствовал в дискуссии!
Благодаря Вам я изучил много нового и полезного.
13 окт 17, 18:31    [20868473]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Elic
Member

Откуда:
Сообщений: 29979
MustDie
Спасибо Тому Кайту.
analyze table
Врёшь. Он не мог такой херни насоветовать.
13 окт 17, 21:15    [20868676]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
DВА
Member

Откуда:
Сообщений: 5439
Elic
MustDie
Спасибо Тому Кайту.
analyze table
Врёшь. Он не мог такой херни насоветовать.

ну для 7-ой версии вполне мог ))
13 окт 17, 21:19    [20868682]     Ответить | Цитировать Сообщить модератору
 Re: ORDER BY приводит в ступор (как заставить Oracle работать с умом)  [new]
Elic
Member

Откуда:
Сообщений: 29979
DВА
ну для 7-ой версии вполне мог ))
Как давно эта макулатура была выпущена...
13 окт 17, 22:13    [20868791]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить