SQL Quick reference

Base of Oracle SQL
§ Data Retrieval
§ DDL (Data Definition Language)
§ DML (Data Manipulation Language)
§ TCL (Transaction Control Language)
§ DCL (Data Control Language)
Data Retrieval
§ SELECT
DDL
§ CREATE
§ ALTER
§ DROP
§ RENAME
§ TRUNCATE
DML
§ UPDATE
§ DELETE
§ INSERT
§ MERGE
TCL
§ COMMIT
§ ROLLBACK
§ SAVEPOINT
DCL
§ GRANT
§ REVOKE

Operators in Oracle
§ Arithmetic Operator (+, -, *, /)
§ Concatenation Operator ()
§ Comparison Operator (>, >=, <, <=, <>) (Between, In, Like, IS NULL)
§ Logical Operator (Not, And, Or)

SQL Functions
Two Types of function
§ Single Row Function
§ Multiple Row Function

Single Row Functions
§ Character
§ Number
§ Date
§ Conversion

Character Functions
§ LOWER - Converts any case to Lower Case
§ UPPER - Converts any case to Upper Case
§ INITCAP - Converts First letter of the word to caps
§ CONCAT - Joins Values together
§ SUBSTR - Extracts a string of determined Length
§ LENGTH - Returns the length of the string
§ INSTR - Finds the numeric position of a named character
§ LPAD RPAD - Pads the character values in right and left side
§ TRIM - Trims Heading or Trailing Characters from the string
§ REPLACE - Find the occurrence of a string and replace with other
§ TRANSLATE - Translates the string from a to b.

Number Function
§ ROUND - Rounds Value to specified decimal
§ TRUNC - Truncates Value to specified decimal
§ MOD - Returns remainder of division
§ ABS - Returns the absolute value
§ CEIL - Returns the smallest integer greater than or equal to value
§ FLOOR - Returns the largest integer less than or equal to value
§ POWER - Returns x to the power of Y
§ SQRT - Square Root of the Value
§ SIGN - Returns –1 if x<0,>0 0 if x=0
§ GREATEST - Returns the greatest value among the ‘n’ parameters
§ LEAST - Returns the least value among the ‘n’ parameters

Date Functions
§ Oracle database stores dates in an internal numeric format: Century , Year, Month, Day, Hours, Minutes, Seconds.
§ Default is DD-MON-RR
§ Limits are Jan 1, 4712 B.C. to Dec 31, 9999 A.D.
§ SYSDATE is a function returns Date and Time.

Functions
§ MONTHS_BETWEEN - No of Months Between two dates
§ ADD_MONTHS - Add Calendar Months to date
§ NEXT_DAY - Next day of the specified date
§ LAST_DAY - Last Day of the month
§ ROUND - Round Date
§ TRUNC - Truncate Date

Conversion Functions

Data Type Conversion
Implicit Data-Type Conversion
Explicit Data-Type Conversion

Implicit Data Type Conversion
For Assignments, the oracle server can automatically convert the following
Varchar2 or Char - > Number
Varchar2 or Char - > Date
Number - > Varchar2 or Char
Date - > Varchar2 or Char

Explicit Data Type Conversion
To_Char - Converts a Number or Date value to a varchar2 character string with format model fmt
To_Number - Converts a Character string Containing digits to a number.
To_Date - Convert a Character string to Date value according to FMT.

Elements of the Date Format Model
YYYY - Full years in Numbers
Year - Year Spelled Out
MM - Two Digit Value For Months
MONTH - Full Name of Month
MON - Three letter abbreviation of the Month
DY - Three letter abbreviation of the day of the Week
DAY - Full Name of the day of the week
DD - Numeric day of the month


General Functions
NVL
Converts Null Value to an actual Value
NVL2
If exp1 is not null then returns exp2 else return exp3.
NULLIF
Compares two expressions and returns null if they are equal or first expression if they are not equal
COALESCE
Returns the first non null expression in the list.

Conditional Expressions

These two Methods are used to implement the (IF-THEN-ELSE Logic) they are
CASE Expression
DECODE function

CASE Expression

CASE expr WHEN comparison_exp1 THEN return_exp1
[WHEN comparison_exp1 THEN return_exp1
WHEN comparison_expn THEN return_expn
ELSE else_expr]
END

Example

SELECT ename,
CASE job
WHEN 'CLERK'
THEN 1.1 * sal
WHEN 'SALESMAN'
THEN 1.3 * sal
ELSE sal
END "REVISED SALARY"
FROM emp;

DECODE Function

DECODE (colexpression, Search1, Result1,
[,Search2, Result2,…,]
[, default]);
Example
SELECT ename, sal,
DECODE (job,
'CLERK', 1.1 * sal,
'SALESMAN', 1.3 * sal,
sal
) "REVISED SALARY"
FROM emp

Oracle JOINS
When we want to join one or more table we use the features of oracle joins
Oracle Joins (8i and Prior)
SQL: 1999 Compliant Joins
§ Equijoin
§ Nonequijoin
§ Outer Join
§ Self Join
§ Cross Joins
§ Natural Joins
§ Using Clause
§ Full or Two sided outer joins
§ Arbitrary Join Conditions for outer joins

Equijoin
Equijoins are also called as simple join or inner join
Joining two tables using a common column with Equal to operator
Syntax
SELECT t1.c1, t2.c1
FROM t1, t2
WHERE t1.c1 = t2.c2
Example
SELECT ename
FROM emp e, dept d
WHERE e.deptno = d.deptno

If you are using n tables in a query, there must be a minimum of n-1 joins

Nonequijoin
A nonequijoin is a join condition containing something other than an equality operator.
Example
SELECT ename
FROM emp e, job j
WHERE e.sal BETWEEN j.low_sal AND j.high_sal

Outer Joins
An outer join is a join which is used to see the rows that do not meet the join condition
Example
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);
Select all the row from emp table with rows satisfying the condition

SELECT *
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
Select all rows from dept table with rows satisfying the condition

Self Joins
Some time you need to join a table to itself.
Example
SELECT e.ename, e.mgr, m.empno
FROM emp e, emp m
WHERE e.mgr = m.empno

SQL: 1999 JOINS (oracle 9i and above as per ASCII standard)

SELECT T1.COL, T2.COL
FROM T1
[CROSS JOIN T2]
[NATURAL JOIN T2]
[JOIN T2 USING (column name)]
[JOIN T2
ON (T1.COL = T2.COL)]
[LEFTRIGHTFULL OUTER JOIN T2
ON (T1.COL = T2.COL)];

CROSS JOIN - Return a Cartesian product from the two tables.
NATURAL JOIN - Joins two tables based on the same column name
JOIN TABLE
USING COLUMN NAME - Performing an equijoin based on the column name
JOIN TABLE
ON T1.COL = T2.COL - Performing an equijoin based on the condition in the on clause
LEFT/RIGHT/FULL OUTER

Joins: Comparing SQL : 1999 to Oracle Syntax

Oracle
SQL: 1999
Equijoin
Natural or Inner Join
Outerjoin
Left Outer Join
Selfjoin
Join on
Nonequijoin
Join using
Cartesian Product
Cross Join



Aggregating Data Using Group Function
Group function operate on sets of rows to give one result per group
Function
Description
AVG ([DISTINCTALL] n)
Average value of n, ignoring null values
COUNT ((*[DISTINCTALL] EXP)
Number of rows where expr evaluates to something other than null
MAX ([DISTINCTALL] EXP)
Maximum value of expr, ignoring null values
MIN ([DISTINCTALL] EXP)
Minimum value of expr, ignoring null values
STDDEV ([DISTINCTALL] *)
Standard Deviation of n, ignoring null values
SUM ([DISTINCTALL] n)
Sum values of n, ignoring null values
VARIANCE ([DISTINCTALL] *)
Variance of n, ignoring null values

Advance Select

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]

GROUP BY clause used to divide the rows in a table into groups. You can use the group functions to return summary information for each group.
HAVING clause to specify which group are to be displayed, and thus, you further restrict the group on the basis of aggregate information.
SUB QUERY
A subquery is a SELECT statement that is embedded in a clause of another SELECT Statement. You can build powerful statements out of simple ones by using subqueries.
You can place the sub query in a number of SQL clauses, including :
§ WHERE Clause
§ HAVING Clause
§ FROM Clause
Syntax:
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

§ The subquery (Inner query) executes once before the main query.
§ The result of the subquery is used by the main query(Outer Query).
In syntax
Oper includes a comparison
§ Single row operator (>, =, >=, <, <>, <=) § Multiple row operator (IN, ANY, ALL) Example: select * from emp where sal>(select sal from emp where ename = 'JONES');

§ Encloses subqueries with paranthesis
§ Place subqueries on the right side of the comparison condition
§ The ORDER BY clause in the subquery is not needed unless you are performaning top n analysis
§ Single row operator for single row subqueries and use multiple row for multiple row subquery
Types of Subqueries
§ Single Row Subqueries: Queries that return only one row from the inner SELECT statement
§ Multiple Row Subqueries: Queries that return more than one row from the inner SELECT statement

SINGLE ROW SUBQUERIES
Operator
Meaning
=
Equal To
>
Greater Than
>=
Greater Than Equal to
<>
Not Equal To

Multiple-Row Subqueries
Operator
Meaning
IN
Equal to any member in the list
ANY
Compare value to each value returned by the subquery
ALL
Compare value to every value returned by the subquery
Example:
select * from emp
where sal < job =" 'SALESMAN')"> 'SALESMAN';

§ ANY means more than the minimum
§ =ANY is equivalent to IN
§ ALL means more than the minimum

Data Manipulation Language

INSERT
INSERT INTO table[(column [, column…])]
VALUES (value [, value…]);
Copy Rows from other Table
INSERT INTO table [column (, column) ] subquery;
With Check Option
WITH CHECK OPTION Keyword prohibits you from changing rows that are not in the subquery.
Example
Insert into (select empno,sal from emp where deptno = 50 with check option) values (7788,5000)
Insert into emp(empno) select 1 from dual;
DEFAULT
DEFAULT option is used to set the column to the value previously specified as the default value.

UPDATE
UPDATE table
SET column = value [, column = value,…]
[WHERE condition];

DELETE
DELETE [FROM] table
[WHERE condition];
MERGE
You can conditionally insert or update rows in a table by using the MERGE statement.
Syntax

MERGE INTO table_name as table_alias
USING (tableviewsub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
Col1 = col1_val,
Col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column list)
VALUES (column values);

INTO clause specifies the target table you are updating or inserting into
USING clause identifies the source of the data to be updated or inserted; can be a table, view or subquery
ON clause the condition upon which the merge operation either updates or inserts
WHEN MATCHED Instruct the server how to respond to the results of the join condition
WHEN NOT MATCHED

Example
MERGE INTO t e
USING emp x
ON (e.empno = x.empno)
WHEN MATCHED THEN
UPDATE SET e.empno = (x.empno +100)
WHEN NOT MATCHED THEN
INSERT VALUES(9090,'hai','manager',7788,'12-may-1995',5000,10,30,'P');


DDL and DCL statements need no commit or Rollback.
We have to commit or rollback explicitly based on the save point.

Read Consistency

Oracle LOCK
Lock is a mechanisms nthat prevents destructive interaction between transactions accesing the same resource.
There are two types of LOCK they are
§ IMPLICIT Lock
The lock requires no user action. Implicit lock occurs for all SQL statements except SELECT.
Two types of lock Modes
- Exclusive : Locks out other users
- Share : Allows other users to access the server
§ EXPLICIT Lock
User can lock the resource explicity.
High level of data concurrency
§ DML: Table Share, Row Exclusive
§ Queries: No Locks required
§ DDL: Protects object definitions
Lock held until commit or rollback.

Database Objects
An oracle database can contain multiple data structures. Each structure should be outlined in the database design so that it can be created during the build stage of database development.


Object
Description
TABLE
Basic unit of storage; Composed of rows and columns; Stores data
VIEW
Logically represents subsets of data from one or more tables.
SEQUENCE
Numeric Value Generator
INDEX
Improves the performance of some queries
SYNONYM
Gives alternative names of objects
TABLE
§ You must have CREATE TABLE privilege and storage area
Syntax
§ CREATE TABLE [schema.] Table_Name
(column datatype [DEFAULT expr] [, …]);
§ You Specify
- Table Name
- Column name, column data type and column size
In syntax
Schema is the same as the owners name
Table is the name of the table
DEFAULT expr specifies a default value if a value is omitted in the INSERT statement
Column is the name of the column
Datatype is the columns data type and length
Tables in the oracle database
§ User Tables
- Are a collection of tables created and maintained by the user
- Contain user information
§ Data Dictionary
- Is a collection of tables created and maintained by the oracle server
- Contain database information\
- There are 4 types Tables/Views of data dictionary
Prefix
Description
USER_
These view contain information about objects owned by the user
ALL_
These views contain information about all of the tables accessible to the user
DBA_
These views are restricted views, which can be accessed only by people who have been assigned the DBA role.
V$
These views are dynamic performance views, database server performance, memory and locking.
ALTER TABLE
Use the ALTER TABLE statement to:
§ Add a new column
§ Modify an existing column
§ Define a default value for the new column
§ Drop Column
Syntax

ALTER TABLE table
ADD/MODIFY (column datatype [DEFAULT expr]
[, column datatype]…);

ALTER TABLE table
DROP (column);

SET UNUSED Option
§ You use the SET UNUSED option to mark one or more columns as unused
§ You use the DROP UNUSED COLUMNS option to remove the columns that are marked as unused
Syntax

ALTER TABLE table
SET UNUSED (column);
or
ALTER TABLE table
SET UNUSED COLUMN column;

ALTER TABLE table
DROP UNUSED COLUMNS

Drop Table
§ All data and structure in the table is deleted
§ Any pending transactions are commited
§ All Index are dropped
§ You cannot rollback the DROP table statement
Syntax
DROP TABLE table name;

RENAME
§ To change the name of the table, view, sequence or synonym , execute the RENAME Statement.
§ You must be the owner of the object
Syntax
RENAME object_name to new_object_name;

TRUNCATE
§ Removes all rows from a table
§ Releases the storage space used by that table
§ You cannot roll back row removal when using TRUNCATE
Syntax
TRUNCATE TABLE table_name;

Comments To the Table
You can add comments to a table or column by using the comment statement.Syntax
COMMENTS ON TABLE table COLUMN table. column
IS ‘text’;

Constraints
The oracle server uses constraints to prevent the invalid entry into tables.
§ Enforce rules on the data in a table whenever a row is inserted, updated or deleted from that table. The constraints must be satisfied for the operation to succeed.
§ Prevent the deletion of a table if there are dependencies from other tables
Data Integrity Constraints
Constraint
Description
NOT NULL
Specifies that the column cannot contain a null value.
UNIQUE
Specifies a column or combination of columns whose values must be unique for all rows in the table
PRIMARY KEY
Uniquely identifies each row of the table
FOREIGN KEY
Establishes and enforces a foreign key relationship between column and a column of the referenced table
CHECK
Specifies a condition that must be true

Constraint Guidelines
§ Oracle server generates a name by using the SYS_Cn format.
§ Create a constraint either
o At the same time as the table is created
o After the table has been created
§ Define a constraint at the column or table level.
§ View a constraints in a data dictionary (USER_CONSTRAINTS)

Syntax
CREATE TABLE [schema.] table
(Column datatype [DEFAULT expr]
[column constraint],
…
[table_constraint] [,…]);

Example
CREATE TABLE t1(
Empno number(6),
Job_id varchar2(10) not null,
CONSTRAINTS t1_const primary key(empno));

Defining Constraints
§ Column constraint level
Column [CONSTRAINT constraint_name] constraint_type,
§ Table constraint level
Column,…
[CONSTRAINT constraint_name] constraint_type
(column, …),

The NOT NULL Constraint
§ The NOT NULL constraint ensures that the column contains no null values. Column without the NOT NULL constraint can contain null values by default.
§ The NOT NULL constraint can be specified only at the column level, not at the Table level.
The UNIQUE Constraint
§ A UNIQUE Constraint requires every value in a column or set of columns to be unique. If the unique constraint comprises more than one column that group of columns is called composite unique key.
§ UNIQUE Constraint allows the input of Nulls.
§ Unique constraint can be defined at the column or table level. A composite unique key is created by using the table level definition.
§ Oracle enforces implicitly create a unique index on unique key column or columns
The PRIMARY KEY Constraint
§ A primary key constraint creates a primary key for the table.
§ Only one primary key for each table.
§ Enforces uniqueness of the column or column combination
§ Primary key cannot contain NULL Values.
§ Primary key can be defined at the column level and table level. A composite primary key can be created by using the table level definition.
§ A UNIQUE index is created automatically for a primary key column.
The FOREIGN KEY Constraint
§ The foreign key or referential integrity constraint designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.
§ A foreign key value must match an existing value in the parent table or be null.
§ Foreign key are based on data values and are purely logical, not physical, pointers.
§ Foreign key can be defined at the column level and table level. A foreign primary key can be created by using the table level definition.

Foreign Key Constraint key words
§ FOREIGN KEY : Defines the column in the child table at the table constraint level.
§ REFERENCES : Identifies the table and column in the parent table.
§ ON DELETE CASCADE : Deletes the dependent rows in the child table when a row in the parent table is deleted.
§ ON DELETE SET NULL : Converts dependent foreign key value to null.
The CHECK Constraint
§ Defines a condition that each row must satisfy
§ The follwing expression are not allowed:
o reference to the curval,nextval,level and rownum pseudocolumns
o Calls to sysdate, UID, USER and USERENV functions
o Queries that refer to other values in other rows
§ There is no limit to the number of check constraints at the column level or table level.
§ Check constraints are defined at both column level and table level.
ALL Constraint Examples
CREATE TABLE temp(empno NUMBER(6) PRIMARY KEY,
empname VARCHAR2(50) NOT NULL,
emailid VARCHAR2(100),
sex VARCHAR2(1),
deptno NUMBER(4),
CONSTRAINT pk_const_mailid UNIQUE(emailid),
CONSTRAINT pk_const_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno),
CONSTRAINT pk_const_sex CHECK (sex IN ('M','F'))
);

Adding a constraint Syntax
§ Use the ALTER table statement to
§ Add or drop a constraint, but not modify its structure.
§ Enable/ Disable Constraints
§ Add a not null constraint by using the MODIFY clause
Syntax
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
Dropping a Constraint
Remove the manager constraint from the table.
Syntax
ALTER TABLE table
DROP PRIMARY KEY UNIQUE (column)
CONSTRAINT constraints [CASCADE];
Disabling Constraints
§ Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.
§ Apply the cascade option to disable depend integrity constraints.
Syntax
ALTER TABLE table
DISABLE CONSTRAINT constraint_name [CASCADE];
Enabling Constraints
§ Activate an integrity constraint currently disabled in the table definition by using the enable clause.
Syntax
ALTER TABLE table
ENABLE CONSTRAINT constraint_name;
CASCADING Constraint
§ The CASCADE CONSTRAINTS clause is used along with the DROP Column clause.
§ The CASCADE CONSTRAINTS clause drop all referential integrity that refer to the primary and unique keys defined on the dropped columns.
§ The CASCADE CONSTRAINTS clause also drops all multicolumn constraints defined on the dropped columns.
Data Dictionary About Constraints
USER_CONSTRAINTS;
USER_CON_CONSTRAINTS

VIEWS
A View is a logical table based on a table or another view.
A view Contain no data of its own.
The view is stored as a select statement in the data dictionary.

USE of VIEW
§ To restrict data access.
§ To make complex query easy.
§ To provide data independence.
§ To present different view of the same data.
Simple Views and Complex Views
Feature
Simple Views
Complex Views
Number of Tables
One
One or More
Contains functions
No
Yes
Contain groups of data
No
Yes
DML operations through a view
Yes
Not always
Syntax
CREATE [OR REPLACE] [FORCENOFORCE] VIEW view
[(alias[, alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]
[WITH READ ONLY [CONSTRAINT constraint];

or replace re-creates the view if it already exist
force Creates the view regardless of whether or not the base tables exist.
Noforce Creates the view only if the base table exist.(By default)
With check option Specifies that only rows accessible to the view can be inserted or updated
Constraint Name assigned to check option constraint
WITH READ ONLY ensures that no dml operation can be performed on this view.
The subquery that defines the view cannot contain an order clause.
Example
CREATE view v_test as select * from emp;
Select * from v_test;
Data Access Using View
§ It retrieves the view definition from the data dictionary table USER_VIEWS.
§ It checks for the access privileges for the view base table.
§ It converts the view query into an equivalent operation on the underlying base tables or other tables.
Rules for performing DML operations on a view
§ You can perform DML Operations on simple views
§ You cannot remove/ Modify a row if the view contains the following:
o Group Function
o A group by clause
o The DISTINCT Keyword
o The pseudcolumn ROWNUM keyword
o Columns defined by expressions
o You cannot add data to NOT NULL columns in the base tables that are not selected by the view
Inline Views
§ An inline view is a subquery with an alias ( or correlation name) that you can use with in a sql statement.
§ A name subquery in the FROM Clause of the main query is an example of an inline view.
§ A inline view is not a schema object.
Example
SELECT e.empno, e.ename, e.sal, b.deptno, b.maxsal
FROM emp e, (SELECT deptno, MAX (sal) maxsal
FROM emp a
GROUP BY deptno) b
WHERE e.deptno = b.deptno AND e.sal < name="OLE_LINK3">INCREMENT BY 10
START WITH 100
MAXVALUE 9999
NOCACHE
NOCYCLE;
- Do Not use the CYCLE option if the sequence is primary key
- Data Dictionary for Sequence is USER_SEQUENCES
NEXTVAL and CURRVAL Pseudocolumns
§ NEXTVAL returns the next available sequence value.
§ It returns a unique value every time it is referenced even for different users.
§ CURRVAL obtains the current sequence value.
§ NEXTVAL must be issued for that before CURRVAL contain value.
Rules for using NEXTVAL and CURRVAL
You can use NEXTVAL and CURRVAL in the following contexts:
§ The SELECT list of a SELECT statement that is not part of a subquery.
§ The select list of a subquery in an INSERT statement
§ The VALUES clause of an UPDATE statement
§ The SET clause of an UPDATE statement.
You cannot use NEXTVAL and CURRVAL in the following contexts:
§ The SELECT list of view
§ A SELECT statement with DISTINCT keyword
§ A SELECT Statement with group by, Having or ORDER by clauses
§ A subquery in a select, delete or UPDATE statement.
§ The DEFAULT expression in a create table or Alter table statement
Using a Sequence
§ Caching sequence values in memory gives faster access to those values.
§ Gaps in sequence values can occurs when:
o A rollback occurs
o The system crashes
o A sequence used in another table.
Alter Sequence
Change the increment value, maximum value, minimum value, cycle option or cache option.
Example
ALTER SEQUENCE seq_name
INCREMENT BY 10
START WITH 100
MAXVALUE 9999
NOCACHE
NOCYCLE;
Guidelines for modifying Sequences
§ You must be the owner or have ALTER privilage for the sequence.
§ Only future sequence numbers are affected.
§ The sequence must be dropped and recreated to restart the sequence at a different number.
§ Some validation is performed
Drop Sequence
DROP SEQUENCE seq_name;

INDEX
§ Is a schema object.
§ Is used by the oracle server to speed up the retrieval of rows by using a pointer.
§ Can reduce disk I/O by using a rapid path access method to locate data quickly.
§ Is independent of the table its indexes.
§ Is used and maintained automatically by the oracle server.
§ If no Index then it will go for Full Table scan
§ When we drop table, corresponding indexes are also dropped.
Type of index Creation
§ Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
§ Manually: Users can create nonunique indexes on columns to speed access to the rows.
Creating an Index
Syntax
CREATE INDEX index_name
ON table (column[, column]…);
Example
CREATE INDEX email_idx
ON emp(emailed);
When to Create an Index
§ A column contains a wide range of values
§ A column contains a large number of null values
§ One or more columns are frequently used together in a WHERE clause or a join condition
§ The table is large and most queries are expected to retrieve less than 2 to 4% of the rows
When not to create an index
§ The table is small
§ The columns are not often used as a condition in the query
§ Most queries are expected to retrieve more that 2 to 4% of the rows in the table.
§ The table is updated frequently
§ The indexed columns are referenced as part of an expression
Data Dictionary
§ USER_INDEXES
§ USER_IND_COLUMNS
Function –Based Indexes
§ A function-based index is an index based on expressions.
§ The index expression is built from table columns, constants, SQL functions, and user-defined functions.
Example
CREATE INDEX upper_name_idx
On dept(UPPER(dept_name));
Removing an Index
DROP INDEX index_name;
SYNONYMS
Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can:
§ Ease referring to a table owned by another user
§ Shorten lengthy object names
Syntax
CREATE [PUBLIC] SYNONYM synonym
FOR object;

Drop Synonyms
DROP SYNONYM Synonym_name;
Controlling User objects
Controlling User objects
§ Control database access
§ Give access to specific objects in the database
§ Confirm given and received privileges with the oracle data dictionary
§ Create synonyms for database objects
Privileges
§ Database security
o System Security
o Data Security
§ System privileges: Gaining access to the database
§ Object privileges: Manipulating the content of the database objects
§ Schema: collections of objects, such as tables, view and sequences
System Privileges
· More than 100 privileges are available
· The database administrator has high level system privileges for task such as:
- Creating new users
- Removing new users
- Removing Tables
- Backup Tables
Creating Users
The DBA creates users by using the CREATE USER Statement.
Syntax
CREATE USER user_name
IDENTIFIED BY password;
Example
CREATE USER deep
IDENTIFIED BY o;
Change password
We can change the password once the DBA have created the user.
Example
ALTER USER deep IDENTIFIED BY oo
User System Privileges
Once a user is created, the DBA can grant specific system privileges to a user
Syntax
GRANT privilege [, privilege]
TO user [, user role, PUBLIC];
Example
GRANT CREATE SESSION, CREATE TABLE,CREATE SEQUENCE, CREATE VIEW
TO deep;
ROLE
A role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges.
A user can have access to several roles and several users can be assigned the same roles. Roles are typically created for a database application.
Syntax
CREATE ROLE role;
Example
CREATE ROLE manager;
GRANT CREATE TABLE, CREATE VIEW TO manager;
GRANT manager TO deep, raj;
Object Privileges
Object Privileges
Tables
Views
Sequence
Procedure
ALTER
X

X

DELETE
X
X


EXECUTE



X
INDEX
X



INSERT
X
X


REFERENCES
X
X


SELECT
X
X
X

UPDATE
X
X



Object privilege by users
· Object privileges vary from object to object.
· An owner has all the privileges on the object.
Syntax
GRANT object_privi [(columns)]
ON object
TO {userrolePUBLIC}
[WITH GRANT OPTION]
Example