ch12 - Chapter12:Indexing n n BasicConcepts n n n n B...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Chapter 12:  Indexing n Basic Concepts n The most commonly used indexes: n B+-Tree Index n Create index statement n Multi-column (key) index n Rule of thumb of index creation
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Motivation n Suppose the following query is running really  slow.  What  can  you  do  to  make  it  faster  without buying new hardware? Select P.pid, price From product P, orders O Where P.pid = O.pid and O.oid = 1234
Background image of page 2
3 What Is an Index? n Index is a data structure that speeds up access to a  table (so each index is tied to a table) Can you create an index on two tables? n It allows quick access to rows satisfying a certain  condition, without checking all the other rows n What will be the condition in the previous example? n Real life example of indexes?
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Basic Concepts About Index n Components of an index n Search key n Index entries n Index file n Types of indexes n 4
Background image of page 4
5 Benefits and Cost of Indexes n Benefits: n Speed up queries n Cost: n What happen when data is updated? n How about disk space?
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Basic Concepts n Search Key  - attribute or set of attributes used to look up  records in a file. Select P.pid, price From product P, orders O Where P.pid = O.pid and O.oid = 1234 We can create an index on orders.oid and an index on product.pid Search key for the first index? How about the second?
Background image of page 6
7 Basic Concepts n An  index file   consists of  index entries  of the form n Pointer either points to data records  n or another index entry (we will talk about this later) n Index files are typically much smaller than the  original table, why? search-key pointer
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8 Primary Index n Each table may have one primary index such that n Index entries are stored sorted on the search key value, so  do the rows in the table.   E.g., Create an index on orders.oid If this index and the orders table are sorted on oid, this index is the  primary index of orders Table(oid,…): Index:  How many primary indexes a table can have?   One Oracle has something called index organized table (IOT) (1,…) (2,…) (…,…) (1,) (2,) (…,)
Background image of page 8
9 Secondary Index n Secondary index :   an index whose index entries are not sorted  in the same order as the rows in the table n E.g., Suppose we create an index on orders.pid Table (oid,pid,…): Index(pid, pointer): Index: How many secondary indexes a table can have? (1,3…) (2,5,…) (3,2,…) (1,) (2,) (3,) (…,…) (1000,1,…) (…,…)
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Primary Index vs. Secondary Index n How many primary index a table can  have, how about secondary index? n
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/28/2012 for the course IS 620 taught by Professor Chen,z during the Spring '08 term at UMBC.

Page1 / 44

ch12 - Chapter12:Indexing n n BasicConcepts n n n n B...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online