-- create trigger to track changes made to countries table CREATE OR REPLACE TRIGGER trg_track_countries before insert or update of country_name, region_id or delete ON countries FOR EACH ROW BEGIN CASE WHEN INSERTING THEN dbms_output.put_line('Adding country ' || :new.country_name); WHEN UPDATING('country_name') THEN dbms_output.put_line('Changing country name from ' || :old.country_name || ' to ' || :new.country_name); WHEN UPDATING('region_id') THEN dbms_output.put_line('Changing region id from ' || :old.region_id || ' to ' || :new.region_id); WHEN DELETING THEN dbms_output.put_line('Removing country ' || :old.country_name); END CASE; END; / -- test trigger insert into COUNTRIES (country_id, country_name, region_id) values (98, 'xxm', 1); update COUNTRIES set country_name = 'mmx' where country_name = 'xxm'; delete COUNTRIES where country_name = 'mmx'; --disable trigger (or all triggers for table) alter trigger trg_track_countries disable; alter table countries disable all triggers;
Indexes are used for:
* create primary key constraint
* create unique constraint
* sort results
* filter out results
* join datasets
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
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.
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;
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.
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:
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;
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';
--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;
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: