Advanced Databases 1

More Advanced PL/SQL Programing

Advanced Databases 2

Agenda

• Indexes – Not PL/SQL but a RBMS performance

enhancement

• PL/SQL– Functions– Cursor For Loops

Advanced Databases

Guide to Oracle 10g

3

Indexes

• Database object which provides capability to speed up the search process.

ID Color

1 Red2 Blue

3 Red

4 Red

5 Blue

6 Green

7 Red

8 Red

Color ID’s

Blue 2, 5Green 6

Red 1, 3, 4, 7, 8

Table Table Index on Color

Impacts on:SELECT? INSERT? UPDATE? DELETE?

Advanced Databases

Guide to Oracle 10g

4

Indexes in SQL

Advanced Databases

Guide to Oracle 10g

5

Why not Index Everything?

• Every Index makes one of those “index tables”

• If you have 10 indexes on a table, an INSERT, DELETE or UPDATE needs to write to that table + the 10 index tables.

• Indexing speeds up reads at the expense of writes.

Advanced Databases

Guide to Oracle 10g

6

Functions

• Functions are stored procedures which return a value.

• Functions are not executed like stored procedures. Instead, they are called from within SQL or PL/SQL.

• SELECT myfunction() …

Advanced Databases

Guide to Oracle 10g

7

Functions

Advanced Databases

Guide to Oracle 10g

8

Cursor For Loops

• Easier to implement cursors. • Simplified – no open, close, fetch or exit

conditions!• If you need to use a cursor, use these.

Advanced Databases 9

Questions