Oracle-club: FAQ - SQL


Новости | FAQ | Ссылки | Темы | Утилиты | Documentation | Семинары

Вопрос:

Если я выбрал sequence.nextval, а потом несколько раз sequence.currval до закрытия транзакции, то существет ли гарантия, что я буду получать всегда одно и то же значение при условии наличия параллельных транзакций?

Ответ:

Да, гарантируется. У параллельных сессий будут другие значения nextval и currval.

Вопрос:

Некоторые строки таблицы дублированы несколько раз. Каким образом можно унистожить повторные строки? Example: Table A: Id Value1 Value2 1 111 222 2 111 222 3 111 222 4 333 444 5 333 444 The task - is to delete rows with id's 2, 3, 5...

Ответы

Вариант 1:

delete a where id not in ( select min(id) from a group by value1, value2 );

Вариант 2:

Об этих способах рассказывал Валерий Юринский на оracle-клубе (честно ссылаясь на авторов): * Убиение дубликатов (теория) DELETE FROM dept X WHERE EXISTS (SELECT * FROM dept Y WHERE X.ROWID = Y.ROWID INTERSECT SELECT * FROM dept Z WHERE X.ROWID > Z.ROWID) Работает долго! (А.Печкарев pech@commit.zp.ua) * Убиение дубликатов (практика) DELETE FROM dept X WHERE deptno IN SELECT deptno FROM dept GROUP BY dept HAVING COUNT(*)>1) AND EXISTS (SELECT * FROM dept Y WHERE X.deptno = Y.deptno AND X.ROWID > Y.ROWID) Вместо deptno можно поставить любой набор столбцов, по которому определяется дубликат

Вариант 3:

delete from tmp t where t.rowid < (select max(rowid) from tmp where a = t.a) Где а - поле (комбинация полей), которое должно быть уникальным. Ruslanas Tretjakovas (rtretjak@it.lt)

Вариант 4:

DELETE FROM Table t WHERE EXISTS (SELECT 'x' FROM Table WHERE rowid>t.rowid AND col_1=t.col_1 AND col_2=t.col_2 AND : col_n=t.col_n) Michael Seagal (socio@vis.infotel.ru)

Вариант 5:

Самый быстрее способ - воспользоваться exceptions, быстрее любых вложенных селестов раз в надцать! При необходимости: create table e( row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30) ); truncate table e; Например, ищу дубликаты Device.Name Если уже есть индекс по Device.Name (неуникальный :-) будет быстрее! alter table Device add constraint Device_Name unique (Name) exceptions into e; Показать: SELECT A.DeviceID, A.name FROM Device A, e WHERE a.rowid = e.row_id order by A.Name, A.DeviceID; Удалить: DELETE FROM Device WHERE rowid in (SELECT row_id FROM e) При желании можно заюзать остальные поля из таблицы e OKukartsev@hotmail.com

Вариант 1:

Сначала определим, что надо оставить: select min(rowid) from TABLE1 group by A,B,C / а затем используем это при удалении: DELETE FROM TABLE1 WHERE ROWID NOT IN (select min(rowid) from TABLE1 group by A,B,C) / или так, это должно работать быстрее: DELETE FROM TABLE1 WHERE ROWID IN (select rowid from TABLE1 MINUS select min(rowid) from TABLE1 group by A,B,C) / (A,B,C - набор столбцов для определения дублей) Victor Abramov (V.Abramov@globalone.ru) ============================================================================= > Как можно выполнить сортировку в иерархическом запросе, > чтобы не потерять саму иерархию. > > SELECT U.id, U.master_id, U.name > FROM u_tnl U > START WITH U.master_id IS NULL > CONNECT BY PRIOR U.id = U.master_id > ORDER BY ?????????????? U.name ???????????????? - нужно в пределах > уровня сортировать по имени см пример SELECT LPAD(' ',2*(LEVEL-1)) || OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || DECODE(ID, 0, 'Cost = ' || POSITION) "Query plan" FROM PLAN_TABLE WHERE STATEMENT_ID = 'Test' START WITH ID = 0 CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'Test'; нужно делать индекс на master_id,name. Вот проверенный вариант: drop table test_emp; create table test_emp (id integer ,name varchar2(30) ,master_id integer ,constraint test_emp_pk primary key (id) ,constraint test_emp_fk foreign key (master_id) references test_emp ); create index test_emp_master_id_name on test_emp(master_id,name); insert into test_emp(id,name,master_id) values(1 ,'IVANOV I.I',null); insert into test_emp(id,name,master_id) values(2 ,'SIDOROV S.S.',1); insert into test_emp(id,name,master_id) values(3 ,'RINDIN A.S',1); insert into test_emp(id,name,master_id) values(4 ,'PETROV P.P',1); insert into test_emp(id,name,master_id) values(5 ,'VASIN V.V.',2); insert into test_emp(id,name,master_id) values(6 ,'GRIGORIEV G.G.',2); insert into test_emp(id,name,master_id) values(7 ,'SAMOILOV S.S.',2); insert into test_emp(id,name,master_id) values(8 ,'ABRAMOV A.A.',2); insert into test_emp(id,name,master_id) values(9 ,'SERGEEV S.A.',3); insert into test_emp(id,name,master_id) values(10,'ANDREEV T.S.',3); insert into test_emp(id,name,master_id) values(11,'ANDREEV A.A.',3); insert into test_emp(id,name,master_id) values(12,'KAZANOK D.S.',4); insert into test_emp(id,name,master_id) values(13,'SAVOSTA D.E.',4); insert into test_emp(id,name,master_id) values(14,'ANANIEV P.I.',4); insert into test_emp(id,name,master_id) values(15,'USICHENKO U.U.',6); insert into test_emp(id,name,master_id) values(16,'KOSTUK O.V.',6); insert into test_emp(id,name,master_id) values(17,'ONUFRIEV I.P.',6); insert into test_emp(id,name,master_id) values(18,'POGOSYAN V.I.',12); insert into test_emp(id,name,master_id) values(19,'YAKOVLEV U.G.',12); insert into test_emp(id,name,master_id) values(20,'VICTOROV K.I.',12); commit; select substr(rpad(' ',level)||name,1,40) name,id,master_id from test_emp e start with master_id is null connect by prior id=master_id ; NAME ID MASTER_ID ---------------------------------------- ---------- ---------- IVANOV I.I 1 PETROV P.P 4 1 ANANIEV P.I. 14 4 KAZANOK D.S. 12 4 POGOSYAN V.I. 18 12 VICTOROV K.I. 20 12 YAKOVLEV U.G. 19 12 SAVOSTA D.E. 13 4 RINDIN A.S 3 1 ANDREEV A.A. 11 3 ANDREEV T.S. 10 3 SERGEEV S.A. 9 3 SIDOROV S.S. 2 1 ABRAMOV A.A. 8 2 GRIGORIEV G.G. 6 2 KOSTUK O.V. 16 6 ONUFRIEV I.P. 17 6 USICHENKO U.U. 15 6 SAMOILOV S.S. 7 2 VASIN V.V. 5 2 20 rows selected. delete from plan_table; 6 rows processed. explain plan set statement_id='query1' INTO plan_table for select substr(rpad(' ',level)||name,1,40) name,id,master_id from test_emp e start with master_id is null connect by prior id=master_id; select substr(operation,1,10), substr(options,1,15), substr(object_name,1,25), id,parent_id from plan_table order by statement_id,id; SUBSTR(OPE SUBSTR(OPTIONS, SUBSTR(OBJECT_NAME,1,25) ID PARENT_ID ---------- --------------- ------------------------- ---------- ---------- SELECT STA 0 CONNECT BY 1 0 TABLE ACCE FULL TEST_EMP 2 1 TABLE ACCE BY USER ROWID TEST_EMP 3 1 TABLE ACCE BY INDEX ROWID TEST_EMP 4 1 INDEX RANGE SCAN TEST_EMP_MASTER_ID_NAME 5 4 6 rows selected. На плане в конце видно использование индекса, что и дает сортировку. ----------------------------- Применение ORDER BY уничтожает действие CONNECT BY. Но получить записи в определенном порядке иногда (но редко!) удается, построив индекс по столбцу сортировки и заставив Oracle его использовать. Обычно это получается по столбцу col2 , если в CONNECT есть выражение PRIOR col1 = col2. А по name из письма с вопросом - сомневаюсь. А пример ничего не доказывает. Если нет ORDER BY и не используются индексы, то порядок вывода обычно зависит от физического порядка записей в блоке. >С помощью PRIOR можно получить только предыдущий элемент >пути. Жаль что PRIOR может ссылаться только на исходные >значения столбцов, а не на выражения, полученные с их участием. Это еще почему? В документации (Oracel SQL Reference) в описании SELECT ясно сказано PRIOR expr comparision_operator expr. :Имелось ввиду, что нельзя ссылаться на алиасы выражений. То есть. Имеется выражение (PRIOR NAME) || NAME. SELECT (PRIOR NAME) || NAME FOO_NAME FROM EMP CONNECT BY ... -- Далее все знают Это можно. SELECT (PRIOR FOO_NAME) || NAME FOO_NAME FROM EMP CONNECT BY ... Это нельзя. А жаль. ==================================================================== > Проблема возникла -- создавал таблицы на Oracle8 при > помощи Delphi's Database Desktop, при этом по глупости ;( > вводил маленькие имена таблиц и столбцов в них. > Доступ к таблицам из Delphi нормальный (через компоненты DOA), > а из любой проги, использующей SQL (например, SQL*plus), нет -- > говорит, что таблица/представление с таким именем не существуют. > Как полегче и побыстрее переименовать таблицы? ИЗ SQL PLUS ПОПРОБУЙ ТАКУЮ ВЕЩЬ SELECT * FROM "tbl_NAMe" ITS will be case чувствительная ---------------------------- spool magnificient_rename_script.sql select 'RENAME "' || table_name || '" to ' || UPPER(table_name) ||';' from user_tables where table_name <> UPPER(table_name); spool off
Триггер работает в рамках сессии пользователя, то есть в нем действуют обычные средстви идентификации USER - имя пользователя UID - код пользователя (ORACLE) USERENV('SESSIONID') - код сессии пользователя Обратится к данным ресурсам можно через SELECT USER INTO .... FROM dual можно обратится и напрямую (так как PLSQL) u_name := USER; --------- Кстати можно по USERENV('SESSIONID') получить код сессии пользователя и через таблицу sys.v_$session получить все данные о пользователе и его сессии. =================================================================== Change your constraint and you do not need after delete trigger: ALTER TABLE Org ADD CONSTRAINT RefObj1 FOREIGN KEY (Org_ID) REFERENCES Obj(Obj_ID) ON DELETE CASCADE; Andrew Protasov > I have two tables > > CREATE TABLE Obj( > Obj_ID NUMBER(10, 0) NOT NULL PRIMARY KEY > ) ; > > CREATE TABLE Org( > Org_ID NUMBER(10, 0) NOT NULL PRIMARY KEY > ) ; > > ALTER TABLE Org ADD CONSTRAINT RefObj1 > FOREIGN KEY (Org_ID) > REFERENCES Obj(Obj_ID); > > When I delete from ORG record with ORG_ID, I need to delete from OBJ record > with OBJ_ID equal ORG_ID (Right this way - detail-master). The trigger is > > CREATE OR REPLACE TRIGGER DELORGFROMOBJ > AFTER DELETE ON ORG > FOR EACH ROW > BEGIN > DELETE FROM OBJ > WHERE OBJ_ID = :OLD.ORG_ID; > END; > > This trigger raise exception "ORA-04091 Table ORG is mutaiting, > trigger/function may not see it." But I really need this way modification of > both tables

Вопрос:

Каким образом посмотреть в SQL*Plus включены или выключены триггеры

Ответ:

select status from dba_triggers where owner='OWNER_OF_TRIGGER' and trigger_name='NAME_OF_TRIGGER';

Вопрос:

Существует ли в Oracle FULL JOIN? Пример: select 1 from (select 1 a from dual ) a, (select 2 b from dual )b where a.a (+) = b.b (+) * ERROR at line 1: ORA-01468: a predicate may reference only one outer-joined table Или как это просто сделать? хотелось бы так (cм. пример) CREATE TABLE temp_a (key_a NUMBER(1) NOT NULL, field_a VARCHAR2(10) NULL, constraint pk_key_a primary key (key_a) ); CREATE TABLE temp_b (key_b NUMBER(1) NOT NULL, field_b VARCHAR2(10) NULL, constraint pk_key_b primary key (key_b) ); INSERT INTO temp_a VALUES(1, 'a_1'); INSERT INTO temp_a VALUES(2, 'a_2'); INSERT INTO temp_a VALUES(3, 'a_3'); INSERT INTO temp_b VALUES(1, 'b_1'); INSERT INTO temp_b VALUES(4, 'b_4'); INSERT INTO temp_b VALUES(5, 'b_5'); SQL> SELECT field_a, field_b 2 FROM temp_a, temp_b 3 / FIELD_A FIELD_B ---------- ---------- a_1 b_1 a_2 b_1 a_3 b_1 a_1 b_4 a_2 b_4 a_3 b_4 a_1 b_5 a_2 b_5 a_3 b_5 А хотелсоь бы FIELD_A FIELD_B ---------- ---------- a_1 b_1 a_2 a_3 b_4 b_5

Вариант 1:

SQL> select a.field_a, b.field_b 2 from temp_0 z, temp_a a, temp_b b 3 where a.key_a (+) = z.key_0 4 and b.key_b (+) = z.key_0; FIELD_A FIELD_B ---------- ---------- a_1 b_1 a_2 a_3 b_4 b_5 Valeri Sorokine

Вариант 2:

SELECT field_a, field_b FROM temp_a, temp_b WHERE key_a = key_b(+) UNION SELECT field_a, field_b FROM temp_a, temp_b WHERE key_a(+) = key_b;

Вариант 3:

SELECT field_a, field_b FROM temp_a, temp_b, (SELECT key_a key_ab FROM temp_a UNION SELECT key_b FROM temp_b) WHERE key_ab = key_a(+) AND key_ab = key_b(+); Краснопольский Михаил. Украина, Харьков, Банк "Грант". Phone: work 380 - 572 - 140-296 E-mail: mgk@grant.kharkov.ua

Вариант 4:

select a.a, b.b from (select 1 a from dual ) a, (select 2 b from dual ) b 2 where a.a(+) = b.b 3 union 4 select a.a, b.b from (select 1 a from dual ) a, (select 2 b from dual ) b 5 where a.a = b.b(+) 6 / A B --------- --------- 1 2 -- Evgeny Zybarev ( zev@nsu.ru )

Вопрос:

Возможно ли в переменную SQL+ записать значение, возвращаемое из заданного SELECTа ? Например, мне необходимо получить имя текущего usera и назвать spool файл его именем ?

Ответ:

COLUMN usernm NEW_VALUE usernm NOPRINT SELECT LOWER(user) usernm FROM dual; SPOOL &usernm SELECT * FROM dual; SPOOL OFF EXIT

Вопрос:

В Oracle можно ли подзапрос вставить в список полей запроса, т.е.: select A, B, ( select sum( V ) from T ) from ...

Ответ:

Это в 7 делается так : select a, b, c.smt from ..., (select sum(v) smt from t) c where ... В восьмёрке - так : SQLWKS> select dual.*,(select sum( 5 ) from dual ) from dual; D (SELECTSUM - ---------- X 5 Выбрана 1 строка.

Тема :

ORDER BY в динамических представлениях

Вопрос:

> Можно ли использовать ORDER BY в динамических > представлениях? И если нельзя, то почему? Мой 7.3 ругается на фразу типа > > select a.*, b.* > from table1 a, > ( select * from table3 order by field1 ) b

Ответ:

Victor Abramov (V.Abramov@globalone.ru) Нельзя. Не только в динамических, но и в обычных представлениях нельзя. И это правильно. Если все же очень хочется, то попытайтесь использовать group by по уникальному столбцу - он дает побочный эффект сортировки. Serj (Serj@mail.primorye.ru) Эта возможность доступна начиная с версии сервера 8.1.5 (8i)

Новости | FAQ | Ссылки | Темы | Утилиты | Documentation | Семинары