Oracle SQL Jumpstart with Examples

Contents at a Glance Foreword Preface Acknowledgements 1 Introduction to Oracle SQL 2 New Features of Oracle SQL 3 Oracle Database Architecture 4 The SELECT Statement 5 Filtering Rows 6 Sorting Rows 7 Operators, Conditions, and Pseudocolumns 8 Using SQL*Plus 9 Single-Row Functions 10 Joining Tables 11 Grouping and Summarizing Data 12 Subqueries 13 Unusual Query Types 14 Expressions 15 Data Manipulation Language (DML) 1
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples Oracle Data Warehouse Tuning for 10g Oracle Data Warehouse Tuning for 10g Gavin Powell Amsterdam • Boston • Heidelberg • London • New York • Oxford Paris • San Diego• San Francisco • Singapore • Sydney • Tokyo Elsevier Digital Press 30 Corporate Drive, Suite 400, Burlington, MA 01803, USA Linacre House, Jordan Hill, Oxford OX2 8DP, UK Copyright © 2005, Elsevier Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: permissions@elsevier.com.uk. You may also complete your request on-line via the Elsevier homepage (http://elsevier.com), by selecting “Customer Support” and then “Obtaining Permissions.” Recognizing the importance of preserving what has been written, Elsevier prints its books on acid-free paper whenever possible. Library of Congress Cataloging-in-Publication Data Application Submitted. British Library Cataloguing-in-Publication Data A catalogue record for this book is available from the British Library. ISBN-13: 978-1-55558-335-4 ISBN-10: 1-55558-335-0 For information on all Elsevier Digital Press publications visit our Web site at www.books.elsevier.com 05 06 07 08 09 10 9 8 7 6 5 4 3 2 1 Contents at a Glance Preface xix Introduction to Data Warehousing xxiii Part I: Data Warehouse Data Modeling 1 1 The Basics of Data Warehouse Data Modeling 3 2 Introducing Data Warehouse Tuning 31 3 Effective Data Warehouse Indexing 49 4 Materialized Views and Query Rewrite 79 5 Oracle Dimension Objects 113 6 Partitioning and Basic Parallel Processing 137 Part II: Tuning SQL Code in a Data Warehouse 161 7 The Basics of SQL Query Code Tuning 163 8 Aggregation Using GROUP BY Clause Extensions 215 9 Analysis Reporting 249 10 Modeling with the MODEL Clause 281 Part III: Advanced Topics 317 11 Query Rewrite 319 12 Parallel Processing 335 13 Data Loading 351 14 Data Warehouse Architecture 385 A New Data Warehouse Features in Oracle Database 10g 423 B. Sample Schemas 425 C. Sample Scripting 431 D. Syntax Conventions 447 E. Sources of Information 449 Index 451 v Contents Preface xix Introduction to Data Warehouse Tuning xxiii Part I: Data Warehouse Data Modeling 1 1 The Basics of Data Warehouse Data Modeling 3 1.1 The Relational and Object Data Models 3 1.1.1 The Relational Data Model 4 Normalization 4 1st Normal Form 4 2nd Normal Form 4 3rd Normal Form 4 4th Normal Form 5 5th Normal Form 6 Referential Integrity 6 Surrogate Keys 7 Denormalization 7 Data Warehouses—Why Not the Relational Model? 10 1.1.2 The Object Data Model 10 Data Warehouses—Why Not the Object Model? 12 1.1.3 The Object-Relational Data Model 13 1.2 Data Modeling for Data Warehouses 13 1.2.1 The Container Shipment Tracking Schema 13 1.2.2 The Dimensional Data Model 15 What Is a Star Schema? 18 What Is a Snowflake Schema? 19 1.2.3 Data Warehouse Data Model Design Basics 21 Dimension Entities 22 vii viii Contents Dimension Entity Types 22 Fact Entities 24 Fact Entity Types 25 Granularity, Granularity, and Granularity 26 ...