ORDER BY приводит в ступор (как заставить Oracle работать с умом) / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / ORDER BY приводит в ступор (как заставить Oracle работать с умом)

25 сообщений из 29, страница 1 из 2

все

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535521

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

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

Суть задачи.

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

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

Запрос такого типа выполняется чрезвычайно долго (Запрос №1):

Код: plsql

SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID";

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

При этом, тот же запрос, но без ORDER BY выполняется молниеносно (Запрос №2):

Код: plsql

SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE";

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

Пробую извращаться. Вот такой запрос дает нужный результат и выполняется в сотню раз быстрее чем первый (Запрос №3):

Код: plsql

1.
2.
3.
4.
5.

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" выглядит так:

Код: plsql

1.
2.
3.
4.
5.

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 выполнять запрос по уму - сначала сортировку, а уже затем выборку больших данных?

...

Рейтинг:

0 / 0

12.10.2017, 16:52

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535531

DВА

Участник

Сообщения: 4 743

Рейтинг: 0 / 0

за 0.1 секунду вам прилетают вот так вот все записи с блобами?
ню-ню ))

...

Рейтинг:

0 / 0

12.10.2017, 17:00

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535533

j2k

Участник

Откуда: Новосибирск

Сообщения: 524

Рейтинг: 0 / 0

MustDie, вы все данные отфетчили во втором запросе?

...

Рейтинг:

0 / 0

12.10.2017, 17:02

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535583

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

Я открываю данные из своего приложения, через OCI.

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

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

Повторил те же запросы в SQL Developer. Он фетчит, как Вы говорите, по 50 записей.
Как заставить SQL Developer отфетчить сразу всю выборку, я не знаю.
Хотя, есть ли в этом смысл? Видно же, что и все три запроса на тех же 50 записях дают разный результат и план.

...

Рейтинг:

0 / 0

12.10.2017, 17:50

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535589

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

MustDie,

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

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

....
stax

...

Рейтинг:

0 / 0

12.10.2017, 18:03

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535600

Стоп кей

Гость

MustDieпо 50 записей.Ну так и сравнивай запросы с fetch first/rownum

...

Рейтинг:

0 / 0

12.10.2017, 18:18

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535607

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

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

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

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

маленькое, очень близкое к выборке только одного OBJECTID сортированного по OBJECTID.

...

Рейтинг:

0 / 0

12.10.2017, 18:32

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535626

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

Стоп кейMustDieпо 50 записей.Ну так и сравнивай запросы с fetch first/rownum

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

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

Попробовал так:

Код: plsql

1.
2.
3.
4.
5.

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 записи

Код: plsql

1.
2.
3.
4.
5.

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 таблицы.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

  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-запроса если требуется сортировка выборки?! Повторю - на скорость открытия, на не выборку данных!
Выборка - не так критично.
Но ждать несколько минут пока что-то там на сервере варится - это жесть...

...

Рейтинг:

0 / 0

12.10.2017, 19:16

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535732

DВА

Участник

Сообщения: 4 743

Рейтинг: 0 / 0

MustDie,

на скорость выполнения запроса если требуется сортировка влияет сортировка.
как вы себе представляете открытие запроса с сортировкой без предварительной сортировки, если изначально данные гарантировано не отсортированы ?

...

Рейтинг:

0 / 0

12.10.2017, 23:19

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535734

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

MustDie,

просто добавь в первый свой запрос хинт first_rows(50)...

...

Рейтинг:

0 / 0

12.10.2017, 23:41

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535738

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

12.10.2017, 23:50

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535773

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

MustDieСуть задачи.

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

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

...

Рейтинг:

0 / 0

13.10.2017, 08:15

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535798

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

MaximaXXL,
странно что оракля берется за сортировку,
ведь есть индекс по сортируемому полю и достаточно INDEX FULL SCAN

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

......
stax

...

Рейтинг:

0 / 0

13.10.2017, 09:10

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39535817

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

Stax,

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

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

Код: plsql

1.
2.
3.
4.
5.

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

...

Рейтинг:

0 / 0

13.10.2017, 09:32

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536046

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

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

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

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

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

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

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

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

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

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

Да, спасибо. Я знаю. Это все проклятый копи-паст...

...

Рейтинг:

0 / 0

13.10.2017, 15:06

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536101

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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

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

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

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

......
stax

...

Рейтинг:

0 / 0

13.10.2017, 16:19

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536118

DВА

Участник

Сообщения: 4 743

Рейтинг: 0 / 0

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

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

В своем извращении вы позволили ораклу вместо таблицы отсортировать индекс и таким образом сократить время выполнения.
Еще одним извращением - указанием хинта index или first_rows(1), вы можете заставить оракл использовать вместо фулскана доступ по уже отсортированному индексу, и тогда тоже первую запись вы получите по моментально

...

Рейтинг:

0 / 0

13.10.2017, 16:48

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536121

DВА

Участник

Сообщения: 4 743

Рейтинг: 0 / 0

StaxMustDieпропущено...

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

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

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

......
stax

ну так оракл откуда знает, что автору нужна одна строка?
он считает, что фетчиться будут все записи, и их легче профулсканить и отсортировать, чем читать сначала индекс, а потом еще и таблицу.
Указывать нада явно чего хотется - все или первую строку

...

Рейтинг:

0 / 0

13.10.2017, 16:50

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536145

Vint

Участник

Откуда: Москва

Сообщения: 4 120

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

13.10.2017, 17:25

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536160

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

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

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

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

Ну, во всяком случае, применение хинта в тесте, выполняемом в SQL Developere, помогло.
План для "OBJECTID">-1 изменился на INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID.
Задержки на открытие минимальны.
:) Если поставить казалось бы абсурдное "OBJECTID" IS NOT NULL, то получается INDEX FULL SCAN.

...

Рейтинг:

0 / 0

13.10.2017, 18:01

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536167

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

VintMustDie,
не зная броду Вы батенька полезли копать одну из самых сложных тем в оракле... Пейджинг требует времени и понимания механизмов.
хотя бы первые три ссылки для начала осильте

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

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

Поэтому не совсем понял, как изучение информации по ссылкам мне поможет.

...

Рейтинг:

0 / 0

13.10.2017, 18:14

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536172

MustDie

Участник

Откуда: Подмосковье

Сообщения: 227

Рейтинг: 0 / 0

Все оказалось гораздо банальнее.
Я в силу своей неопытности в Oracle не с того начал.
Спасибо Тому Кайту.
Его "случай 6" из главы "Почему мой индекс не используется":

Код: plsql

1.
2.

analyze table "MYTABLE" compute statistics;
analyze table "MYTABLE" compute statistics for all indexes;

привело к желаемому INDEX FULL SCAN
для

Код: plsql

SELECT "OBJECTID", "FIELD1", "FIELD2", "FIELD3" FROM "MYTABLE" ORDER BY "OBJECTID";

Большое спасибо всем, кто поучаствовал в дискуссии!
Благодаря Вам я изучил много нового и полезного.

...

Рейтинг:

0 / 0

13.10.2017, 18:31

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536198

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

MustDieСпасибо Тому Кайту.

Код: plsql

analyze table

Врёшь. Он не мог такой херни насоветовать.

...

Рейтинг:

0 / 0

13.10.2017, 21:15

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536200

DВА

Участник

Сообщения: 4 743

Рейтинг: 0 / 0

ElicMustDieСпасибо Тому Кайту.

Код: plsql

analyze table

Врёшь. Он не мог такой херни насоветовать.
ну для 7-ой версии вполне мог ))

...

Рейтинг:

0 / 0

13.10.2017, 21:19

| Ответить | Цитировать | Написать

ORDER BY приводит в ступор (как заставить Oracle работать с умом)

#39536206

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

DВАну для 7-ой версии вполне мог ))Как давно эта макулатура была выпущена...

...

Рейтинг:

0 / 0

13.10.2017, 22:13

| Ответить | Цитировать | Написать

25 сообщений из 29, страница 1 из 2

все

Форумы / Oracle [игнор отключен] [закрыт для гостей] / ORDER BY приводит в ступор (как заставить Oracle работать с умом)

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&startmsg=39535521&tid=1885098]:	0ms
get settings:	7ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	155ms
get topic data:	13ms
get forum data:	3ms
get page messages:	81ms
get tp. blocked users:	1ms
others:	212ms

total:	496ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы