Comparing File Organizations • • •

• •

Heap files (random order; insert at eof) Sorted files, sorted on Clustered B+ tree file, Alternative (1), search key Heap file with unclustered B + tree index on search key Heap file with unclustered hash index on search key

Operations to Compare • • • • •

Scan: Fetch all records from disk Equality search Range selection Insert a record Delete a record

Cost Model for Our Analysis We ignore CPU costs, focus on I/O cost, assume: – – –

– –



B: The number of data pages R: Number of records per page D: (Average) time to read or write a disk page F: Fanout in B+ Tree Measuring number of page I/O’s ignores gains of pre-fetching a sequence of pages (i.e., blocked I/O); thus, even I/O cost is only approximated. Average-case analysis with simplistic assumptions.  Good enough to show the overall trends!

Assumptions in Our Analysis • Heap Files: –

Equality search on a candidate key: results in exactly one match

• Sorted Files: –

Files compacted after deletions.

• Indexes: – Alt (2), (3): data entry size = 10% size of record – Hash: No overflow buckets. •



80% page occupancy => File size = 1.25 data size

Tree: 67% occupancy (this is typical). •

Implies file size = 1.5 data size

Cost of Operations (a) Scan

(b) Equality

(c ) Range

(d) Insert

(e) Delete

(1) Heap (2) Sorted (3) Clustered (4) Unclustered Tree index (5) Unclustered Hash index

 Several assumptions underlie these (rough) estimates!

Cost of Operations (a) Scan

(b) Equality

(c ) Range

(d) Insert (e) Delete

(1) Heap

BD

0.5BD

BD

2D

(2) Sorted

BD

Dlog 2B

D(log 2 B + # pgs with match recs) (3) 1.5BD Dlog F 1.5B D(log F 1.5B Clustered + # pgs w. match recs) (4) Unclust. BD(R+0.15) D(1 + D(log F 0.15B Tree index log F 0.15B) + # pgs w. match recs) (5) Unclust. BD(R+0.125) 2D BD Hash index

Search + BD

Search +D Search +BD

Search +D

Search +D

Search + 2D

Search + 2D

Search + 2D

Search + 2D

 Several assumptions underlie these (rough) estimates!

Performance Tuning Step 1: Understanding the Workload • For each query in the workload: – – – –

Which relations does it access? Which attributes are retrieved? Which attributes are involved in selection/join conditions? How selective are these conditions likely to be?

• For each update in the workload: – – –

Which attributes are involved in selection/join conditions? How selective are these conditions likely to be? The type of update (INSERT/DELETE/UPDATE), and the attributes that are affected.

Performance Tuning Step 2: Choice of Indexes • What indexes should we create? – – –

Which relations should have indexes? What field(s) should be the search key? Should we build multiple indexes?

• For each index, what kind of an index should it be? – –

Hash/tree? Clustered?

Choice of Indexes (Contd.) • One approach: – – – –

Consider the most important queries in turn. Consider the best plan using the current indexes See if a better plan is possible with an additional index* If so, create it. Iterate..

• Before creating an index, consider the impact on updates in the workload! –

Trade-off: Indexes can make queries go faster, updates slower. Require disk space, too.

Index Selection Guidelines (I) • Attributes in WHERE clause are candidates for index keys. – Exact match condition suggests hash index. – Range query suggests tree index. – Clustering especially useful for: • range queries; • equality queries if there are many duplicates.

Examples of Clustered Indexes •

Consider the range query – – –

Search key? Index type? How selective is the condition? Is the index clustered?

• Consider the – – –

GROUP BY

query.

SELECT E.dno, COUNT (*) FROM Emp E WHERE E.age>10 GROUP BY E.dno

Selectivity? Index on E.age or E.dno? Clustered?

• Equality queries and duplicates: –

SELECT E.dno FROM Emp E WHERE E.age>40

Clustering on E.hobby helps!

SELECT E.dno FROM Emp E WHERE E.hobby=Stamps

Index Selection Guidelines (II) • Multi-attribute search keys should be considered when a WHERE clause contains several conditions. –

Only when all conditions a equality conditions, then use hash index, otherwise, use tree index.



Order of attributes in the search key is important for range queries in terms of performance

Indexes with Composite Search Keys • Composite Search Keys: for multiple conditions in WHERE –

Equality query: Every field value is equal to a constant value. E.g. wrt index: • age=20 and sal =75



Range query: Some field value is not a constant. E.g.:

Examples of composite key indexes. 11,80

11

12,10

12

12,20 13,75

• age=20 and sal > 10 10,12

• Data entries in B+ tree index sorted by search key to support range, group by queries.

20,12

75,13

name age sal bob 12

10

cal 11

80

joe 12

20

sue 13

75

12 13 10

Data records sorted by name

80,11

Data entries in index sorted by

20

75 80

Data entries sorted by

Composite Search Keys • To retrieve Emp records with age=30

AND

sal=4000

– Hash/Tree? On , , ,? Clustered?

• If condition is: age=30 –

AND

Tree/Hash? Index key or ? Clustered?

• If condition is: 20
• •

3000
AND

3000
Tree/Hash? On or ? Clustered?

Composite indexes are larger, updated more often Better chance to have index-only evaluation plan

Index Selection Guidelines (III) • Try to choose indexes that benefit as many queries as possible. • Index-only strategies may be possible  very desirable • Choose clustered index based on importance of the queries and the amount of speed up – For queries with index-only strategies, clustering does not matter

Index-Only Plans SELECT E.dno, COUNT(*) FROM Emp E Tree Index! GROUP BY E.dno

SELECT E.dno, MIN(E.sal) FROM Emp E Tree index! GROUP BY E.dno SELECT AVG(E.sal) or FROM Emp E WHERE E.age=25 AND E.sal BETWEEN 3000 AND 5000 Tree index!

Index-Only Plans (Contd.) • Tree index • Unclustered or clustered? • or – Which is better?

SELECT E.dno, COUNT (*) FROM Emp E WHERE E.age=30 GROUP BY E.dno

SELECT E.dno, COUNT (*) FROM Emp E WHERE E.age>30 GROUP BY E.dno

Summary • Many alternative file organizations exist, each appropriate in some situation. • If selection queries are frequent, sorting the file or building an index is important. – –

Hash-based indexes only good for equality search. Sorted files and tree-based indexes best for range search; also good for equality search. (Files rarely kept sorted in practice; B+ tree index is better.)

• Index is a collection of data entries plus a way to quickly find entries with given key values.

Summary (Contd.) • Data entries can be actual data records, pairs, or pairs. • Can have several indexes on a given file of data records, each with a different search key. • Indexes can be classified as clustered vs. unclustered, primary vs. secondary. Differences have important consequences for utility/performance.

Summary (Contd.) • Understanding the nature of the workload for the application, and the performance goals, is essential to developing a good physical design. • Indexes must be chosen to speed up important queries (and perhaps some updates!). – – – – –

Index maintenance overhead on updates to key fields. Choose indexes that can help many queries, if possible. Build indexes to support index-only strategies. Clustering is an important decision; only one index on a given relation can be clustered! Order of fields in composite index key can be important.

Overview of Storage and Indexing

Sorted files, sorted on . • Clustered B+ tree file, Alternative (1), search key . • Heap file with unclustered B + tree index on search key

276KB Sizes 0 Downloads 265 Views

Recommend Documents

Endogenous Indexing and Monetary Policy Models
I Degree of indexation signi cant ACB may erroneously conclude that the value of commitment is small, price level target welfare-reducing. I What if persistence ...

STORAGE OF PAPER AND PARCHMENT RECORDS.pdf ...
There was a problem previewing this document. Retrying... Download ... STORAGE OF PAPER AND PARCHMENT RECORDS.pdf. STORAGE OF PAPER AND ...

Segmented Trajectory based Indexing and Retrieval of Video Data.
Indexing and Retrieval of Video. Data. Multimedia Systems Lab, UIC. 1. Faisal I. Bashir, Ashfaq A. Khokhar, Dan Schonfeld. University of Illinois at Chicago,.

Segmented Trajectory based Indexing and Retrieval of ...
Multimedia Systems Lab, UIC. 1. Faisal I. Bashir, Ashfaq A. ... Systems Lab, UIC. 4. System Block Diagram .... Of Detection, P d and Prob. Of False Alarm,. P f is:.

A Survey of Indexing Techniques for Scalable Record Linkage and ...
A Survey of Indexing Techniques for Scalable Record Linkage and Deduplication..pdf. A Survey of Indexing Techniques for Scalable Record Linkage and ...

Indexing Dataspaces - Semantic Scholar
and simple structural requirements, such as “a paper with title 'Birch', authored ... documents, Powerpoint presentations, emails and contacts,. RDB. Docs. XML.

STORAGE OF BOOKS AND ALBUMS.pdf
Page 1 of 2. STORAGE OF BOOKS AND ALBUMS. Iona McCraith, Archives Advisor, July 2016. The use of appropriate storage methods and materials for ...

Storage of materials
Mar 24, 1992 - (74) Attorney, Agent, or F irmiNeifeld IP Law, PC. (57). ABSTRACT .... 1989”; pp. 234229. Fax from Eric Potter Clarkson to EPO dated Oct. 15, 1999 ..... substances Which in their free states are extremely labile are found to ...

Storage of materials
Aug 28, 2001 - 4,847,090 A * 7/ 1989 Della Posta et al. ...... .. 424/440 ... Water-soluble or sWellable glassy or rubbery composition .... drate Water Systems”.*.

Overview of Machine Learning and H2O.ai - GitHub
Gradient Boosting Machine: Highly tunable tree-boosting ensembles. •. Deep neural networks: Multi-layer feed-forward neural networks for standard data mining tasks. •. Convolutional neural networks: Sophisticated architectures for pattern recogni

Storage of materials
Aug 28, 2001 - Declaration under 37 CFR 1.132 re: Franks et al. Ser. No. 08/241,457 ..... continue it for some hours, for instance 24 to 36 hours. As evaporation ...

OVERVIEW OF MANAGEMENT
Page 18. PUBLIC SECTOR ORGANIZATIONS include central government departments such as the Ministry of Health, which do not have profit as their goal.

Storage router and method for providing virtual local storage
Jul 24, 2008 - Technical Report-Small Computer System Interface-3 Generic. PacketiZed Protocol ... 1, 1996, IBM International Technical Support Organization, ..... be a rack mount or free standing device With an internal poWer supply.

OVERVIEW OF MANAGEMENT
calculated as the amount of resources used to produce a product or service. It refers to the ..... Managers scan their environment for information that may affect ...

Storage router and method for providing virtual local storage
Jul 24, 2008 - CRD-5500, Raid Disk Array Controller Product Insert, pp. 1-5. 6'243'827 ..... Data Book- AIC-1 160 Fibre Channel Host Adapter ASIC (Davies Ex. 2 (CNS ..... devices 20 comprise hard disk drives, although there are numerous ...

Intrinsic volumes of convex cones Overview Overview
Aug 7, 2012 - app ear in a tub e formula similar to the one defining the intrinsic volumes. Intrinsic volumes of convex cones. Random convex p ograms. Lo calizing ...... β. /2). =: M n. ,β. Intrinsic volumes of convex cones. Measures of th e semide

NOA- Supply and Delivery of Server and Central Storage System.pdf ...
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. NOA- Supply ...

Stability of Equilibrium Points and Storage Capacity of ...
and stability of the so-called Hopfield neural networks with higher order nonlinearity. There are ... Iran (phone: +98-918-839-0095; fax: +98-21-640-6009; e-mail:.

Safe-And-Secure-Transport-And-Storage-Of-Radioactive-Materials ...
Page 1 of 3. Download ]]]]]>>>>>(PDF) Safe And Secure Transport And Storage Of Radioactive Materials. [eBooks] Safe And Secure Transport And Storage Of. Radioactive Materials. SAFE AND SECURE TRANSPORT AND STORAGE OF RADIOACTIVE MATERIALS EBOOK AUTHO

Stability of Equilibrium Points and Storage Capacity of ...
numerical results (i.e. the width of the distribution must be much less than its minimum value). (α must be near the maximum loading tolerated by the network, ...

An Overview of Bombing and Arson Attacks by Environmental and ...
An Overview of Bombing and Arson Attacks by Environm ... ghts Extremists in the United States 1995 - 2010.pdf. An Overview of Bombing and Arson Attacks by ...

Database Indexing Summary.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. Database ...

Indexing Dataspaces - Xin Luna Dong
documents, Powerpoint presentations, emails and contacts,. RDB. Docs. XML ..... experiments show that adding association information into an ATIL (to obtain ...