Database Indexes for Improved Query Performance
O
t
h
e
r
d
a
t
a
b
a
s
e
o
b
j
e
c
t
s
(Index and synonyms)
W
h
a
t
i
s
a
n
i
n
d
e
x
?
A
n
i
n
d
e
x
:
•
I
s
a
s
c
h
e
m
a
o
b
j
e
c
t
•
I
s
u
s
e
d
b
y
t
h
e
O
r
a
c
l
e
S
e
r
v
e
r
t
o
s
p
e
e
d
u
p
t
h
e
r
e
t
r
i
e
v
a
l
o
f
r
o
w
s
b
y
u
s
i
n
g
a
p
o
i
n
t
e
r
•
I
s
i
n
d
e
p
e
n
d
e
n
t
o
f
t
h
e
t
a
b
l
e
i
t
i
n
d
e
x
e
s
T
h
i
s
m
e
a
n
s
t
h
a
t
t
h
e
y
c
a
n
b
e
c
r
e
a
t
e
d
o
r
d
r
o
p
p
e
d
a
t
a
n
y
t
i
m
e
a
n
d
h
a
v
e
n
o
e
f
f
e
c
t
o
n
t
h
e
b
a
s
e
t
a
b
l
e
s
o
r
o
t
h
e
r
i
n
d
e
x
e
s
N
o
t
e
:
W
h
e
n
y
o
u
d
r
o
p
a
t
a
b
l
e
,
c
o
r
r
e
s
p
o
n
d
i
n
g
i
n
d
e
x
e
s
a
r
e
a
l
s
o
d
r
o
p
p
e
d
.
H
o
w
A
r
e
I
n
d
e
x
e
s
C
r
e
a
t
e
d
?
•
A
u
t
o
m
a
t
i
c
a
l
l
y
:
A
u
n
i
q
u
e
i
n
d
e
x
i
s
c
r
e
a
t
e
d
a
u
t
o
m
a
t
i
c
a
l
l
y
w
h
e
n
y
o
u
d
e
f
i
n
e
a
P
R
I
M
A
R
Y
K
E
Y
o
r
U
N
I
Q
U
E
c
o
n
s
t
r
a
i
n
t
i
n
a
t
a
b
l
e
d
e
f
i
n
i
t
i
o
n
.
•
M
a
n
u
a
l
l
y
:
U
s
e
r
s
c
a
n
c
r
e
a
t
e
n
o
n
u
n
i
q
u
e
i
n
d
e
x
e
s
o
n
c
o
l
u
m
n
s
t
o
s
p
e
e
d
u
p
a
c
c
e
s
s
t
o
t
h
e
r
o
w
s
C
r
e
a
t
i
n
g
a
n
I
n
d
e
x
•
C
r
e
a
t
e
a
n
i
n
d
e
x
o
n
o
n
e
o
r
m
o
r
e
c
o
l
u
m
n
s
.
S
y
n
t
a
x
:
C
R
E
A
T
E
I
N
D
E
X
i
n
d
e
x
n
a
m
e
O
N
t
a
b
l
e
(
c
o
l
u
m
n
[
,
c
o
l
u
m
n
]
.
.
.
)
;
•
E
X
:
I
m
p
r
o
v
e
t
h
e
s
p
e
e
d
o
f
q
u
e
r
y
a
c
c
e
s
s
t
o
t
h
e
L
A
S
T
_
N
A
M
E
c
o
l
u
m
n
i
n
t
h
e
E
M
P
L
O
Y
E
E
S
t
a
b
l
e
:
C
R
E
A
T
E
I
N
D
E
X
e
m
p
_
l
a
s
t
_
n
a
m
e
_
i
d
x
O
N
e
m
p
l
o
y
e
e
s
(
l
a
s
t
_
n
a
m
e
)
;
M
o
r
e
I
s
N
o
t
A
l
w
a
y
s
B
e
t
t
e
r
•
More indexes on a table does not mean
faster queries. Each DML operation that is
committed on a table with indexes means
that the indexes must be updated. The
more indexes you have associated with a
table, the more effort the Oracle server
must make to update all the indexes after
a DML operation
W
h
e
n
t
o
C
r
e
a
t
e
a
n
I
n
d
e
x
Y
o
u
s
h
o
u
l
d
c
r
e
a
t
e
a
n
i
n
d
e
x
i
f
:
•
A
c
o
l
u
m
n
c
o
n
t
a
i
n
s
a
w
i
d
e
r
a
n
g
e
o
f
v
a
l
u
e
s
•
A
c
o
l
u
m
n
c
o
n
t
a
i
n
s
a
l
a
r
g
e
n
u
m
b
e
r
o
f
n
u
l
l
v
a
l
u
e
s
•
O
n
e
o
r
m
o
r
e
c
o
l
u
m
n
s
a
r
e
f
r
e
q
u
e
n
t
l
y
u
s
e
d
t
o
g
e
t
h
e
r
i
n
a
W
H
E
R
E
c
l
a
u
s
e
o
r
a
j
o
i
n
c
o
n
d
i
t
i
o
n
•
T
h
e
t
a
b
l
e
i
s
l
a
r
g
e
a
n
d
m
o
s
t
q
u
e
r
i
e
s
a
r
e
e
x
p
e
c
t
e
d
t
o
r
e
t
r
i
e
v
e
l
e
s
s
t
h
a
n
2
t
o
4
%
o
f
t
h
e
r
o
w
s
W
h
e
n
N
o
t
t
o
C
r
e
a
t
e
a
n
I
n
d
e
x
I
t
i
s
u
s
u
a
l
l
y
n
o
t
w
o
r
t
h
c
r
e
a
t
i
n
g
a
n
i
n
d
e
x
i
f
:
•
T
h
e
t
a
b
l
e
i
s
s
m
a
l
l
•
T
h
e
c
o
l
u
m
n
s
a
r
e
n
o
t
o
f
t
e
n
u
s
e
d
a
s
a
c
o
n
d
i
t
i
o
n
i
n
t
h
e
q
u
e
r
y
•
M
o
s
t
q
u
e
r
i
e
s
a
r
e
e
x
p
e
c
t
e
d
t
o
r
e
t
r
i
e
v
e
m
o
r
e
t
h
a
n
2
t
o
4
%
o
f
t
h
e
r
o
w
s
i
n
t
h
e
t
a
b
l
e
•
T
h
e
t
a
b
l
e
i
s
u
p
d
a
t
e
d
f
r
e
q
u
e
n
t
l
y
•
T
h
e
i
n
d
e
x
e
d
c
o
l
u
m
n
s
a
r
e
r
e
f
e
r
e
n
c
e
d
a
s
p
a
r
t
o
f
a
n
e
x
p
r
e
s
s
i
o
n
R
e
m
o
v
i
n
g
a
n
I
n
d
e
x
•
R
e
m
o
v
e
a
n
i
n
d
e
x
f
r
o
m
t
h
e
d
a
t
a
d
i
c
t
i
o
n
a
r
y
b
y
u
s
i
n
g
t
h
e
D
R
O
P
I
N
D
E
X
c
o
m
m
a
n
d
S
y
n
t
a
x
:
D
R
O
P
I
N
D
E
X
i
n
d
e
x
n
a
m
e
;
E
X
:
R
e
m
o
v
e
t
h
e
U
P
P
E
R
_
L
A
S
T
_
N
A
M
E
_
I
D
X
i
n
d
e
x
.
D
R
O
P
I
N
D
E
X
u
p
p
e
r
_
l
a
s
t
_
n
a
m
e
_
i
d
x
;
•
To drop an index, you must be the owner of the
index or have the DROP ANY INDEX privilege.
S
y
n
o
n
y
m
s
S
i
m
p
l
i
f
y
a
c
c
e
s
s
t
o
o
b
j
e
c
t
s
b
y
c
r
e
a
t
i
n
g
a
s
y
n
o
n
y
m
(
a
n
o
t
h
e
r
n
a
m
e
f
o
r
a
n
o
b
j
e
c
t
)
.
W
i
t
h
s
y
n
o
n
y
m
s
,
y
o
u
c
a
n
:
•
E
a
s
e
r
e
f
e
r
r
i
n
g
t
o
a
t
a
b
l
e
o
w
n
e
d
b
y
a
n
o
t
h
e
r
u
s
e
r
•
S
h
o
r
t
e
n
l
e
n
g
t
h
y
o
b
j
e
c
t
n
a
m
e
s
C
r
e
a
t
i
n
g
a
n
d
R
e
m
o
v
i
n
g
S
y
n
o
n
y
m
s
S
y
n
t
a
x
:
C
R
E
A
T
E
[
P
U
B
L
I
C
]
S
Y
N
O
N
Y
M
s
y
n
o
n
y
m
F
O
R
o
b
j
e
c
t
n
a
m
e
;
E
X
:
C
r
e
a
t
e
a
s
h
o
r
t
e
n
e
d
n
a
m
e
f
o
r
t
h
e
s
y
s
t
e
m
.
b
a
n
k
t
a
b
l
e
.
C
R
E
A
T
E
S
Y
N
O
N
Y
M
s
b
a
n
k
F
O
R
s
y
s
t
e
m
.
b
a
n
k
;
R
e
m
o
v
i
n
g
a
S
y
n
o
n
y
m
•
To drop a synonym, use the DROP
SYNONYM statement.
SYNTAX: DROP SYNONYM
synonym
EX:DROP SYNONYM sbank;
•
Only the database administrator can drop
a public synonym or the user who has
DROP PUBLIC SYNONYM privilege.
EX:DROP PUBLIC SYNONYM emp
An index in a database serves as a pointer to speed up row retrieval by the Oracle Server. Indexes can be created manually or automatically when defining primary key or unique constraints in table definitions. Creating indexes on columns can enhance query access speed, but having too many indexes on a table may not always result in faster queries. It is important to create indexes wisely based on certain criteria to optimize database performance.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Other database objects (Index and synonyms)
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 Is independent of the table it indexesThis means that they can be created or dropped at any time and have no effect on the base tables or other indexes 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 nonunique indexes on columns to speed up access to the rows
Creating an Index Create an index on one or more columns. Syntax:CREATE INDEX indexname ON table (column[, column]...); EX:Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table: CREATE INDEX emp_last_name_idx ON employees(last_name);
More Is Not Always Better More indexes on a table does not mean faster queries. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation
When to Create an Index You should create an index if: 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 It is usually not worth creating an index if: The table is small The columns are not often used as a condition in the query Most queries are expected to retrieve more than 2 to 4% of the rows in the table The table is updated frequently The indexed columns are referenced as part of an expression
Removing an Index Remove an index from the data dictionary by using the DROP INDEX command Syntax: DROP INDEX indexname; EX:Remove the UPPER_LAST_NAME_IDX index. DROP INDEX upper_last_name_idx; To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.
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
Creating and Removing Synonyms Syntax: CREATE [PUBLIC] SYNONYM synonym FOR objectname; EX:Create a shortened name for the system.bank table. CREATE SYNONYM sbank FOR system.bank;
Removing a Synonym To drop a synonym, use the DROP SYNONYM statement. SYNTAX: DROP SYNONYM synonym EX:DROP SYNONYM sbank; Only the database administrator can drop a public synonym or the user who has DROP PUBLIC SYNONYM privilege. EX:DROP PUBLIC SYNONYM emp
