Category Archives: SQL

PL/SQL – indexes

Indexes are used for:
* create primary key constraint
* create unique constraint
* sort results
* filter out results
* join datasets
* more…

Oracle as a lookup field uses it’s pseudocolumn rowid. This column is not visible by default, but every row has got it’s rowid. It is globally unique.
select rowid, hr.countries.* from hr.countries;

Types of indexes:
* B*Tree index – balanced tree
This index is very efficient when you try to select up to 4% of total numer of rows in the table. If your where clause selects more rows it’s faster to do full table scan.
Nulls are not handled in this index, so a query ‘… where name is null’ is executed without using index – full table scan.
Create B*Tree index when there are many rows in the table, cardinality (number of distinct values) in a column is high and column is used in join or where clause.

* Bitmap index
This index should be used when column’s cardinality is small (i.e. sex, days in year), column is used in boolean algebra (AND, OR, NOT, ==, !=) and the number of rows is high.

create index JOB_HISTORY_START_DATE_IX on hr.job_history (start_date); -- default nonunique B*Tree index
create unique index JOB_HISTORY_JOB_ID_IX on hr.job_history (job_id); 
create bitmap index JOB_HISTORY_DEP_ID_IX on hr.job_history (department_id); -- bitmap indexes available in enterprise edition
create index JOB_HISTORY_DATES_IX on hr.job_history (start_date, end_date); -- composite index on two columns
create index PRODUCT_TOTAL_IX on hr.product (price * qty); -- function based index
drop index JOB_HISTORY_START_DATE_IX; -- deleting the index

PL/SQL – sequences

Create and use sequence:

CREATE SEQUENCE hr.myseq
INCREMENT BY 1
START WITH -100
MAXVALUE 200
MINVALUE -200
CYCLE
CACHE 100; -- how many next values should be prepared and ready to use

select hr.myseq.nextval from dual; -- selects next unique value
select hr.myseq.currval from dual; -- selects last produced value, but only in current session!

Sequence can be altered, but one property cannot be changed in alter command – it is starting value.

PL/SQL – views

Create, use, delete view:

create or replace force view hr.euro_countries -- force: create even when table does not exist
as 
select * from hr.countries
where region_id = 1
with check option; -- do not allow DML for rows which are not visible in this view
--with read only -- do not allow any DML

select * from hr.euro_countries;

drop view hr.euro_countries;

PLSQL – constraints

There are two ways to create constraints:
a) when creating a table – inline with the column,
b) after creating a table – more flexible.

create table hr.people
(
id NUMBER(10) constraint people_id_pk PRIMARY KEY constraint people_id_ck CHECK (id >= 0),
first_name VARCHAR2(30 CHAR) constraint people_fn_nn NOT NULL,
last_name VARCHAR2(30 BYTE) constraint people_ln_ck CHECK (last_name is not null),
local_hello NVARCHAR2(50) constraint people_lh_uq UNIQUE, 
height NUMBER(3,2) constraint people_height_ck CHECK (height >= 1.5) DISABLE,
best_skill NUMBER(1) constraint people_bs_fk references skills(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
knowledge_level NUMBER(1) constraint people_kl_fk references technology(id) on delete set null,
hobby NUMBER(1) constraint people_hobby_fk references hobby(id) on delete set null
);

alter table hr.people
enable constraint people_height_ck;

alter table hr.people
add constraint people_fn_ck check (LENGTH(first_name) > 2);

alter table hr.people
drop constraint people_fn_ck;

alter table hr.people
modify local_hello constraint people_lh_nn NOT NULL;

When Primary Key or Unique constraint is created, index is build automatically.
Primary Key is combination of NOT NULL and UNIQUE constraints.

Not null constraint is translated to CHECK(column_name IS NOT NULL).

When Foreign Key is created index is not created, you should do it on your own as it is good practice.

Deferrable means that constraint is not checked directly in time of insert or update. It is checked at the end of the transaction. It can solve chicken and egg like problems.

On delete set null means that when parent of this value (foreign key relationship) is deleted value will be set to null.
On delete cascade means that row will be deleted when parent value is deleted.

PL/SQL – NVARCHAR2 vs. VARCHAR2

I’ve been reading about differences between NVARCHAR2 and VARCHAR2 and generally people say that NVARCHAR2 should be used when someone wants to have multilingual data in a column. I played a little bit with it and I had some doubts still.

Doubt no. 1
Imagine I crated simple table to play with:

create table hr.test1
(
col1 VARCHAR2(3 CHAR)
);

Then I inserted a few rows with texts using different languages (german, japanese, russian):

insert into hr.test1 values ('こんに');
insert into hr.test1 values ('Здр');
insert into hr.test1 values ('aßö');
commit;

select * from hr.test1;

And I see that I have correct multilingual result without using NVARCHAR2:
COL
こんに
Здр
aßö

So when I really should use NVARCHAR2, if this example works well with VARCHAR2?
Answer: There is no difference whether you use NVARCHAR2 or VARCHAR2 when default character set of the database is set to multi-byte character set (i.e. UTF-8). The only one difference can be the length in bytes for the same strings. Continue reading, please…

Doubt no. 2:
Imagine I’ve crated simple table to play with:

create table hr.test2
(
col1 VARCHAR2(3 CHAR),
col2 NVARCHAR2(3)
);
insert into hr.test2 values ('asf', 'asf');
insert into hr.test2 values ('Здр', 'Здр');
insert into hr.test2 values ('こんに', 'こんに');
commit;
select LENGTHB(col1), LENGTHB(col2) from hr.test2;

Result is:
LENGTHB(COL1) LENGTHB(COL2)
3 6
6 6
9 6

Why there are such differences in byte-length? Why for japanese chars NVARCHAR2 took less bytes, but for english chars VARCHAR2 took less?
It is due to the fact that default character set is ‘AL32UTF8’ (multi-byte one) and NVARCHAR2 uses fixed-length bytes for every character. In this case always any character stored in NVARCHAR2 takes 2 bytes and it do not depend on language used. This is opposite to VARCHAR2.

To check your character set execute:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

PL/SQL – create and alter table

Creating tables

--Create simple table with heap organization:
create table hr.people
(
id NUMBER(10) NOT NULL, -- any integer up to 10 places
first_name VARCHAR2(30 CHAR) NOT NULL, -- text up to 30 characters (single non english character can take more than one byte)
last_name VARCHAR2(30 BYTE) NOT NULL,  -- text up to 30 bytes
local_hello NVARCHAR2(50), -- unicode text, multilingual data (it means you can mix in this column different languages - including english like and non english exotic asian languages)
height NUMBER(3,2), -- any number up to 3 digits where up to two are on the right of comma and 3-2 are on the left
skills_level NUMBER(1) DEFAULT 3, -- default must be constant value, cannot base on function
knowledge_level NUMBER(1) DEFAULT 3,
final_level as (skills_level * knowledge_level) -- function-based virtual column
);

Notice that when you can insert into height column value 1.7554432 (it will be rounded), but you cannot insert 10.7 (it will fail).

--Create table copy (DDL + DML):
create table hr.people2 as select * from hr.people;

--Create table copy (DDL only):
create table hr.people3 as select * from hr.people where 1=2;

Altering tables

alter table hr.people add profession nvarchar2(20);
alter table hr.peple modify height number(4, 3) NOT NULL;
alter table hr.people rename column height to height_cm;
alter table hr.people drop column final_level; -- can be time consuming as every row must be rebuilt
alter table hr.people set unused column first_name; -- do not removes column, but it won't be available in queries, views, etc. Use when dropping column can take long time.
alter table hr.people drop unused columns; -- it should follow a few 'set unused' commands when it is allowed to consume some resources to clean up not needed columns.
alter table hr.people read only; -- table will reject any modifications, alter table privilege is needed for this
alter table hr.people read write; -- will turn off read only mode
drop table hr.people; -- it will delete the table (also if it is in read only mode)

For more information about differences between NVARCHAR2 and VARCHAR2 and encoding go to:

PL/SQL – transactions

Transactions in Oracle are controlled using a few key words:
BEGIN – starts
SAVEPOINT name – creates a marker which can be used to roll back part of transaction only
COMMIT – accepts
ROLLBACK – rejects everything
ROLLBACK TO SAVEPOINT name – rejects changes up to savepoint

Transaction also can be closed (commited or rejected) when DDL or DCL command is executed (commit is executed), session is killed, db crash ocurred, … (rollbacks).

You can decide whether your tool (SQLPLUS, SQLDeveloper) commits transaction for you. By default you need to commit on your own. To change this use SET AUTOCOMMIT ON.

--simple transaction commited
begin
insert into hr.regions values (100, 'Region A');
insert into hr.regions values (101, 'Region B');
commit;
end;

--commit or rollback simple transaction
declare
regions_count number := 0;
begin
insert into hr.regions values (103, 'Region C');
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 15 
        then rollback; 
        else commit;
    end if;
end;

--rolling back to savepoint
declare
regions_count number := 0;
begin
insert into hr.regions values (103, 'Region C');
savepoint after_a;
insert into hr.regions values (104, 'Region D');
select count(*) into regions_count from hr.regions;
    if regions_count > 5 
        then rollback to savepoint after_a; --will rollback only one insert
    end if;
commit; -- the second insert will be commited
end;