Аналитические функции

Аналитические функции

oracle: простая схема выполнения sql запроса

SQL execution

oracle: DELETE

1. Удалить все записи, где sal < 1000, можно двумя вариантами:

-- можно так
delete from (select * from emp where sal< 1000);

-- или "классический"
delete from emp where sal < 1000;

oracle: Нумерация строк в запросе

Источник: Нумерация строк в запросе

Есть таблица.
Нужно последовательно вывести записи вместе с порядковым номером:

SELECT ROWNUM, pole1, pole2, pole3
  FROM my_table;

Однако если перед выводом нужно записи отсортировать (ORDER BY) по полям, тогда такой запрос не проходит, так как Оракл сначала формирует ROWNUM, и лишь потом сортирует записи.

Поэтому будем оракл обманывать таким образом:

SELECT ROWNUM, pole1, pole2, pole3
  FROM (SELECT   pole1, pole2, pole3
            FROM my_table
        ORDER BY pole1, pole2, pole3);

Ну а для гурманов смотреть здесь «аналитические функции»

oracle: sql условие EXISTS

Условие EXISTS и проверка существования набора значений в ORACLE SQL

Условие EXISTS используется только в одной ситуации — когда вы используете в запросе и подзапрос и хотите проверить, возвращает ли подзапрос записи. Если подзапрос возвращает хотя бы одну запись, то условие EXISTS вернет True, если нет — то False.

Пример применения этого условия может выглядеть так:

SELECT * FROM departments d WHERE EXISTS
(SELECT * FROM employees e WHERE d.department_id = e.department_id);

В этом примере мы возвращаем информацию о всех департаментах, для которых в таблице employees есть сотрудники.

Этот запрос можно переписать с помощью конструкции IN:

SELECT *
  FROM users
 WHERE users.ID IN (SELECT users_id
                      FROM users_res
                     WHERE users.ID = users_res.users_id AND users_res.ID > 1);

Как обычно, это условие EXISTS можно обращать при помощи NOT:

SELECT * FROM departments d WHERE NOT EXISTS
(SELECT * FROM employees e WHERE d.department_id = e.department_id);

Те же возможности предусмотрены и для Microsoft  SQL Server.

oracle: HAVING

HAVING - выражение из оператора SELECT, определяет условие возврата групп и используется только совместно с GROUP BY.

select duplicate_id, value

from test_duplicate

where

  value

  IN (

  select value

  from test_duplicate

  having count(*) > 1

  group by value

  )

order by value;

oracle: join

Ключевое слово join в SQL используется при построении select выражений.
Инструкция join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается.

Существует три типа join-выражений:

    * inner join;
    * outer join;
    * cross join;

oracle: SELECT

1. select from partition
select count(*) from arbor.CDR_UNBILLED partition(CDR_UNBILLED_P_2011_05);
select count(*) from arbor.CDR_DATA partition(CDR_DATA_P_2011_05);

2.Пример селект в селекте
Лучше так не делать, т.к. селект (acct_mgr) будет выполняться для каждой строки, но вот чисто для примера можно делать такие вот селекты:

select c.customer_id, c.cust_fi rst_name|| ’ ‘||c.cust_last_name, 

    (select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id) acct_mgr

from oe.customers c;

CUSTOMER_ID   CUST_NAME                                        ACCT_MGR
---------------        -----------------------------------------  --------------
            147                        Ishwarya Roberts                          Russell
            148                       Gustav Steenburgen                        Russell
...

3.

oracle: определение размеров табличных пространств, таблиц и т.д.

--
-- Свободное место в табличных пространствах
--
set linesize 200
set pagesize 49999
set serveroutput on size 1000000

oracle: блоки каких объектов в buffer_cache

Недокументированная таблица X$BH содержит информацию о заголовках буферов в буферном кеше.

Посмотреть блоки каких объектов находятся в буферном кеше:

select
 s.owner owner,
 object_name objname,
 subobject_name subobjname,
 substr(object_type,1,10) objtype,
 ts.block_size / 1024 blockkb,
 buffer.blocks blocks,
 s.blocks totalblocks,
 (buffer.blocks * ts.block_size / 1024) memkb,
 (buffer.blocks/decode(s.blocks, 0, .001, s.blocks))*100 bufferpercent
from
 (select o.owner, o.object_name, o.subobject_name,
         o.object_type object_type, count(*) blocks
  from dba_objects o, v$bh bh
  where o.object_id = bh.objd and o.owner not in ('SYS','SYSTEM')
  group by o.owner, o.object_name, o.subobject_name, o.object_type) buffer,
  dba_segments s,
 dba_tablespaces ts
where s.tablespace_name = ts.tablespace_name
  and s.owner = buffer.owner
  and s.segment_name = buffer.object_name
  and s.SEGMENT_TYPE = buffer.object_type
  and (s.PARTITION_NAME = buffer.subobject_name or buffer.subobject_name is null)
order by memkb desc;

oracle: работа с датой

1. Системная дата
select sysdate from dual;

2. Текущая дата в сессии
select current_date from dual;

3. Какая дата будет завтра или через несколько дней, месяцев
-- завтра
select sysdate + 1 from dual;

oracle: compression tables and partitions

(в работе)
Сжатие таблиц и партиций
Сжатие данных в целях экономии места и ускорения работы Oracle 
Modifying Table Partitions 

oracle: insert

Примеры

1. oracle: insert как select из другой таблицы

2. insert with select

insert into t values('ANDROID',(select round(avg(user_id)) from t),sysdate);

oracle: NULL

Поле со значением NULL никогда не будет равно полю с тем же значением

Чтобы найти значения NULL можно использовать разные подходы:


1. Использовать ф-цию NVL(exp1,exp2)
если exp1 = NULL, то ф-ция возвращает exp2

oracle: SELECT FOR UPDATE

Уровни изолированности транзакций
oracle: transaction level SERIALIZABLE

Для обеспечения повторяемости при чтении в Oracle не нужно использовать SELECT FOR UPDATE — это делается только для обеспечения последовательного доступа к данным.

Уровни изолированности транзакций

transaction isolation level
Уровни изолированности транзакций
Управление одновременным доступом, согласованность

oracle: insert как select из другой таблицы

Вставка как select из другой таблицы:

1. insert into guppi.managers (empno) (select empno from guppi.emp);

2. insert into guppi.managers (empno, lname) (select empno, ename from guppi.emp);

3. insert /*+ APPEND */ into ARBOR.WP_USER_DATA select * from GUPPI.WP_USER_DATA;

oracle: flashback table

Откатить таблицу на какой нибудь момент времени

oracle: узнать включен ли flashback

!!! нужно разобраться

1. Используем SCN:

flashback table t to scn :scn;

Выскочила ошибка: ORA-08189: cannot flashback the table because row movement is not enabled

Нужно сделать

alter table t enable row movement;

и после

flashback table t to scn :scn;

Выдало ошибку: ORA-30055: NULL snapshot expression not allowed here

Но когда указал конкретный SCN, не через переменную, то получилось

flashback table t to scn 528407;

Получить таблицу на момент какого то SCN

Получить таблицу на момент какого то SCN:

select * from t as of scn :scn;

select * from t as of scn 141434762;

oracle: условие where 1=0

the “where” clause returnes a boolean value…so if you out 1=1 this is a true condition and you will take all the recors…for 1=0 this is a false condition and will not return records….so :-)

1.
select * from emp where 1=0;

возвращает заголовок таблицы(имена столбцов)

2.
select * from emp where 1=1;
возвращает всю таблицу