Posts Tagged ‘SQL’
11
Jan

After installing the database, you may want to create a schema with tables, indexes, etc., to start your project.

Create User / Schema with Privileges

Log on as user SYSTEM (e.g. in sqldeveloper) and execute the script

-- clean SCHEMA (only if created earlier, for dev only!)
DROP USER mh CASCADE;

-- create
CREATE USER mh IDENTIFIED BY pwd
  DEFAULT TABLESPACE users -- if this line is omitted, the database default tablespace will be used; after installation it's 'USERS', to check it: SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE'; (if you want another existing tablespace: SELECT * FROM user_tablespaces;)
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users; -- such that you can insert data, you can also limit this

-- grant priviliges to create/alter/drop objects
GRANT
  CREATE SESSION, -- to log on; equivalent to the role 'CONNECT'
  CREATE TABLE,
  CREATE VIEW,
  CREATE PROCEDURE,
  CREATE TRIGGER,
  CREATE SEQUENCE
TO mh;

-- though different, but shorter (also see remarks below)
-- GRANT CONNECT, RESOURCE TO mh;

Remarks:

  • if you have more users, create a role, and grant the role to the users
  • DROP and ALTER the named objects are implicitly granted (there exist no such privilege DROP TABLE)
  • the role RESOURCE provides the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. This role is provided for compatibility with previous releases of Oracle Database. You can determine the privileges encompassed by this role by querying the DBA_SYS_PRIVS data dictionary view.
  • Oracle recommends that you design your own roles for database security rather than relying on this role. This role may not be created automatically by future releases of Oracle Database.

Log off as SYSTEM, and log in as newly created user MH. Now you can create all objects with the user (fast setup for dev).

If you have more than one user, a better approach would be to run all scripts under an install user (SYSTEM would have enough priviliges ;-) ), but then don’t forget to name the objects fully qualified with schema names, e.g., CREATE schema.tablename (col1 numeric(20), …);.

Create Table with Primary Key and Foreign Key

We want to create a table with autoincrement primary key. This can be done in Oracle through a sequence and a “before insert” trigger. We additionally create a realistic environmet with foreign key and an index.

Suppose an already existing schema/user mh.

Note: A sequence does not guarantee a lückenlos Folge, i.e., we can get 1,2,3,7,10,11,…

CREATE TABLE mh.mh_table_2(
  id                 NUMERIC(20) PRIMARY KEY
);

CREATE TABLE mh.mh_table(
  id                 NUMERIC(20),
  name               VARCHAR2(255) NOT NULL,
  ts                 TIMESTAMP DEFAULT SYSTIMESTAMP,
  id_fk              NUMERIC(20),
  FOREIGN KEY (id_fk) REFERENCES mh.mh_table_2 (id),
  PRIMARY KEY (id)
);

A foreign key is not automatically indexed like a primary key! You have to declarate an index yourself, see below.

Create Sequence, Trigger

A sequence for thread-safely incrementing a value and trigger putting the autoincrement value of the sequence into mh_table.id when inserting row mh.mh_table.id:

CREATE SEQUENCE mh.mh_sequence
  START WITH 1
  MINVALUE 1
  -- MAXVALUE 9999999999999999999999999999 is default
  INCREMENT BY 1
  NOCYCLE -- is default: means after reaching the max, don't restart
  NOORDER -- is default: means requests are not put in order
  CACHE 100; -- is default with 20: number of next values that are hold in cache, for performance

CREATE OR REPLACE TRIGGER mh.mh_trigger
BEFORE INSERT ON mh.mh_table FOR EACH ROW
DECLARE
BEGIN
  -- if condition: when manually id is set, then do nothing (if this makes sense in your context)
  IF :NEW.id IS NULL THEN
    SELECT mh.mh_sequence.NEXTVAL INTO :NEW.id FROM dual;
  END IF;
END;
/

Don’t forget the slash at the end!

Create Index (unique, bitmap, function based)

The foreign key mh_table.id_fk may need an index, as well as mh_table.name may need a function based index (with the funny name FBI) to support performant search on the first letter:

CREATE INDEX mh.idx_mh_table_id_fk ON mh.mh_table (id_fk);
-- or if this column needs a unique constraint
CREATE UNIQUE INDEX ...;
-- or if this column only has a few (say 100) different values (like status ids)
CREATE BITMAP INDEX ...;

CREATE BITMAP INDEX mh.idx_mh_table_name_firstletter ON mh.mh_table (SUBSTR(name, 1,1));

The latter should contain only the 26 letters of the alphabet, hence we choose the BITMAP version (only available in Enterprise Edition).

Gather Table Statsitics

If this table would already have have data, you should gather table statistics (Oracle cost based optimizer (CBO) uses these),

parameter “cascade=>TRUE”, not only stats on columns, but also on indexes (it’s not clear to me, what it does in detail internaly)

exec dbms_stats.gather_table_stats(OWNNAME=>'MH', TABNAME=>'MH_TABLE', cascade=>TRUE);
-- or if you want to update only a certain index:
exec dbms_stats.gather_index_stats(OWNNAME=>'MH', INDNAME=>'IDX_[...]');

, , , ,