Ну и с чего вы решили что тормозит сортировка?
Насколько я разобрался в этой xml-хренотени — основные тормоза из-за nested loops объединения таблиц uclient и physical_uclient. А раз у вас там даже соответствующий хинт используется, то вы точно знаете зачем вам это надо.
По плану строка с nested loops оценена оптимизатором в cost=1904443, тогда как последующая сортировка оценена в cost=1926395. Т.е. оптимизатор оценивает сортировку в (1926395-1904443)/1904443 ~ 1 процент от данного nested loops join. Копайте в сторону этого джойна, а не сортировки/индекса.

И делайте план хотябы через:
explain plan for select ....;
select * from table(dbms_xplan.display);
И сделайте замер производительности запроса через трассировку 10046.

Добрый день.

Я столкнулся с проблемой в плане производительности при сортировке таблицы (1 млн. записей). Я добавил индексы на поля, по которым идет сортировка, но результата нет. Пожалуйста, подскажите в чем может быть проблема либо дайте ссылки на литературу по данному вопросу.

Заранее благодарен.

Здравствуйте, Petr.com.

DDL, запрос, его план, а заодно и трейс 10053 в студию.

Здравствуйте, Petr.com, Вы писали:

PC>Добрый день.

PC>Я столкнулся с проблемой в плане производительности при сортировке таблицы (1 млн. записей). Я добавил индексы на поля, по которым идет сортировка, но результата нет. Пожалуйста, подскажите в чем может быть проблема либо дайте ссылки на литературу по данному вопросу.

PC>Заранее благодарен.

0. Прежде всего нужно убедиться, что действительно тормозит сортировка и вдумчиво посмотреть план запроса.
1. Вы добавили индексы на таблицу, но используются ли они оптимизатором запросов? На этот вопрос также ответит план запроса.
2. Индексы не используются, но будет ли при их использовании запрос выполняться быстрее? Вы можете попробовать проверить это явно подключив индекс с помощью хинта INDEX. http://www.psoug.org/reference/hints.html
3. С индексами запрос работает быстрее? Тогда надо добиться, чтобы индекс подхватывался оптимизатором автоматически. Например, проверить, что собрана статистика по таблице и индексу.
4. С индексом запрос работает еще медленнее? Значит индекс не нужен, а затык в чем-то другом. Возможно, недостаточен размер PGA, для запроса выделяется недостаточная рабочая область, и сортировка выполняется в несколько проходов. А может и просто железо слабое.

Здравствуйте, Овощ, Вы писали:

О>Здравствуйте, Petr.com.

О>DDL, запрос, его план, а заодно и трейс 10053 в студию.

Пожалуйста

CREATE OR REPLACE FORCE VIEW "V_PHYSICAL_UCLIENT" ("UCLIENT_ID", "PHYSICAL_UCLIENT_ID", "LNAME", "FNAME", "PNAME", "BIRTH_DATE", "ADDRESS", "UCLIENT_STATUS", "STATE", "BRANCH_ID") AS
select /*+ use_nl(pc uc)*/ uc.uclient_id, pc.physical_uclient_id, pc.lname, pc.fname, pc.pname, pc.birth_date,
(select case when ar.region is not null then ar.region || ', ' else '' end ||
case when ar.district is not null then ar.district || ', ' else '' end ||
case when ar.city is not null then ar.city || ', ' else '' end ||
case when ar.settlement is not null then ar.settlement || ', ' else '' end ||
case when ar.street is not null then ar.street || ', ' else '' end ||
case when ar.home is not null then ' д.' || ar.home || ', ' else '' end ||
case when ar.home_subnum1 is not null then 'корп.' || ar.home_subnum1 || ', ' else '' end ||
case when ar.home_subnum2 is not null then 'стр.' || ar.home_subnum2 || ', ' else '' end ||
case when ar.apartment is not null then 'кв.' || ar.apartment || ', ' else '' end
from address ar where uc.uclient_id = ar.uclient_id and ar.addr_type_id = 1) address,
(select cs.name from uclient_status cs where uc.status_id = cs.status_id) uclient_status,
nvl(uc.state,0) state, uc.branch_id
from uclient uc inner join physical_uclient pc on uc.uclient_id = pc.uclient_id;

запрос

select * from V_PHYSICAL_UCLIENT order by lname

план (xml export)

<results>
<row>
<Operation><![CDATA[SELECT STATEMENT]]></Operation>
<Optimizer><![CDATA[ALL_ROWS]]></Optimizer>
<Cost><![CDATA[1926395]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.ADDRESS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[4]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[143]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA["AR"."ADDR_TYPE_ID"=1]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(RANGE SCAN) RSHB_UNFR.XIF4ADDRESS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[3]]></Cost>
<Cardinality><![CDATA[2]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["AR"."UCLIENT_ID"=:B1]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.UCLIENT_STATUS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[1]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[28]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(UNIQUE SCAN) RSHB_UNFR.XPK_PHYSICALUCLIENTSTATUS]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[0]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["CS"."STATUS_ID"=:B1]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[SORT(ORDER BY)]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[1926395]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[NESTED LOOPS]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[]]></Cost>
<Cardinality><![CDATA[]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[NESTED LOOPS]]></Operation>
<Optimizer><![CDATA[]]></Optimizer>
<Cost><![CDATA[1904443]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[96119496]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(FULL) RSHB_UNFR.PHYSICAL_UCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[18803]]></Cost>
<Cardinality><![CDATA[942348]]></Cardinality>
<Bytes><![CDATA[81984276]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[INDEX(UNIQUE SCAN) RSHB_UNFR.XPKUCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[1]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA["PC"."UCLIENT_ID"="UC"."UCLIENT_ID"]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
<row>
<Operation><![CDATA[TABLE ACCESS(BY INDEX ROWID) RSHB_UNFR.UCLIENT]]></Operation>
<Optimizer><![CDATA[ANALYZED]]></Optimizer>
<Cost><![CDATA[2]]></Cost>
<Cardinality><![CDATA[1]]></Cardinality>
<Bytes><![CDATA[15]]></Bytes>
<Partition_Start><![CDATA[]]></Partition_Start>
<Partition_Stop><![CDATA[]]></Partition_Stop>
<Partition_Id><![CDATA[]]></Partition_Id>
<ACCESS_PREDICATES><![CDATA[]]></ACCESS_PREDICATES>
<FILTER_PREDICATES><![CDATA[]]></FILTER_PREDICATES>
</row>
</results>

Здравствуйте, Petr.com, Вы писали:

PC>Добрый день.

PC>Я столкнулся с проблемой в плане производительности при сортировке таблицы (1 млн. записей). Я добавил индексы на поля, по которым идет сортировка, но результата нет. Пожалуйста, подскажите в чем может быть проблема либо дайте ссылки на литературу по данному вопросу.

PC>Заранее благодарен.
Статистику надеюсь собрали?

на uclient_id ключи есть?

Здравствуйте, Petr.com, Вы писали:

PC>Добрый день.

PC>Я столкнулся с проблемой в плане производительности при сортировке таблицы (1 млн. записей). Я добавил индексы на поля, по которым идет сортировка, но результата нет. Пожалуйста, подскажите в чем может быть проблема либо дайте ссылки на литературу по данному вопросу.

Жесть.

Ссылка на литературу №1

1.

запрос
select * from V_PHYSICAL_UCLIENT order by lname

Это что, реальный запрос, использующийся в приложении? Кому-то постоянно нужен список из ВСЕХ (1 млн) клиентов, отсортированных [только] по фамилии? Не верю! (Хотя все может быть)

2.

( select case ... from address ...) address
(select cs.name from uclient_status ...) uclient_status

Еще один источник низкой производительности, особенно при большом объеме выборки.

3.

/*+ use_nl(pc uc)*/

Автор view явно не предполагал такие запросы как п.1. + 1 причина низкой производительности.

+ Ты не показал индексы. А план показал в таком виде, что и смотреть не хочется.

P.S. А сколько советов вумных сразу надавали!..

wildwind пишет:

> 1.
> запрос
> select * from V_PHYSICAL_UCLIENT order by lname
>
> Это что, реальный запрос, использующийся в приложении? Кому-то постоянно
> нужен список из ВСЕХ (1 млн) клиентов, отсортированных [только] по
> фамилии? Не верю! (Хотя все может быть)

Вот люблю я людей, которые могут ухватывать главное.

"Зри в корень" (с) Козьма Прутков.