Muutke küpsiste eelistusi

E-raamat: Pro Oracle SQL Development: Best Practices for Writing Advanced Queries

4.50/5 (10 hinnangut Goodreads-ist)
  • Formaat: PDF+DRM
  • Ilmumisaeg: 09-May-2019
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484245170
Teised raamatud teemal:
  • Formaat - PDF+DRM
  • Hind: 61,74 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Lisa ostukorvi
  • Lisa soovinimekirja
  • See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
Pro Oracle SQL Development: Best Practices for Writing Advanced QueriesSuurem pilt
  • Formaat: PDF+DRM
  • Ilmumisaeg: 09-May-2019
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484245170
Teised raamatud teemal:

DRM piirangud

  • Kopeerimine (copy/paste):

    ei ole lubatud

  • Printimine:

    ei ole lubatud

  • Kasutamine:

    Digitaalõiguste kaitse (DRM)
    Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale  Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

    Vajalik tarkvara
    Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

    PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

    Seda e-raamatut ei saa lugeda Amazon Kindle's. 

Write SQL statements that are more powerful, simpler, and faster using Oracle SQL and its full range of features. This book provides a clearer way of thinking about SQL by building sets, and provides practical advice for using complex features while avoiding anti-patterns that lead to poor performance and wrong results. Relevant theories, real-world best practices, and style guidelines help you get the most out of Oracle SQL.

Pro Oracle SQL Development is for anyone who already knows Oracle SQL and is ready to take their skills to the next level. Many developers, analysts, testers, and administrators use Oracle databases frequently, but their queries are limited because they do not have the knowledge, experience, or right environment to help them take full advantage of Oracle’s advanced features. This book will inspire you to achieve more with your Oracle SQL statements through tips for creating your own style for writing simple, yet powerful, SQL. It teaches you how to think about and solve performance problems in Oracle SQL, and covers advanced topics and shows you how to become an Oracle expert.


What You'll Learn
  • Understand the power of Oracle SQL and where to apply it
  • Create a database development environment that is simple, scalable, and conducive to learning
  • Solve complex problems that were previously solved in a procedural language
  • Write large Oracle SQL statements that are powerful, simple, and fast
  • Apply coding styles to make your SQL statements more readable
  • Tune large Oracle SQL statements to eliminate and avoid performance problems

Who This Book Is For

Developers, testers, analysts, and administrators who want to harness the full power of Oracle SQL to solve their problems as simply and as quickly as possible. For traditional database professionals the book offers new ways of thinking about the language they have used for so long. For modern full stack developers the book explains how a database can be much more than simply a place to store data.

About the Author xxiii
About the Technical Reviewer xxv
Acknowledgments xxvii
Introduction xxix
Part I Learn How to Learn 1(104)
Chapter 1 Understand Relational Databases
3(26)
History of Relational Databases
3(3)
Relational Model and Why It Matters
6(3)
History
6(1)
Terminology
7(1)
Simplicity
7(1)
Sets and Tables
8(1)
Problems Implementing a Relational Model
8(1)
Relational Model and Why It Doesn't Matter
9(3)
The NULL Problem Isn't a Problem
9(2)
Column Order Matters
11(1)
Denormalization
11(1)
All Rows Are Distinct
12(1)
SQL Programming Language
12(5)
History and Terminology
12(1)
SQL Alternatives
13(3)
Is SQL a Programming Language?
16(1)
Different Database Types
17(2)
Alternative Database Models
17(1)
Different Oracle Databases (OLTP vs. DW)
18(1)
Key Concepts
19(9)
NULL
20(2)
JOIN
22(6)
Summary
28(1)
Chapter 2 Create an Efficient Database Development Process
29(22)
Shared Database vs. Private Database
29(1)
Create an Infinite Number of Databases
30(6)
Advantages of Private Databases
31(3)
How to Implement Private Databases
34(2)
Rapidly Drop and Recreate Schemas
36(6)
Why Deploy Often?
36(1)
How to Deploy Often?
37(1)
SQL*Plus Installation Scripts
38(2)
SQL*Plus Patch Scripts
40(2)
Control and Integrate Schemas with Version-Controlled Text Files
42(4)
Single Source of Truth
42(1)
Load Objects from the Repository and File System
43(1)
Create and Save Changes Manually
44(2)
Empower Everyone
46(4)
Power Imbalance Between Developers and Administrators
47(1)
Improve Communication
48(1)
Transparency
49(1)
Lower Barriers to Entry
49(1)
Summary
50(1)
Chapter 3 Increase Confidence and Knowledge with Testing
51(26)
Build Confidence with Automated Tests
51(8)
Fix Bugs Faster
51(1)
Gain Confidence, Avoid Biases
52(1)
Test-Driven Development
53(1)
Create Useful Test Data
53(2)
Create Large Test Data
55(1)
Remove Test Data
56(1)
How to Build Automated Tests
56(3)
Build Knowledge with Minimal, Complete, and Verifiable Examples
59(8)
Why Spend So Much Time Building Reproducible Test Cases?
59(1)
Minimal
60(1)
Complete
61(1)
Verifiable
62(2)
Sharing Tests
64(1)
Avoiding the XY Problem
65(1)
Oracle Performance Testing
65(2)
Oracle Detective Kit
67(8)
Data Dictionary Views
67(2)
Dynamic Performance Views
69(3)
Relational Tools for Inspecting Databases
72(2)
Non-relational Tools for Inspecting Databases
74(1)
Summary
75(2)
Chapter 4 Find Reliable Sources
77(10)
Places to Go
78(6)
The Problems with Forums
78(1)
The Problems with Static Websites
79(1)
Read the Manual
79(3)
The Manual Is Not Perfect
82(1)
My Oracle Support
83(1)
People to See
84(1)
Summary
85(2)
Chapter 5 Master the Entire Stack
87(18)
Not Just Faster
87(2)
Typing
89(1)
Operating Systems and Supporting Programs
89(4)
Operating Systems
90(1)
Text Editors
90(1)
Comparison Tools
91(1)
Reporting Tools and Excel
92(1)
SQL and PL/SQL
93(1)
SQL*Plus
94(2)
When We Should Use SQL*Plus
94(1)
When We Should Not Use SQL*Plus
95(1)
Integrated Development Environment
96(3)
Learn an IDE
97(1)
When Not to Use an IDE Feature
98(1)
Oracle IDE Comparison
98(1)
Worksheets, Notebooks, Snippets, Scripts, and Gists
99(4)
Get Organized
99(1)
Worksheets
100(3)
Summary
103(2)
Part II Write Powerful SQL with Sets and Advanced Features 105(186)
Chapter 6 Build Sets with Inline Views and ANSI Join Syntax
107(20)
Spaghetti Code from Nonstandard Syntax
107(5)
Hard to Read Old Syntax
108(1)
Hard to Debug Old Syntax
109(1)
Accidental Cross Joins in Old Syntax
110(1)
Nonstandard but Still Useful
111(1)
Too Much Context
112(3)
The Importance of Limiting Context
112(1)
Avoid Correlated Subqueries
113(1)
Avoid Common Table Expressions
114(1)
Sets, Chunking, and Functional Programming to the Rescue
115(4)
Sets
115(1)
Chunking
116(2)
Functional Programming
118(1)
Inline Views
119(3)
What Is an Inline View?
119(1)
Inline Views Make Code Bigger but Simpler
120(1)
Simple Inline Views for a Large Example
121(1)
ANSI Joins
122(1)
Example
123(3)
Summary
126(1)
Chapter 7 Query the Database with Advanced SELECT Features
127(64)
Operators, Functions, Expressions, and Conditions
128(3)
Semantics
128(1)
How to Know When We're Missing Something
128(1)
Precedence Rules
129(1)
Simplify
130(1)
CASE and DECODE
131(2)
Joins
133(7)
Partitioned Outer Joins
134(2)
Lateral, Cross Apply, and Outer Apply
136(1)
Equijoin or Non-equijoin
136(1)
Semi-join or Anti-join
136(2)
Self-joins
138(1)
Natural Joins and USING Considered Harmful
139(1)
Sorting
140(2)
Sorting Syntax
140(2)
Sorting Performance, Resources, and Implicit Sorting
142(1)
Set Operators
142(4)
UNION and UNION ALL
143(1)
INTERSECT and MINUS
144(1)
Set Operator Complications
145(1)
Advanced Grouping
146(4)
ROLLUP, GROUP*, CUBE
146(2)
LISTAGG
148(1)
Advanced Aggregate Functions
149(1)
Analytic Functions
150(4)
Analytic Function Syntax
150(1)
Analytic Function Examples
151(3)
Regular Expressions
154(5)
Regular Expression Syntax
154(1)
Regular Expression Examples
155(3)
Regular Expression Limitations
158(1)
Row Limiting
159(3)
Row Limiting Clause
159(1)
ROWNUM
160(1)
Analytic Function Row Limiting
161(1)
Pivoting and Unpivoting
162(6)
Old Pivot Syntax
163(1)
New Pivot Syntax
164(1)
UNPIVOT
165(3)
Table References
168(2)
Flashback
168(1)
Sample
169(1)
Partition Extension Clause
169(1)
Common Table Expressions
170(4)
Example
170(2)
PL/SQL Common Table Expressions
172(1)
Performance and Over-use
173(1)
Recursive Queries
174(3)
CONNECT BY Syntax
174(2)
Recursive Common Table Expressions
176(1)
XML
177(5)
XMLType
177(2)
DBMS_XMLGEN and Creating XML
179(1)
XMLTABLE
180(1)
XML Programming Languages
181(1)
JSON
182(3)
Build and Store JSON in the Database
182(2)
Querying JSON
184(1)
National Language Support
185(5)
Character Sets
186(1)
Length Semantics
187(1)
NLS Comparing and Sorting
188(1)
Display Formats
189(1)
Summary
190(1)
Chapter 8 Modify Data with Advanced DML
191(28)
INSERT
192(1)
UPDATE
193(1)
DELETE
194(2)
MERGE
196(2)
Updatable Views
198(1)
DML Hints
199(2)
Error Logging
201(2)
Returning
203(1)
TRUNCATE
204(3)
COMMIT, ROLLBACK, and SAVEPOINT
207(2)
ALTER SYSTEM
209(2)
ALTER SESSION
211(1)
Input and Output
212(2)
Useful PL/SQL Packages
214(3)
Summary
217(2)
Chapter 9 Improve the Database with Advanced Oracle Schema Objects
219(46)
ALTER
219(2)
Tables
221(10)
Table Types
221(4)
Table Properties
225(4)
ALTER and DROP Table
229(1)
Column Types and Properties
229(2)
Constraints
231(6)
Constraint Performance Impact
232(1)
Altering Constraints
232(1)
Constraint Exceptions
233(1)
NOVALIDATE and Parallel Constraints
234(2)
Other Constraints
236(1)
Indexes
237(6)
Index Concepts
237(2)
Index Features
239(3)
Rebuilding Indexes
242(1)
Partitioning
243(5)
Partitioning Concepts
243(3)
Partitioning Features
246(2)
Views
248(2)
Creating Views
248(1)
Expanding Views
249(1)
Users
250(2)
Sequences
252(2)
Synonyms
254(1)
Materialized Views
254(3)
Materialized Views for Multi-table Constraints
255(2)
Database Links
257(2)
PL/SQL Objects
259(1)
Other Schema Objects
260(1)
Global Objects
260(1)
GRANT and REVOKE
261(2)
Summary
263(2)
Chapter 10 Optimize the Database with Oracle Architecture
265(26)
Redo
265(3)
Redo in Theory
266(1)
Redo in Practice
266(2)
Undo and Multiversion Read Consistency
268(4)
Undo for Rollback
268(2)
Undo for Multiversion Consistency
270(2)
Storage Structures
272(10)
Column Values
273(1)
Row Pieces
274(1)
Blocks and Row-Level Locking
275(2)
Extents
277(1)
Segments
277(1)
Data Files
278(1)
Tablespaces
279(1)
Automatic Storage Management
280(1)
Wasted Space
281(1)
Temporary Tablespace
282(1)
Memory
283(2)
Caches
285(2)
Database Types
287(2)
Summary
289(2)
Part III Write Elegant SQL with Patterns and Styles 291(88)
Chapter 11 Stop Coding and Start Writing
293(16)
The Hypocrisy of Examples
294(1)
Comments
295(3)
Comment Styles
295(1)
Comment Mechanics
296(1)
Comment ASCII Art
297(1)
Choose Good Names
298(3)
Name Styles
298(1)
Avoid Quoted Identifiers
299(1)
Name Length and Changes
300(1)
Whitespace
301(1)
Make Bugs Obvious
302(5)
Fail Fast
303(1)
Avoid Pokemon Exception Handling
303(2)
Use Bad Names and Weird Values
305(1)
Use Fragile SQL
306(1)
The Path to Writing Good SQL
307(1)
Summary
308(1)
Chapter 12 Write Large SQL Statements
309(18)
Imperative Programming Size Limits Do Not Apply
309(1)
One Large SQL Statement vs. Multiple Small SQL Statements
310(2)
Performance Risks of Large SQL Statements
312(4)
Large SQL Parsing Problems
312(1)
Large SQL Increases Optimizer Risks
313(2)
Large SQL Resource Consumption Problems
315(1)
Performance Benefits of Large SQL Statements
316(5)
Large SQL Improves Clarity
316(1)
Large SQL Increases Optimizer Opportunities
316(2)
Large SQL Reduces Input/Output
318(1)
Large SQL Reduces Context Switches
318(1)
Large SQL Improves Parallelism
319(2)
Reading and Debugging Large SQL Statements
321(4)
Inside Out
321(1)
Navigating Inline Views
322(3)
Summary
325(2)
Chapter 13 Write Beautiful SQL Statements
327(12)
How to Measure Code Complexity
328(1)
Avoid Unnecessary Aliases
329(1)
Prefixes and Suffixes
330(2)
Object and Variable Names
331(1)
Referencing Tables and Columns
331(1)
Avoid Abbreviations
332(1)
Use Tabs for Left Alignment
333(3)
Avoid Code Formatters
336(1)
Lower Case
337(1)
Summary
338(1)
Chapter 14 Use SQL More Often with Basic Dynamic SQL
339(16)
When to Use Dynamic SQL
339(5)
Running DDL
340(1)
Unknown Until Run Time
341(1)
Simplify Privileges
342(1)
Rule Engines
343(1)
When Not to Use Dynamic SQL
343(1)
Basic Features
344(1)
Bind Variables for Performance and Security
345(2)
How to Simplify String Concatenation
347(4)
Multiline Strings
347(1)
Alternative Quoting Mechanism
348(2)
Templating
350(1)
Code Generation, Not Generic Code
351(2)
Summary
353(2)
Chapter 15 Avoid Anti-Patterns
355(24)
Avoid Second System Syndrome and Rewriting from Scratch
355(2)
Avoid Stringly Typed Entity-Attribute-Value Model
357(4)
EAV Pros and Cons
357(1)
Never Use the Wrong Type
358(1)
Subtle Conversion Bugs in Oracle SQL
359(2)
Avoid Soft Coding
361(1)
Avoid Object-Relational Tables
362(2)
Avoid Java in the Database
364(1)
Java Is Not Always Available
364(1)
Java Does Not Fit Perfectly
364(1)
SQL and PL/SQL Are Almost Always Better Choices
365(1)
Avoid TO DATE
365(3)
Avoid String-to-Date Conversion
366(1)
Use DATE, TIMESTAMP, and INTERVAL Literals
367(1)
Avoid CURSOR
368(2)
Avoid Custom SQL Parsing
370(2)
Avoid Automating Everything
372(1)
Avoid Cargo Cult Syntax
373(1)
Avoid Undocumented Features
373(1)
Avoid Deprecated Features
374(1)
Avoid Simplistic Explanations for Generic Errors
374(3)
Dead Processes
375(1)
Deadlocks
375(1)
Top of the Error Stack
376(1)
Avoid Unnecessarily Small Parameters
377(1)
Anti-Patterns Discussed in Other
Chapters
378(1)
Summary
378(1)
Part IV: Improve SQL Performance 379(126)
Chapter 16 Understand SQL Performance with Algorithm Analysis
381(30)
Algorithm Analysis Introduction
382(4)
O(1/N) — Batching to Reduce Overhead
386(2)
O(1) — Hashing, Other Operations
388(4)
How Hashing Works
388(2)
Hash Partitioning
390(1)
Hash Clusters
391(1)
Hash Joins
392(1)
Other
392(1)
O(LOG(N)) — Index Access
393(2)
1/((1-P)+P/N) — Amdahl's Law
395(2)
O(N) — Full Table Scans, Other Operations
397(1)
O(N*LOG(N)) — Full Table Scan vs. Index, Sorting, Joining, Global vs. Local Index, Gathering Statistics
398(9)
Full Table Scan vs. Index
399(1)
Sorting
400(1)
Joining
401(3)
Global vs. Local Index
404(1)
Gathering Optimizer Statistics
404(3)
O(N^2) — Cross Joins, Nested Loops, Other Operations
407(2)
O(N!) — Join Order
409(1)
O(infinity) —The Optimizer
409(1)
Summary
410(1)
Chapter 17 Understand SQL Tuning Theories
411(38)
Managing User Expectations
411(1)
Performance Tuning State of Mind
412(4)
Performance Tuning Is Not Debugging
413(1)
Motivated Troubleshooting
413(1)
Different Approaches
414(2)
Why Not Database Tuning?
416(1)
Declarative Programming (Why Execution Plans Are Important)
416(2)
Declarative Quirks
416(1)
Execution Plans
417(1)
Operations (What Execution Plan Decisions Are Available)
418(14)
Operation Details
419(1)
Execution Plans and Recursive SQL
419(1)
Why Operations Matter
420(1)
First Operations
421(1)
Joining
421(2)
Table Access
423(1)
Index Access
424(1)
Grouping and Sorting
425(1)
Set Operators
425(1)
Optimizer Statistics
426(1)
Parallel
426(3)
Partition
429(1)
Filter
430(1)
Other
431(1)
Cardinality and Optimizer Statistics (Building Execution Plans I)
432(8)
Cardinality Is Important
433(2)
Cardinality Differences
435(1)
Cost Doesn't Matter
436(1)
Optimizer Statistics
436(2)
Optimizer Statistics Example
438(2)
Transformations and Dynamic Optimizations (Building Execution Plans II)
440(7)
Transformations
440(2)
Adaptive Cursor Sharing and Adaptive Statistics
442(2)
Adaptive Query Plans
444(3)
Clear, Simple, and Wrong
447(1)
Summary
448(1)
Chapter 18 Improve SQL Performance
449(56)
Application Tuning - Logging and Profiling
449(5)
Logging
450(1)
Profiling - DBMS_PROFILER
451(2)
Profiling - DBMS_HPROF
453(1)
Application Tuning Through Batching
454(5)
Installation and Patch Scripts
455(2)
OLTP Applications
457(1)
Data Warehouses
458(1)
Database Tuning
459(11)
Measure Database Performance
460(3)
Automatic Workload Repository (AWR)
463(2)
Active Session History (ASH)
465(1)
Automatic Database Diagnostic Monitor (ADDM)
466(2)
Advisors
468(1)
Other Tools
469(1)
SQL Tuning - Find Slow SQL
470(3)
Get Organized
470(1)
Slow Is Based on DB Time
470(1)
Find Currently Running Slow SQL
471(2)
Find Historically Slow SQL
473(1)
SQL Tuning - Find Execution Plans
473(7)
Graphical Execution Plans Considered Harmful
473(2)
Text Is Best
475(2)
DBMS_XPLAN Functions
477(1)
DBMS_XPLAN FORMAT Parameter
478(1)
Note Section
479(1)
Other Ways to Get Execution Plans
480(1)
SQL Tuning - Find Actual Times and Cardinalities for Operations
480(13)
GATHER_PLAN_STATISTICS
481(4)
Real-Time SQL Monitor Reports (Text)
485(3)
Real-Time SQL Monitor Reports (Active)
488(1)
Degree of Parallelism
489(2)
What to Look for in Execution Plans
491(2)
SQL Tuning - Changing Execution Plans
493(6)
Changing Execution Plans
493(2)
Hints
495(2)
SQL Profile Example
497(2)
SQL Tuning - Gathering Optimizer Statistics
499(4)
Manual Statistics
499(2)
Automatic Statistics
501(1)
Other Statistics
501(2)
Summary
503(2)
Part V Solve Anything with Oracle SQL 505(74)
Chapter 19 Solve Challenging Problems with Arcane SQL Features
507(16)
Oracle vs. the Unix Philosophy
507(1)
MODEL
508(4)
Row Pattern Matching
512(1)
Any Types
513(2)
APEX
515(2)
Oracle Text
517(2)
Other Features
519(2)
Advanced Analytics (Data Mining)
519(1)
Spatial
519(1)
OLAP
519(1)
Property Graph
520(1)
Virtual Private Database
520(1)
Database In-Memory
521(1)
Advanced Compression
521(1)
Summary
521(2)
Chapter 20 Use SQL More Often with Advanced Dynamic SQL
523(14)
Parsing
523(5)
PL/Scope
524(1)
PLSQL_LEXER
525(1)
ANTLR
526(2)
DBMS SQL
528(1)
DBMS_XMLGEN
529(2)
PL/SQL Common Table Expressions
531(1)
Method4 Dynamic SQL
532(1)
Polymorphic Table Functions
533(1)
Method5
534(1)
Summary
535(2)
Chapter 21 Level Up Your Skills with PL/SQL
537(42)
Is PL/SQL Worth Mastering?
537(1)
The Focus Is Still on SQL
538(1)
Create a PL/SQL Playground
538(1)
PL/SQL Integration Features
539(36)
Tips for Packaging Code
540(1)
Session Data
540(3)
Transactions I - COMMIT, ROLLBACK, and SAVEPOINT
543(2)
Transactions II - Implicit Cursor Attributes
545(1)
Transactions III - Row-Level Locking
546(1)
Transactions IV - Isolation and Consistency
547(2)
Simple Variables
549(2)
Cursors
551(3)
Records
554(2)
Collections
556(3)
Functions
559(2)
Table Functions
561(2)
Pipelined Functions
563(1)
Parallel Pipelined Functions
564(1)
Autonomous Transactions for DML and DDL
565(1)
Autonomous Transactions for Logging
566(2)
Definer's Rights vs. Invoker's Rights
568(1)
Triggers
569(5)
Conditional Compilation
574(1)
Other PUN.. Features
575(1)
Start Teaching and Creating
575(4)
Teach Others
576(1)
Create Open Source Projects
576(3)
Part VI: Appendices 579(6)
Appendix A: SQL Style Guide Cheat Sheet
581(2)
Appendix B: Computer Science Topics
583(2)
Index 585
Jon Heller is an expert SQL and PL/SQL programmer with 17 years of Oracle experience. During that time he has worked as a database analyst, developer, and administrator. In his spare time he is active on Stack Overflow where he is a top user in the Oracle and PL/SQL tags. He enjoys creating open source software for Oracle, such as the remote execution program Method5. He has a master of computer science degree from North Carolina State University and lives in Iowa with his wife and two sons.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814842451702e.html
Märksõnad: