下载  >  数据库  >  Oracle  > Expert.Oracle.Indexing.and.Access.Paths

Expert.Oracle.Indexing.and.Access.Paths 评分

Speed up the execution of important database queries by making good choices about which indexes to create. Choose correct index types for different scenarios. Avoid indexing pitfalls that can actually have indexes hurting performance rather than helping. Maintain indexes so as to provide consistent
Expert Oracle indexing and Access Paths Darl kuhn Sam R alapati Morrison, Colorado, USA Flower Mound, Texas, USA Bill Padfield Aurora, Colorado, uSA ISBN-13(pbk):978-14842-19836 ISBN-13( electronic:978-1-4842-1984-3 DOI10.1007/978-1-4842-1984-3 Library of Congress Control Number: 2016953748 Copyright o 2016 by Darl Kuhn, Sam R Alapati and Bill Padfield This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse ofillustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights While the advice and information in this book are believed to be true and accurate at the date of publication neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein Managing Director: Welmoed Spahr Lead Editor Jonathan gennick Development Editor: Laura Berendson Technical reviewer: Michelle malcher Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron black, Louise Corrigan Jonathan gennick, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James Markham Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing Coordinating Editor: Jill Balzano Copy Editor: Kim Burton-Weisman Compositor SPi Global Indexer: SPi global Artist: SPi global Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax(201)348-4505, e-mail orders-ny@springer-sbm.comorvisitwww.springer.comApressMedia,lLcisaCaliforniaLlcandthesole member (owner) is Springer Science Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation Forinformationontranslationspleasee-mailrights@apress.com,orvisitwww.apress.com Apress and friends of Ed books may be purchased in bulk for academic, corporate, or promotional use. e Book versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales-ebooklIcensingwebpageatwww.apress.com/bulk-sales Any source code or other supplementary material referenced by the author in this text is available to readers at www.aprEss.com.Fordetailedinformationabouthowtolocateyourbookssourcecodegoto www.apress.com/source-code/ Printed on acid-free paper To Deni Luelle rossiter Staheli, she inspired greatness -Darl kuhn With love to Yuna, Evan, and my family -Bill padfield Contents at a glance About the authors About the technical reviewer Acknowledgments Chapter1: ntroduction to0 racle indexes,,…,…,,…,,,m:1 Chapter 2: B-tree Indexes ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■口■■■■■■■■■■■■■■■a■国a■国■ 23 Chapter 3: Bitmap Indexes 57 Chapter 4: Index-Organized Tables.aa 77 Chapter 5: Specialized Indexes mm mmammammmmmaamm 93 Chapter 6: Partitioned Indexes IRRRBBBRBRRRRRBRIRIBRIR IRIRRRRRRIIRRRIEIIRIRRIRn 125 Chapter 7: Tuning Index Usage .mmmmm mmma 157 Chapter 8: Maintaining Indexes mamammmmammaamma 185 Chapter 9: SQL Tuning Advisor amman 211 Chapter 10: In-Memory Column Store amman u235 Index…uu253 Contents About the authors About the technical reviewer Acknowledgments Chapter 1: Introduction to Oracle Indexes. mmm BBRBRBRRBRREIan Index basics 122 Performance without an index Implementing an Index to Improve Performance Accessing Only the Index..............................6 Oracle ignoring an Index 日a日日自日日自日日自日日自日日自日日自日日自日日日日自日日日日日日日面日日面日目面日面面日面面日面日面日日面日面量 689 Index Basics Wrap-up Determining Which Type of Index to Use B-tree Indexes Specialized Index Types.........................................13 Determining Which Columns to Index ……18 Indexes on Primary Key and Unique Key columns 18 Indexes on Foreign Key columns. Other suitable columns Indexing Guidelines 20 Summary 21 CONTENTS Chapter 2: B-tree Indexes. m R RE B a II I Ea E EE E In 23 Understanding how oracle Uses b-tree Indexes 24 Prepping for B-tree Indexes 29 Estimating the size of an Index before creation Creating separate tablespaces for Indexes 31 Inheriting Storage Parameters from the Tablespace 32 Naming Standards 32 Implementing B-tree Indexes 33 Creating a B- tree Index….…………33 Reporting on Indexes…..,…34 Displaying Index Code. Dropping a b-tree index. Managing B-tree Indexes with Constraints 37 creating B-tree Index on Primary Key columns 37 Creating a B-tree Index on Unique Key column 42 Indexing Foreign Key columns . Multiple Indexes on the Same Column Combinations 49 EXtended Data Types .eaeaeaeaeaeaeaeaeaeaeaoeaoaa. 50 Indexing Extended columns .. Virtual column solution wwwwwww 53 Function-basedindexsolutionwwwwwwww.55 Summary 56 Chapter 3: Bitmap Indexes ■口■■■■■■■■■■■■■■■■■■■■■■■■■■■■■口■■■■■■■■■■■■■■■■■■■■口■■■■■■■■■■■■■■■■■■■■口■■■■■国■■ 57 Understanding bitmap Indexes 58 Creating a Bitmap Index .mmammannmannmmnmonnmmnnnn 61 Creating a Partitioned Bitmap Index.ameaeeaaee.. 62 Creating a Bitmap Index on an Index-organized Table..saanen 63 Performance Implications of Querying with Bitmap Indexes... 64 Performance Implications of Loading Data with ,68 V11 ONTENTS Understanding Bitmap Join Indexes Creating a Bitmap Join Index 72 Duplicating Indexes for Performance Reporting on Bitmap Indexes 74 Summary. 75 Chapter 4: Index-Organized Tables.mmammmmmammmmmmma 77 Understanding the structure Understanding the Advantages 78 Creating an Index-Organized Table. Adding an Overflow Segment .mmmmananmmnmnmmnmnn 81 Compressing an Index-Organized Table 84 Building Secondary Indexes .85 Rebuilding an Index-Organized Table 88 Converting to or from an Index-Organized Table 9 Reporting on Index-Organized Tables. 90 Summary 91 Chapter 5: Specialized Indexes ■■■口■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■口■■■■■■■■■■■口■■■■■■■ 93 nvisible indexes,…93 When to create an Invisible Index Creating an Invisible Ind Finding Invisible Indexes in Your Database Making an Invisible Index Available to the optimizer Maintaining an Invisible Index.. Descending Indexes…, 96 Function-Based Indexes 98 Creating a Function-Based Index 日B日日面日自aB日a日自a日面日aB日a日自a日面日aB日面a日自a日面日B日aB自a日面日B日自a日日 99 Limitations of function -Based Indexes 102 Collecting Statistics for Function-Based Indexes............................103 Indexes on virtual columns IX CONTENTS Key compressed Indexes……,,.,.,.,.,.,.,, 106 When Key compression is Usefu 106 Creating a compressed Index Key compression and Storage................................109 Advanced Index Compression …110 Composite Indexes 112 Understanding Index Skip Scans and composite Indexes 113 0 dering the Columns in a Composite Index……........…,114 Choosing Keys for Composite Indexes Creating virtual Indexes 117 Reverse Key Indexes 119 Disadvantages of a Reverse Key Index 120 When to Use a Reverse Key Index...,,……,,…121 Creating a Reverse Key Index Application Domain Indexes…… 122 Summary… 123 Chapter 6: Partitioned Indexes u125 Understanding Partitioned Indexes 126 Creating a Local Partitioned Index 126 The simplest Form… Partition Level requirements 128 Prefixed and Non-Prefixed Options. Managing Primary Keys and Unique Indexes 129 Creating a global partitioned Index. ..mmm. 131 Partial indexes, mmmmmmmm 135 Creating Local Partial Indexes Creating Global Partial Indexes 137 Performance Implications of Partial Indexes.......................... 138 Choosing the Type of Index for Your Application Maintaining Indexes on Partitioned Tables .141 CONTEN Adding a Partition.…,,…,… 142 Truncating a Partition.….,…,…,…,…,…,…,…,…,…………,………143 Moving a Partition...,.,....,.,…,……,143 Splitting a partition 144 Exchanging a Partition 145 Dropping a partition.…....…..146 Merging a Partition..,,…,…,……,…………147 Rebuilding Global Partitioned and Non-Partitioned Indexes.....to..n 147 Setting Index partitions as Unusable and then Rebuilding .. 150 Index Implications for Interval Partitioning 152 Making Older Data Read-Only 153 Reporting on Partitioned Indexes 153 Summary. 155 Chapter 7: Tuning Index Usage mmamaammmman 157 Optimizer Access Paths 157 Index scans ……158 Index Unique Scan 158 Index Range Scan…..........................159 ndex skip scan.……16 Index Full scan 162 Index fast Full scan Determining Whether a Query Uses an Index 163 Avoiding an Index 165 Avoiding All Use of an Index 165 Avoiding Only the Fast Full Scan Forcing a Table scan.....,.,.,.,,.,.,,…… 166 Choosing Between an Index and a Table Scan 166 Why the optimizer May ignore indexes ...m..mmmmm. 167 Number of distinct rows 168 Index Clustering fact

...展开详情
所需积分/C币:9 上传时间:2016-10-18 资源大小:6.15MB
举报 举报 收藏 收藏
分享 分享

评论 下载该资源后可以进行评论 1

breakenarrow thanks!!谢谢分享!!
2017-01-07
回复
Apress.Expert.Oracle.Practices.Oracle.Database.Administration.from.the.Oak.Table.Edition.2010.Reta.pdf

Apress.-.Expert.Oracle.Practices.Oracle.Database.Administration.from.the.Oak.Table.Edition.2010.Reta.pdf

立即下载
Apress.Expert.Oracle.GoldenGate

Apress.Expert.Oracle.GoldenGate

立即下载
Apress.Expert.Oracle.Application.Express

Apress.Expert.Oracle.Application.Express

立即下载
Expert.Oracle.Practices

Expert.Oracle.Practices 英文版 Apress

立即下载
.Expert.Oracle

“This book will help you make the best use of Oracle technology. Emulating Tom’s rational methodology, and demand for proof by example, will make you a far better technology thinker. Without question, this is one of the most important Oracle books you can possess.” —Ken Jacobs, (aka “Dr. DBA”) Vice

立即下载
Expert.Oracle.Database.Architecture Scripts

Expert.Oracle.Database.Architecture Scripts

立即下载
Apress.Expert.Oracle.Database.Architecture.2nd.Edition

经典Expert.Oracle.Database.Architecture.2nd.Edition书籍第二版

立即下载
Expert.Oracle.Database.11g.Administration

Expert.Oracle.Database.11g.Administration English version by Sam.R.Alapati

立即下载
Apress.Expert.Oracle.Database.11g.Administration.2009.pdf

Apress.Expert.Oracle.Database.11g.Administration.2009.pdf

立即下载
Expert.Oracle.Database.Architecture.2nd.Edition

Expert.Oracle.Database.Architecture.2nd.Edition.pdf

立即下载
Expert.Oracle.Database.Architecture.3rd.Edition

Expert.Oracle.Database.Architecture.3rd.Edition Oracle编程艺术,第三版(英文),附源码

立即下载
Apress.Expert.Oracle9i.Database.Administration

Apress.Expert.Oracle9i.Database.Administration.eBook-LiB

立即下载
Apress.Expert.Oracle.RAC.12c

Oracle 12C RAC install.setup,admin

立即下载
Expert.Oracle.RAC.Performance.Diagnostics

Working for several years across several industries on various RAC projects, there have been several occasions to troubleshoot performance issues in a production environment.

立即下载
Expert.Oracle.Indexing.and.Access.Paths

Speed up the execution of important database queries by making good choices about which indexes to create. Choose correct index types for different scenarios. Avoid indexing pitfalls that can actually have indexes hurting performance rather than helping. Maintain indexes so as to provide consistent

立即下载
Expert.Oracle.JDBC.Programming.May.2005

Apress公司出品,oracle专家编程,经典之作。

立即下载
Expert.Oracle.Database.Architecture.9i.and.10g.Programming

Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions.Sep.2005.rar

立即下载
ModbusTCP/RTU网关设计

基于UIP协议栈,实现MODBUS联网,可参考本文档资料,有MODBUS协议介绍

立即下载
html+css+js制作的一个动态的新年贺卡

该代码是http://blog.csdn.net/qq_29656961/article/details/78155792博客里面的代码,代码里面有要用到的图片资源和音乐资源。

立即下载