SQL

Всякие эскуэльные хитрости

Метасимвол "_" представлеют собой любой одиночный символ:

SELECT last_name
FROM employees
WHERE last_name IN '_o%'

---------
last_name
---------

Kochhar
Lorentz
Mourgos

Чтобы выбрать поля, действительно содержащие символ "_" нужно:


SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id LIKE %SA\_% ESCAPE '\'


------
job_id
------

SA_MAN
SA_REP
SA_REP
SA_REP

Summary only...

EXPLAIN PLAN и AUTOTRACE

EXPLAIN PLAN Usage
DBMS_XPLAN
Очень полезный скрипт explain.sql от товарисчей с oracle-base

cd $oracle_home/rdbms/admin
log into sqlplus as system
run SQL> @utlxplan
run SQL> create public synonym plan_table for plan_table
run SQL> grant all on plan_table to public
exit sqlplus and cd $oracle_home/sqlplus/admin
log into sqlplus as SYS
run SQL> @plustrce
run SQL> grant plustrace to public

Для включения AUTOTRACE (роли plustrace):

To use the AUTOTRACE feature of SQL*Plus, the user must have a PLAN_TABLE in the schema and have PLUSTRACE role granted.

AUTOTRACE configuration is required. Perform the following steps to configure the user for AUTOTRACE:
1. connect sys/change_on_install @$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to username;
2. connect username/password @$ORACLE_HOME/rdbms/admin/utlxplan.sql
set autotrace (run sql statement)

Summary only...