INDEXES
What is an Index?
- An 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 it indexes.
- Is
used and maintained automatically by the Oracle server.
Note: When you
drop a table, corresponding indexes are also dropped.
How Are Indexes Created?
•
Automatically : A unique index is
created automatically when you define a
PRIMARY KEY or
UNIQUE constraint in a table definition.
•
Manually : Users can create non-unique indexes
on columns to
speed up access to the rows.
Types of Indexes
Two types of
indexes can be created.
- One type is a
unique index and
-
Other type of
index is a non-unique index.
Creating an Index :
Create an index on one or more columns.
Syntax:
CREATE INDEX index
ON table (column[, column]...);
In the syntax:
index is the name of the index
table is the name of the table
column is the name of the
column in the table to be
To create an index in any schema, you need the CREATE
ANY INDEX privilege or the CREATE TABLE privilege on the table on which you are
creating the index.
Example:
CREATE INDEX XX_EMP_N1 ON XX_EMP_EMPID(EMP_ID);
Dropping an Index
Remove an index from the data dictionary by using the
DROP INDEX command.
Syntax:
DROP INDEX index;
Example:
DROP INDEX XX_EMP_N1;
To drop an index, you must be the owner of the index
or have the DROP ANY INDEX privilege.
You cannot modify indexes. To change an index, you
must drop it and then re-create it.
Note: If you drop
a table, indexes and constraints are automatically dropped, but views and
sequences remain.
OLAP
•
Oracle OLAP cubes deliver
sophisticated calculations using simple SQL queries - producing results with
speed of thought response times.
•
This outstanding query performance
may be leveraged transparently when deploying OLAP cubes as materialized views
– enhancing the performance of summary queries against detail relational
tables.
•
Because Oracle OLAP is embedded in
Oracle Database 12c, it allows centralized management of data and business
rules in a secure, scalable and enterprise-ready platform.
With Oracle OLAP you can:
•
Easily define a multidimensional
model with advanced analytic calculations
•
Productively deliver rich analytics
to any reporting and analysis tool using simple SQL
•
Transparently improve summary queries
against tables using cube-based materialized views
•
Combine OLAP data with any other data
in your Oracle Database - including spatial, data mining, XML, documents and
more
•
Leverage your existing Oracle
Database expertise and software investment
LOCKS
Locks :
When one thread of control wants to obtain access to
an object, it requests a lock for that object. This lock is
what allows DB to provide your application with its transactional isolation
guarantees by ensuring that:
•
no other thread of control can read
that object (in the case of an exclusive lock), and
•
no other thread of control can modify
that object (in the case of an exclusive or non-exclusive lock).
Lock Resources :
When locking occurs, there are conceptually three
resources in use:
- The locker.
- This is the thing that holds the lock. In a transactional
application, the locker is a transaction handle. For
non-transactional operations, the locker is a cursor or
a Db handle.
- The lock.
- This is the actual data structure that locks the object. In DB, a
locked object structure in the lock manager is representative of the
object that is locked.
No comments:
Post a Comment