Как указать сортровку при апдейте в Оракле ?

← →
ANB © (2005-05-17 12:32) [0]

Делаю апдейт :
update Step S
set S.Num = rownum * 10
where S.Test_ID = :Test_ID
Все замечательно работало, пока не повесил на Step еще один индекс. Съехала сортировка и теперь не так идет перенумерация. В селекте можно ордер бай добавить. А как здесь ? Или без курсора никак ?

← →
ANB © (2005-05-17 12:57) [1]

И что, никак ? Ладно, я уже через курсор сделал.

← →
Sergey13 © (2005-05-17 13:13) [2]

>Все замечательно работало, пока не повесил на Step еще один индекс.
Тут скорее вопрос почему раньше работало. 8-)

← →
Johnmen © (2005-05-17 13:14) [3]

Какая сортировка ? Где сортировка ? Какая перенумерация ? Где здесь ?
Ты вообще о чём ???

← →
ANB © (2005-05-17 13:16) [4]

> Тут скорее вопрос почему раньше работало. 8-)
- да есть такая фича в оракле. Если индекс один и запрос такой, что он юзается, то данные приходят в сортировке по нему сами.

> Ты вообще о чём ???
см. сабж. Там перенумерация в подчиненной таблице. Как в старом GWBasic перенумерация операторов.

← →
Sergey13 © (2005-05-17 13:19) [5]

2[4] ANB © (17.05.05 13:16)
Там сортировка - побочный эфект вроде. Просто данные при селекте из индекса берутся, а не из таблицы. Но тут то селекта нет. Хотя может я и ошибаюсь.

← →
Johnmen © (2005-05-17 13:20) [6]

>см. сабж

Посмотрел. Не увидел... М.б. мне к окулисту сходить ? :)))
Но потелепатив малька, думаю, что у тебя что-то в консерватории (в структуре/логике БД) не то.

← →
DenK_vrtz © (2005-05-17 13:25) [7]

>ANB ©

rownum использовать в DML-операции, как-то, мягко сказать, не по-человечески :)

← →
ANB © (2005-05-17 14:24) [8]

> DenK_vrtz © (17.05.05 13:25) [7]
ой, да ладно, кошерно, не кошерно. Почему бы и не поюзать, если работает. Я и в DDL использовал rownum.

← →
ANB © (2005-05-17 14:55) [9]

> Sergey13 © (17.05.05 13:19) [5]
- при апдейте с where курсор неявно все равно создается. И выборка идет. Правила те же. Только теперь на уникальный индекс переключился. Дык есть способ (хинтануть например или еще что) ?

← →
Johnmen © (2005-05-17 15:02) [10]

Попробуй добавить в where ... and (S.Step+1=S.Step+1)
но я бы правил консерваторию :)

← →
Sergey13 © (2005-05-17 15:04) [11]

2[9] ANB © (17.05.05 14:55)
При "чистом" селекте данные берутся из индекса. Апдейтится же сама таблица, а не индекс. Порядок, ИМХО, все равно не гарантируется. Просто везло наверное раньше. 8-)

>Дык есть способ (хинтануть например или еще что) ?
Я не знаю такого хинта. 8-)

← →
ANB © (2005-05-17 15:06) [12]

> но я бы правил консерваторию :)
- типа ?

> Sergey13 © (17.05.05 15:04) [11]
- жаль. Ну пусть курсором обновляется.

← →
Johnmen © (2005-05-17 15:11) [13]

>- типа ?

Типа структура/логика БД.

← →
ANB © (2005-05-17 15:17) [14]

> Johnmen © (17.05.05 15:11) [13]
- не, там уже ничего не придумаешь лучше. Хотя попробуй :
Есть шаги (записи в таблице Step), которые надо выполнять в строгой последовательности. И юзер должен иметь возможность ее менять.
Я реализовал - завел поле num, в автомате добавляю в него по 10 и сортирую по нему же. Для красоты, если юзер начнем жестоко менять нумерацию, делая 15, 16, 17 я сделал перенумерацию с сохранением старого порядка.

← →
Johnmen © (2005-05-17 15:30) [15]

>ANB © (17.05.05 15:17) [14]

>в автомате добавляю в него по 10 и сортирую по нему же.

Не понял... Это зачем ?

>И юзер должен иметь возможность ее менять.

Ну и пусть меняет.
Любая "смена" разложима на атомарные операции перестановки. Их и надо делать....
Или ты о чём ?

← →
Sergey13 © (2005-05-17 15:37) [16]

2[14] ANB © (17.05.05 15:17)
Я делал подобное. Но на клиенте в цикле прокручивал датасет и перенумеровывал.

← →
Desdechado © (2005-05-17 15:40) [17]

странно про индексы-то...
я вот когда с IB на Oracle перебрался, отлавливал у себя в программе баги такого рода:
- в IB если делаешь SELECT * FROM tbl, то, при наличии первичного или уникального ключа, данные выбираются отсортированными по нему
- в Oracle при такой же операции данные выбираются отсортированными по rowid, а он назначается Ораклом как попало (это всего лишь ВНУТРЕННИЙ идентификатор строки в базе)
Поэтому пришлось под Оракл вставлять явные сортировки.

← →
ANB © (2005-05-17 15:42) [18]

> Johnmen © (17.05.05 15:30) [15]
ну так порядок все равно хранить в num придется. А кнопки делать вверх-вниз лень. Вывел в грид num, народу показал - все довольны.

← →
Sergey13 © (2005-05-17 15:44) [19]

2[17] Desdechado © (17.05.05 15:40)
> а он назначается Ораклом как попало
Не то что бы "как попало", но сортировать по нему бестолку. 8-)

← →
ANB © (2005-05-17 16:16) [20]

> Desdechado © (17.05.05 15:40) [17]
, да по первичному ключу оракл сам не сортирует. А вот если условем заставить его идти по индексу, то данные так и приедут. Но не всегда так, как хочешь, если индексов несколько :)))

← →
Danilka © (2005-05-17 16:18) [21]

Интересно очень, все-таки зачем хранить в базе rownum, пусть даже и умноженый на 10?

← →
ANB © (2005-05-17 16:23) [22]

> Danilka © (17.05.05 16:18) [21]
читай посты выше. Есть другие варианты решения задачи ?

← →
Sergey13 © (2005-05-17 16:23) [23]

2 [21] Danilka © (17.05.05 16:18)
Да не ровнум он хранит, а порядок сортировки, который вычисляется на его основе. 8-)

← →
Danilka © (2005-05-18 08:15) [24]

Понятно.
[22] ANB © (17.05.05 16:23)
> Есть другие варианты решения задачи ?

Например, после с порядком сортировки вообще скрыть от пользователя. В интерфейс добавить две кнопочки "вверх" и "вниз", которые меняют значения полей сортировки у текущий записи и у предыдущей/последующей, после чего переоткрывает запрос, например. Можно еще две кнопочки добавить "на самый верх" и "на самый низ".
:)

← →
ANB © (2005-05-18 09:29) [25]

> Danilka © (18.05.05 08:15) [24]
- 1) - у меня уже кнопочки цеплять некуда :)
2) Вариант с полем в гриде всем больше понравился.
3) Через курсор в безымянном блоке все прекрасно работает, как я хочу.

← →
Danilka © (2005-05-18 09:36) [26]

[25] ANB © (18.05.05 09:29)
> 1) - у меня уже кнопочки цеплять некуда :)

Это ты зря. Лучше разделить на закладки например.
Очень полезная весчь в тему:
http://www.uibook1.ru/stat/click.php?http://www.uibook1.ru/uidesign1.pdf
весит 2.5МБ.

← →
ANB © (2005-05-18 09:40) [27]

Дык у меня уже около 16 закладок и 4 уровня вложенности по ним.

Попробуй типа такого:

UPDATE Step S
SET S.Num = (SELECT ROW_NUMBER()*10 OVER (ORDER BY O.Num) FROM Step O WHERE O.ID = S.ID)

Такой вроде синтаксис. Не знаю только с какой версии такое будет работать, в 9-ой точно работает.

Не, в 8 не работает. А откуда ROW_NUMBER() ? Я не видел такой функции и на 9.

ANB © (17.05.05 13:16) [4]

> Тут скорее вопрос почему раньше работало. 8-)
- да есть такая фича в оракле.

И где же она описана?
Ткни пальцем, у меня по всем версиям почти документация есть

ROW_NUMBER() - системная функция. Только что проверил в 9 работает, 8 у меня нет. Замечание: ROW_NUMBER()*10 - так ругается, поэтому надо вынести за скобки подзапроса. В любом случае, этот подзапрос можно переделать и под 8, если там нет ROW_NUMBER и конструкции OVER. Просто получи в селекте с ORDER BY нужный порядок, а потом с этим селектом еще один select + rownum.

> Petr V. Abramov © (18.05.05 11:55) [30]
- да нигде она не описана. Знаю, что оракл не гарантирует сортировку, если она не указана явно. Но бывают частности, которые работают. Вот попробуй сделать вывод дерева с connect by, чтобы в одной ветке листья сортировались хотя бы по алфавиту. А если грамотно индекс завести, то все приходит как надо.

> evvcom © (18.05.05 12:04) [31]
- сохраню ка я себе твой пост на будущее. Не очень понял, как это у меня отработает. Потом разберусь. Интересно. А коротенько - OVER что делает ? Ни разу не юзал.

> И где же она описана?
> Ткни пальцем, у меня по всем версиям почти документация
> есть

Это вопрос мне?
Например здесь http://www.constant.obninsk.ru/doc/db2/db2s0/olapfunc.htm - первое что нашел в сети сейчас. Хотя судя по пути это для db2, но в Oracle 9.2.0.1 работает точно.

> evvcom © (18.05.05 12:10) [34]
- это не тебе, это мне был пост. Плз, напиши по русски сам. У меня нет подрезанный. Закрыты все сайты кроме этого.

> А коротенько - OVER что делает

OVER переписывает уже в результате (типа открывает окно) новые значения (на 2 проходе).

> У меня нет подрезанный

SQL Reference

OLAP Functions

OLAP-function

|--+-| ranking-function |-----+---------------------------------|
+-| numbering-function |---+
"-| aggregation-function |-"

ranking-function

|---+-RANK ()-------+--OVER------------------------------------->
"-DENSE_RANK ()-"

>----(--+------------------------------+------------------------>
"-| window-partition-clause |--"

>----| window-order-clause |--)---------------------------------|

numbering-function

|---ROW_NUMBER ()--OVER---(--+------------------------------+--->
"-| window-partition-clause |--"

>----+--------------------------+---)---------------------------|
"-| window-order-clause |--"

aggregation-function

|---column-function--OVER---(--+------------------------------+->
"-| window-partition-clause |--"

>----+--------------------------+------------------------------->
"-| window-order-clause |--"

.-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING--.
>----+------------------------------------------------------------+---)->
"-| window-aggregation-group-clause |------------------------"

>---------------------------------------------------------------|

window-partition-clause

.-,--------------------------.
V |
|---PARTITION BY-----partitioning-expression---+----------------|

window-order-clause

.-,-------------------------------.
V .-ASC--. |
|---ORDER BY-----sort-key-expression--+------+--+---------------|
"-DESC-"

window-aggregation-group-clause

|---+-ROWS--+---+-| group-start |---+---------------------------|
"-RANGE-" "-| group-between |-"

group-start

|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
"-CURRENT ROW-------------------"

group-between

|---BETWEEN--| group-bound1 |--AND--| group-bound2 |------------|

group-bound1

|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
"-CURRENT ROW-------------------"

group-bound2

|---+-UNBOUNDED FOLLOWING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
"-CURRENT ROW-------------------"

On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used as an argument of a column function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function.

When specifying an OLAP function, a window is specified that defines the rows over which the function is applied, and in what order. When used with a column function, the applicable rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a partition by month, an average can be calculated over the previous three month period.

The ranking function computes the ordinal rank of a row within the window. Rows that are not distinct with respect to the ordering within their window are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.

If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus, if two or more rows are not distinct with respect to the ordering, then there will be one or more gaps in the sequential rank numbering.

If DENSE_RANK 36 is specified, the rank of a row is defined as 1 plus the number of rows preceding that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.

The ROW_NUMBER 37 function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order as returned by the subselect (not according to any ORDER BY clause in the select-statement).

The data type of the result of RANK, DENSE_RANK or ROW_NUMBER is BIGINT. The result cannot be null.

PARTITION BY (partitioning-expression,...)
Defines the partition within which the function is applied. A partitioning-expression is an expression used in defining the partitioning of the result set. Each column-name referenced in a partitioning-expression must unambiguously reference a result set column of the OLAP function subselect statement (SQLSTATE 42702 or 42703). The length of each partitioning-expression must not be more than 255 bytes (SQLSTATE 42907). A partitioning-expression cannot include a scalar-fullselect (SQLSTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845).

ORDER BY (sort-key-expression,...)
Defines the ordering of rows within a partition that determine the value of the OLAP function or the meaning of the ROW values in the window-aggregation-group-clause (it does not define the ordering of the query result set). A sort-key-expression is an expression used in defining the ordering of the rows within a window partition. Each column-name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect including the OLAP function (SQLSTATE 42702 or 42703). The length of each sort-key-expression must not be more than 255 bytes (SQLSTATE 42907). A sort-key-expression cannot include a scalar fullselect (SQLSTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSE_RANK functions (SQLSTATE 42601).

ASC
Uses the values of the sort-key-expression in ascending order. Null values are considered last in the order.

DESC
Uses the values of the sort-key-expression in descending order. Null values are considered first in the order.

window-aggregation-group-clause
The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows of R"s partition. This clause specifies the aggregation group.

ROWS
Indicates the aggregation group is defined by counting rows.

RANGE
Indicates the aggregation group is defined by an offset from a sort key.

group-start
Specifies the starting point for the aggregation group. The aggregation group end is the current row. Specification of the group-start clause is equivalent to a group-between clause of the form "BETWEEN group-start AND CURRENT ROW".

group-between
Specifies the aggregation group start and end based on either ROWS or RANGE.

UNBOUNDED PRECEDING
Includes the entire partition preceding the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.

UNBOUNDED FOLLOWING
Includes the entire partition following the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.

CURRENT ROW
Specifies the start or end of the aggregation group as the current row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.

value PRECEDING
Specifies either the range or number of rows preceding the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or value FOLLOWING.

value FOLLOWING
Specifies either the range or number of rows following the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

Examples:
Display the ranking of employees, in order by surname, according to their total salary (based on salary plus bonus) that have a total salary more than $30,000.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE WHERE SALARY+BONUS > 30000
ORDER BY LASTNAME

Note that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:

ORDER BY RANK_SALARY

or
ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)

Rank the departments according to their average total salary.
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY RANK_AVG_SAL

Rank the employees within a department according to their education level. Having multiple employees with the same rank in the department should not increase the next ranking value.
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL
DENSE_RANK() OVER
(PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME

Provide row numbers in the result of a query.
SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
LASTNAME, SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME

List the top five wage earners.
SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE) AS RANKED_EMPLOYEE
WHERE RANK_SALARY < 6
ORDER BY RANK_SALARY

Notice that a nested table expression was used to first compute the result, including the rankings, before the rank could be used in the WHERE clause. A common table expression could also have been used.

Как указать сортровку при апдейте в Оракле ? Найти похожие ветки