INNER JOIN vs LEFT RIGHT FULL OUTER JOIN

Джойны - это запросы, комбинирующие строки из нескольких таблиц, представлений или материализованных представлений, которые перечислены в секции FROM запроса.

INNER JOIN
Inner join или просто join возвращает только те строки из двух таблиц, которые удовлетворяют условию указанному в ON:

SELECT 
    s.id, 
    s.supplier_name, 
    o.order_date 
FROM suppliers s
INNER JOIN orders o 
    ON o.supplier_id = s.id;

OUTER JOIN
Outer join возвращает все строки, удовлетворяющие условию, указанному в ON, а также некоторые строки, которые не удовлетворяют этому условию:

  • LEFT JOIN - дополнительно возвращает все строки из левой части условия;
  • RIGHT JOIN - дополнительно возвращает все строки из правой части условия;
  • FULL JOIN - дополнительно возвращает все строки из обеих частей условия.


Поясняющая картинка из статьи на CodeProject:

Полнотекстовый поиск при помощи Oracle Text

Oracle Text - это бесплатная компонента, входящая в состав Oracle Database, предназначенная для создания приложений с использованием полнотекстового поиска. В предыдущих версиях Oracle имела также названия ConText и interMedia Text. Начиная с 11ой версии входит во все редакции СУБД.

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

Oracle Text позволяет строить полнотекстовые индексы 4-ёх типов:

  • CONTEXT - основной тип индекса. Применяется для индексирования содержимого одной или нескольких колонок таблицы;
  • CTXCAT - применяется для индексации небольших текстовых фрагментов, не требует периодической переиндексации;
  • CTXRULE - применяется при создании приложений для классификации документов;
  • CTXXPATH - предназначен для ускорения работы с полями типа XMLType.

Рассмотрим пример создания контекстного полнотекстового индекса по таблице документов docs.

Создадим таблицу документов и наполним ее данными:

create table docs (id number not null, text varchar2(1000) not null);
insert into docs values (1, 'Шла Саша по шоссе и сосала сушку.');
insert into docs values (2, 'London is the capital of The United Kingdom of Great Britain and Northern Ireland.');


Создадим настройки для полнотекстового индекса и выполним его построение:

begin
    ctx_ddl.create_preference('my_wordlist', 'BASIC_WORDLIST');
    ctx_ddl.create_preference('my_lexer', 'AUTO_LEXER');
    ctx_ddl.set_attribute('my_lexer', 'INDEX_STEMS','YES');
end;

create index docs_idx on docs (text) indextype is ctxsys.context parameters ('LEXER my_lexer WORDLIST my_wordlist');


Тестируем построенный индекс:

select * from docs where contains(text, 'саша') > 0;

ID TEXT
1 Шла Саша по шоссе и сосала сушку.

select * from docs where contains(text, '$идти') > 0;

ID TEXT
1 Шла Саша по шоссе и сосала сушку.

Обратите внимание, что слово 'идти' в тексте документов не встречается, но благодаря использованию оператора стемминга $, найден документ, в котором встречается словоформа слова 'идти', т.е. слово 'шла'.

select * from docs where contains(text, 'united') > 0;

ID TEXT
2 London is the capital of The United Kingdom of Great Britain and Northern Ireland.

select * from docs where contains(text, 'uni%') > 0;

ID TEXT
2 London is the capital of The United Kingdom of Great Britain and Northern Ireland.

Что еще полезного (помимо вышеприведенных поиска с морфологией и поиска по шаблонам) есть в Oracle Text:
  • подстветка искомых слов в найденном - реализуется при помощи процедур CTX_DOC.MARKUP или CTX_DOC.POLICY_MARKUP;
  • поиск с учетом логических выражений (AND, OR, NOT), расстояния между словами (при помощи оператора NEAR);
  • поиск с учетом тезауруса, синонимов, созвучных слов - к сожалению не работает для русского языка;
  • встоенное извлечение текста из документов разных форматов, например MS Word, Excel, Pdf, RTF, HTML и массы других при помощи утилиты cthxh или процедур CTX_DOC.FILTER или CTX_DOC.IFILTER;
  • возможность индексирования документов, расположенных не только в БД, но и на внешних ресурсах, например в файлах ОС или даже в интернет. Реализуется при помощи установки параметра DATASTORE.

Начиная с версии 11.2 Oracle Text позволяет извлекать из текста документов сущности - например, имена и должности персон, названия стран, городов, компаний, url-адреса, почтовые индексы и т.д., с помощью функций пакета ctx_entity, правда пока только для документов на английском языке.

Описания ошибок в Oracle Database

Найти описание ошибки по ее коду можно в разделе Oracle® Database Error Messages официального сайта Oracle Database Documentation Library. Если же у вас нет доступа в интернет, то можно воспользоваться утилитой oerr, которую можно найти в папке bin сервера. К сожалению, и в первом и во втором случаях описания доступны только на английском языке, так что если у вас проблемы с английским, Google Translate в помощь! А лучше учите английский, например вот здесь.

Корзина и восстановление удаленных таблиц в Oracle

Начиная с 10-ой версии СУБД Oracle содержит такой инструмент восстановления случайно удаленных таблиц как корзина (recycle bin), хорошо известный пользователям Windows, MacOS и других операционных систем. При удалении таблицы и их индексы не стираются с диска физически, а специальным образом переименовываются Ораклом, т.е. перемещаются в корзину.

Посмотреть содержимое корзины можено через представление USER_RECYCLEBIN или просто RECYCLEBIN. Очистка корзины производится командой PURGE RECYCLEBIN, а восстановление таблицы - FLASHBACK TABLE.


create table TEST (TEST_COLUMN number primary key);
insert into TEST values (100);
insert into TEST values (200);
select * from TEST;

-- удаляем таблицу
drop table TEST;

-- создаем новую таблицу TEST с новыми данными
create table TEST (TEST_COLUMN number primary key);
insert into TEST values (999);
select * from TEST;

-- просмотр содержимого таблицы
select * from RECYCLEBIN;

-- восстановление таблицы TEST под именем TEST_OLD
flashback table TEST to before drop rename to TEST_OLD;
select * from TEST_OLD;

-- очистка таблицы
purge RECYCLEBIN;
select * from RECYCLEBIN;

Примеры схем БД

Нашел на сайте британской конторы Database Answers примеры схем различных БД.
Вот, к примеру, пример схемы БД плавательного бассейна или полицейского департамента.

Как переименовать таблицу или столбец

Переименование таблицы:

alter table OLD_TABLE rename to NEW_TABLE;

или короче
rename OLD_TABLE TO NEW_TABLE;


Переименование столбца:

alter table TEST_TABLE rename column OLD_COLUMN TO NEW_COLUMN;

UPSERT = UPdate or inSERT

UPSERT (от UPDATE OR INSERT)- условное наименование конструкции на языке SQL, которая
позволяет обновлять данные в таблице или вставлять их, если их еще не существует.

Пример:

UPDATE tablename
SET val1 = :p1,
val2 = :p2
WHERE val3 = :p3;

IF ( sql%rowcount = 0 )
THEN
INSERT INTO tablename
VALUES (:p1, :p2, :p3);
END IF;

обновит таблицу tablename, если val3 = :p3, или вставит новую запись в противном случае.

Советы по работе с sqlplus

1. При работе с sqlplus последний выполненный оператор всегда сохраняется в текстовый файл afiedt.buf. Можно использовать этот файл для того чтобы выполнить последнюю команду или подредактировать ее. Для этого нужно просто ввести в командной строке sqlplus команду edit (или просто ed). При закрытии окна редактора измененная команда автоматически станет текущей в sqlplus.

2. Выполняя SQL-операторы, не завершайте их точкой с запятой (;), а указывайте символ косой черты (/) в строке, следующей за оператором.

3. Можно сначала написать sql-запрос в тектовом файле, а потом загрузить его в sqlplus и выполнить. Для этого сохраните файл с расширением .sql и загрузите командой get или @, например:
1) сохраняем оператор в файл my_select.sql
2) выполняем команду

SQL>@my_select

4. Если Вам нужно сохранить выходные данные скрипта в файл, используйте команду SPOOL.
Пример:

SQL>spool out.txt
SQL>select * from DUAL

Результат запишется в файл out.txt

5. Для более удобного чтения результатов запросов можно использовать форматирование при помощи команды column. Например, операторы

SQL>column ID a3
SQL>column FIRSTNAME a12
SQL>column LASTNAME a12
SQL>select ID, FIRSTNAME, LASTNAME from EMPLOYEE

выведут результат в виде 3 колонок, первая из которых будет иметь ширину 3 символа, а вторая и третья – по 12 символов.