SQL All-in-One For Dummies. 4 Ed

Title Page....3 Copyright Page....4 Table of Contents....7 Introduction....25 About This Book....25 Foolish Assumptions....26 Icons Used in This Book....27 Beyond the Book....27 Where to Go from Here....28 Book 1 Getting Started with SQL....29 Chapter 1 Understanding Relational Databases....31 Understanding Why Today’s Databases Are Better than Early Databases....32 Irreducible complexity....32 Managing data with complicated programs....32 Managing data with simple programs....35 Which type of organization is better?....35 Databases, Queries, and Database Applications....36 Making data useful....36 Retrieving the data you want — and only the data you want....36 Examining Competing Database Models....38 Looking at the historical background of the competing models....38 The hierarchical database model....39 The network database model....43 The relational database model....45 Defining what makes a database relational....45 Protecting the definition of relational databases with Codd’s rules....47 Highlighting the relational database model’s inherent flexibility....48 The object-oriented database model....49 The object-relational database model....49 The nonrelational NoSQL model....49 Why the Relational Model Won....50 Chapter 2 Modeling a System....53 Capturing the Users’ Data Model....53 Identifying and interviewing stakeholders....54 Reconciling conflicting requirements....55 Obtaining stakeholder buy-in....55 Translating the Users’ Data Model to a Formal Entity-Relationship Model....56 Entity-Relationship modeling techniques....57 Entities....57 Attributes....58 Identifiers....58 Relationships....59 Drawing Entity-Relationship diagrams....62 Maximum cardinality....63 Minimum cardinality....63 Understanding advanced ER model concepts....65 Strong entities and weak entities....65 ID-dependent entities....67 Supertype and subtype entities....67 Incorporating business rules....69 A simple example of an ER model....69 A slightly more complex example....71 Problems with complex relationships....74 Simplifying relationships using normalization....75 Translating an ER model into a relational model....75 Chapter 3 Getting to Know SQL....77 Where SQL Came From....77 Knowing What SQL Does....78 The ISO/IEC SQL Standard....79 Knowing What SQL Does Not Do....79 Choosing and Using an Available DBMS Implementation....80 Microsoft Access....81 Microsoft SQL Server....85 IBM DB2....85 Oracle Database....86 Sybase SQL Anywhere....86 MySQL....86 PostgreSQL....87 Chapter 4 SQL and the Relational Model....89 Sets, Relations, Multisets, and Tables....90 Functional Dependencies....91 Keys....92 Views....93 Users....94 Privileges....94 Schemas....95 Catalogs....96 Connections, Sessions, and Transactions....96 Routines....97 Paths....97 Chapter 5 Knowing the Major Components of SQL....99 Creating a Database with the Data Definition Language....99 The containment hierarchy....100 Creating tables....101 Specifying columns....101 Creating other objects....102 Views....102 Schemas....107 Domains....108 Modifying tables....109 Removing tables and other objects....109 Operating on Data with the Data Manipulation Language (DML)....110 Retrieving data from a database....110 Adding data to a table....111 Adding data the dull and boring way (typing it in)....112 Adding incomplete records....113 Adding data in the fastest and most efficient way: Bypassing typing altogether....113 Updating data in a table....114 Deleting data from a table....117 Updating views doesn’t make sense....118 Maintaining Security in the Data Control Language (DCL)....119 Granting access privileges....119 Revoking access privileges....120 Preserving database integrity with transactions....120 Chapter 6 Drilling Down to the SQL Nitty-Gritty....123 Executing SQL Statements....123 Interactive SQL....124 Challenges to combining SQL with a host language....125 Embedded SQL....125 Module language....128 Using Reserved Words Correctly....129 SQL’s Data Types....129 Exact numerics....130 INTEGER....131 SMALLINT....131 BIGINT....131 NUMERIC....132 DECIMAL....132 DECFLOAT....132 Approximate numerics....132 REAL....133 DOUBLE PRECISION....133 FLOAT....134 Character strings....134 CHARACTER....134 CHARACTER VARYING....135 CHARACTER LARGE OBJECT (CLOB)....135 NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT....136 Binary strings....136 BINARY....136 BINARY VARYING....137 BINARY LARGE OBJECT (BLOB)....137 Booleans....137 Datetimes....137 DATE....137 TIME WITHOUT TIME ZONE....138 TIME WITH TIME ZONE....138 TIMESTAMP WITHOUT TIME ZONE....139 TIMESTAMP WITH TIME ZONE....139 Intervals....139 XML type....140 ROW type....140 Collection types....142 ARRAY....142 Multiset....143 REF types....143 JSON types....143 User-defined types....144 Distinct types....144 Structured types....145 Data type summary....148 Handling Null Values....149 Applying Constraints....150 Column constraints....150 NOT NULL....150 UNIQUE....151 CHECK....151 Table constraints....152 Foreign key constraints....153 Assertions....155 Book 2 Developing Relational Databases....157 Chapter 1 System Development Overview....159 The Components of a Database System....160 The database....160 The database engine....161 The DBMS front end....161 The database application....161 The user....162 The System Development Life Cycle....162 Definition phase....163 Requirements phase....164 The users’ data model....165 Statement of Requirements....165 Evaluation phase....166 Determining project scope....167 Reassessing feasibility....168 Documenting the Evaluation phase....168 Design phase....168 Designing the database....169 The database application....170 Documenting the Design phase....170 Implementation phase....171 Final Documentation and Testing phase....172 Testing the system with sample data....172 Finalizing the documentation....173 Delivering the results (and celebrating)....173 Maintenance phase....174 Chapter 2 Building a Database Model....175 Finding and Listening to Interested Parties....176 Your immediate supervisor....176 The users....177 The standards organization....177 Upper management....178 Building Consensus....178 Gauging what people want....180 Arriving at a consensus....180 Building a Relational Model....180 Reviewing the three database traditions....181 Knowing what a relation is....182 Functional dependencies....182 Keys....183 Being Aware of the Danger of Anomalies....184 Eliminating anomalies....185 Examining the higher normal forms....188 The Database Integrity versus Performance Tradeoff....190 Chapter 3 Balancing Performance and Correctness....193 Designing a Sample Database....194 The ER model for Honest Abe’s....194 Converting an ER model into a relational model....196 Normalizing a relational model....196 Handling binary relationships....198 A sample conversion....203 Maintaining Integrity....206 Entity integrity....206 Domain integrity....207 Referential integrity....208 Avoiding Data Corruption....210 Speeding Data Retrievals....211 Hierarchical storage....212 Full table scans....213 Working with Indexes....213 Creating the right indexes....214 Indexes and the ANSI/ISO standard....214 Index costs....215 Query type dictates the best index....215 Point query....215 Multipoint query....215 Range query....216 Prefix match query....216 Extremal query....216 Ordering query....216 Grouping query....217 Equi-join query....217 Data structures used for indexes....217 Indexes, sparse and dense....218 Index clustering....219 Composite indexes....219 Index effect on join performance....220 Table size as an indexing consideration....220 Indexes versus full table scans....221 Reading SQL Server Execution Plans....221 Robust execution plans....221 A sample database....222 A typical query....223 The execution plan....224 Chapter 4 Creating a Database with SQL....227 First Things First: Planning Your Database....227 Building Tables....228 Locating table rows with keys....228 Using the CREATE TABLE statement....231 Setting Constraints....233 Column constraints....233 Table constraints....234 Keys and Indexes....234 Ensuring Data Validity with Domains....234 Establishing Relationships between Tables....235 Altering Table Structure....239 Deleting Tables....239 Book 3 Writing SQL Queries....241 Chapter 1 Values, Variables, Functions, and Expressions....243 Entering Data Values....243 Row values have multiple parts....244 Identifying values in a column....244 Literal values don’t change....244 Variables vary....246 Special variables hold specific values....246 Working with Functions....247 Summarizing data with set functions....247 COUNT....248 AVG....249 MAX....249 MIN....249 SUM....250 LISTAGG....250 ANY_VALUE....250 Dissecting data with value functions....251 String value functions....251 Numeric value functions....255 Datetime value functions....260 Polymorphic table functions....261 Using Expressions....261 Numeric value expressions....261 String value expressions....262 Datetime value expressions....262 Interval value expressions....263 Boolean value expressions....264 Array value expressions....265 Conditional value expressions....265 Handling different cases....265 The NULLIF special CASE....267 Bypassing null values with COALESCE....268 Converting data types with a CAST expression....268 Casting one SQL data type to another....269 Using CAST to overcome data type incompatibilities between SQL and its host language....269 Row value expressions....270 Chapter 2 SELECT Statements and Modifying Clauses....273 Finding Needles in Haystacks withthe SELECT Statement....273 Modifying Clauses....274 FROM clauses....274 WHERE clauses....275 Comparison predicates....277 BETWEEN....278 IN and NOT IN....279 LIKE and NOT LIKE....281 NULL....282 ALL, SOME, and ANY....283 EXISTS....286 UNIQUE....287 DISTINCT....287 OVERLAPS....288 MATCH....288 The MATCH predicate and referential integrity....290 Logical connectives....292 GROUP BY clauses....294 HAVING clauses....297 ORDER BY clauses....298 Tuning Queries....300 SELECT DISTINCT....301 Temporary tables....304 The ORDER BY clause....309 The HAVING clause....313 The OR logical connective....317 Chapter 3 Querying Multiple Tables with Subqueries....319 What Is a Subquery?....319 What Subqueries Do....320 Subqueries that return multiple values....320 Subqueries that retrieve rows satisfying a condition....320 Subqueries that retrieve rows that don’t satisfy a condition....321 Subqueries that return a single value....322 Quantified subqueries return a single value....325 Correlated subqueries....328 Using a subquery as an existence test....328 Introducing a correlated subquery with the IN keyword....329 Introducing a correlated subquery with a comparison operator....330 Correlated subqueries in a HAVING clause....332 Using Subqueries in INSERT, DELETE, and UPDATE Statements....333 Tuning Considerations for Statements Containing Nested Queries....336 Tuning Correlated Subqueries....342 Chapter 4 Querying Multiple Tables with Relational Operators....347 UNION....348 UNION ALL....350 UNION CORRESPONDING....350 INTERSECT....351 EXCEPT....353 JOINS....354 Cartesian product or cross join....354 Equi-join....356 Natural join....358 Condition join....359 Column-name join....359 Inner join....361 Outer join....361 Left outer join....361 Right outer join....364 Full outer join....364 ON versus WHERE....365 Join Conditions and Clustering Indexes....366 Chapter 5 Cursors....369 Declaring a Cursor....370 The query expression....371 Ordering the query result set....371 Updating table rows....373 Sensitive versus insensitive cursors....373 Scrolling a cursor....375 Holding a cursor....375 Declaring a result set cursor....375 Opening a Cursor....376 Operating on a Single Row....377 FETCH syntax....378 Absolute versus relative fetches....379 Deleting a row....379 Updating a row....379 Closing a Cursor....380 Book 4 Securing Your Data....381 Chapter 1 Protecting Against Hardware Failure and External Threats....383 What Could Possibly Go Wrong?....384 Equipment failure....384 Platform instability....386 Database design flaws....387 Data-entry errors....387 Operator error....387 Taking Advantage of RAID....387 Striping....388 RAID levels....390 RAID 0....390 RAID 1....391 RAID 5....391 RAID 10....391 Backing Up Your System....392 Preparation for the worst....392 Full or incremental backup....393 Frequency....393 Backup maintenance....393 Coping with Internet Threats....394 Viruses....394 Trojan horses....396 Worms....397 Denial-of-service attacks....398 Ransomware....398 SQL injection attacks....398 Chipping away at your wall of protection....398 Understanding SQL injection....399 Using a GET parameter....399 Recognizing unsafe configurations....407 Finding vulnerabilities on your site....407 Phishing scams....411 Zombie spambots....411 Installing Layers of Protection....412 Network-layer firewalls....412 Application-layer firewalls....412 Antivirus software....412 Vulnerabilities, exploits, and patches....412 Education....413 Alertness....413 Chapter 2 Protecting Against User Errors and Conflicts....415 Reducing Data-Entry Errors....416 Data types: The first line of defense....416 Constraints: The second line of defense....416 Sharp-eyed humans: The third line of defense....417 Coping with Errors in Database Design....417 Handling Programming Errors....418 Solving Concurrent-Operation Conflicts....418 Passing the ACID Test: Atomicity, Consistency, Isolation, and Durability....420 Operating with Transactions....421 Using the SET TRANSACTION statement....421 Starting a transaction....422 Access modes....422 Isolation levels....422 Committing a transaction....425 Rolling back a transaction....425 Why roll back a transaction?....426 The log file....426 The write-ahead log protocol....427 Checkpoints....428 Implementing deferrable constraints....428 Getting Familiar with Locking....433 Two-phase locking....433 Granularity....434 Deadlock....434 Tuning Locks....435 Measuring performance with throughput....436 Eliminating unneeded locks....436 Shortening transactions....436 Weakening isolation levels (ver-r-ry carefully)....437 Controlling lock granularity....438 Scheduling DDL statements correctly....438 Partitioning insertions....438 Cooling hot spots....439 Tuning the deadlock interval....439 Enforcing Serializability with Timestamps....439 Tuning the Recovery System....442 Chapter 3 Assigning Access Privileges....443 Working with the SQL Data Control Language....443 Identifying Authorized Users....444 Understanding user identifiers....444 Getting familiar with roles....444 Creating roles....445 Destroying roles....445 Classifying Users....446 Granting Privileges....446 Looking at data....447 Deleting data....448 Adding data....448 Changing data....448 Referencing data in another table....449 Using certain database facilities....450 Responding to an event....451 Defining new data types....451 Executing an SQL statement....451 Doing it all....451 Passing on the power....452 Revoking Privileges....452 Granting Roles....454 Revoking Roles....454 Chapter 4 Error Handling....455 Identifying Error Conditions....456 Getting to Know SQLSTATE....456 Handling Conditions....458 Handler declarations....459 Handler actions and handler effects....459 Conditions that aren’t handled....461 Dealing with Execution Exceptions: The WHENEVER Clause....461 Getting More Information: The Diagnostics Area....462 The diagnostics header area....463 The diagnostics detail area....464 Examining an Example Constraint Violation....466 Adding Constraints to an Existing Table....468 Interpreting SQLSTATE Information....468 Handling Exceptions....469 Book 5 Programming with SQL....471 Chapter 1 Database Development Environments....473 Microsoft Access....473 The Jet engine....474 DAO....474 ADO....474 ODBC....475 OLE DB....475 Files with the .mdb extension....475 The Access Database Engine....475 Microsoft SQL Server....475 IBM Db2....476 Oracle 23c....477 SQL Anywhere....477 PostgreSQL....477 MySQL....478 Chapter 2 Interfacing SQL to a Procedural Language....479 Building an Application with SQL and a Procedural Language....479 Access and VBA....480 The ADOdb library....480 The ADOX library....481 Other libraries....481 SQL Server and the .NET languages....481 MySQL and C++.NET or C#....482 MySQL and C....482 MySQL and Perl....483 MySQL and Python....483 MySQL and PHP....483 MySQL and Java....483 Oracle SQL and Java....483 Db2 and Java....484 Chapter 3 Using SQL in an Application Program....485 Comparing SQL with Procedural Languages....486 Classic procedural languages....486 Object-oriented procedural languages....487 Nonprocedural languages....487 Difficulties in Combining SQL with a Procedural Language....488 Challenges of using SQL with a classical procedural language....488 Contrasting operating modes....489 Data type incompatibilities....489 Challenges of using SQL with an object-oriented procedural language....489 Embedding SQL in an Application....490 Embedding SQL in an Oracle Pro*C application....491 Declaring host variables....493 Converting data types....493 Embedding SQL in a Java application....494 Using SQL in a Perl application....494 Embedding SQL in a PHP application....495 Using SQL with a Visual Basic .NET application....495 Using SQL with other .NET languages....496 Using SQL Modules with an Application....496 Module declarations....497 Module procedures....498 Modules in Oracle....498 Chapter 4 Designing a Sample Application....501 Understanding the Client’s Problem....502 Approaching the Problem....502 Interviewing the stakeholders....502 Drafting a detailed statement of requirements....503 Following up with a proposal....503 Determining the Deliverables....504 Finding out what’s needed now and later....505 Planning for organization growth....505 Greater database needs....505 Increased need for data security....506 Growth in the example scenario....506 Nailing down project scope....506 Building an Entity-Relationship Model....507 Determining what the entities are....508 Relating the entities to one another....508 Relationships....509 Maximum cardinality....509 Minimum cardinality....509 Business rules....510 Transforming the Model....511 Eliminating any many-to-many relationships....511 Normalizing the ER model....514 Creating Tables....515 Changing Table Structure....519 Removing Tables....519 Designing the User Interface....520 Chapter 5 Building an Application....521 Designing from the Top Down....521 Determining what the application should include....522 Designing the user interface....522 Connecting the user interface to the database....523 Coding from the Bottom Up....525 Preparing to build the application....525 Creating the database....526 Filling database tables with sample data....527 Creating the application’s building blocks....533 Developing screen forms....533 Developing reports....533 Gluing everything together....534 Testing, Testing, Testing....534 Fixing the bugs....535 Turning naive users loose....535 Bringing on the hackers....535 Fixing the newly found bugs....535 Retesting everything one last time....536 Chapter 6 Understanding SQL’s Procedural Capabilities....537 Embedding SQL Statements in Your Code....538 Introducing Compound Statements....538 Atomicity....539 Variables....540 Cursors....540 Assignment....541 Following the Flow of Control Statements....541 IF . . . THEN . . . ELSE . . . END IF....542 CASE . . . END CASE....542 Simple CASE statement....542 Searched CASE statement....543 LOOP . . . END LOOP....544 LEAVE....544 WHILE . . . DO . . . END WHILE....545 REPEAT . . . UNTIL . . . END REPEAT....545 FOR . . . DO . . . END FOR....546 ITERATE....546 Using Stored Procedures....547 Working with Triggers....548 Trigger events....549 Trigger action time....550 Triggered actions....550 Triggered SQL statement....550 Using Stored Functions....551 Passing Out Privileges....552 Using Stored Modules....552 Chapter 7 Connecting SQL to a Remote Database....555 Native Drivers....556 ODBC and Its Major Components....557 Application....558 Driver manager....559 Drivers....559 Data sources....561 What Happens When the Application Makes a Request....561 Using handles to identify objects....562 Following the six stages of an ODBC operation....562 Stage 1: The application allocates environment and connection handles in the driver manager....563 Stage 2: The driver manager finds the appropriate driver....564 Stage 3: The driver manager loads the driver....564 Stage 4: The driver manager allocates environment and connection handles in the driver....564 Stage 5: The driver manager connects to the data source through the driver....564 Stage 6: The data source (finally) executes an SQL statement....565 Book 6 Working with Advanced Data Types in SQL: XML, JSON, and PGQ....569 Chapter 1 Using XML with SQL....571 Introducing XML....572 Knowing the Parts of an XML Document....573 XML declaration....573 Elements....574 Nested elements....574 The document element....574 Empty elements....574 Attributes....575 Entity references....576 Numeric character references....577 Using XML Schema....577 Relating SQL to XML....578 Using the XML Data Type....579 When to use the XML type....579 When not to use the XML type....580 Mapping SQL to XML....581 Mapping character sets to XML....581 Mapping identifiers to XML....581 Mapping data types to XML....582 Mapping nonpredefined data types to XML....583 DOMAIN....583 DISTINCT UDT....584 ROW....585 ARRAY....586 MULTISET....587 Mapping tables to XML....588 Handling null values....588 Creating an XML schema for an SQL table....589 Operating on XML Data with SQL Functions....590 XMLELEMENT....591 XMLFOREST....591 XMLCONCAT....592 XMLAGG....592 XMLCOMMENT....593 XMLPARSE....593 XMLPI....594 XMLQUERY....594 XMLCAST....595 Working with XML Predicates....595 DOCUMENT....595 CONTENT....596 XMLEXISTS....596 VALID....596 Chapter 2 Storing XML Data in SQL Tables....599 Inserting XML Data into an SQL Pseudotable....599 Creating a Table to Hold XML Data....601 Updating XML Documents....602 Discovering Oracle’s Tools for Updating XML Data in a Table....603 APPENDCHILDXML....603 INSERTCHILDXML....604 INSERTXMLBEFORE....605 DELETEXML....606 UPDATEXML....607 Introducing Microsoft’s Tools for Updating XML Data in a Table....608 Inserting data into a table using OPENXML....608 Using updategrams to map data into database tables....609 Using an updategram namespace and keywords....609 Specifying a mapping schema....611 Implicit mapping....611 Explicit mapping....613 Elementcentric mapping....618 Attributecentric mapping....619 Mixed elementcentric and attributecentric mapping....619 Schemas that allow null values....620 Chapter 3 Retrieving Data from XML Documents....623 XQuery....624 Where XQuery came from....624 What XQuery requires....625 XQuery functionality....625 Usage scenarios....626 FLWOR Expressions....631 The for clause....632 The let clause....633 The where clause....634 The order by clause....635 The return clause....635 XQuery versus SQL....636 Comparing XQuery’s FLWOR expression with SQL’s SELECT expression....637 Relating XQuery data types to SQL data types....637 Chapter 4 Using JSON with SQL....641 Using JSON with SQL....641 The SQL/JSON Data Model....642 SQL/JSON items....642 SQL/JSON scalar....643 SQL/JSON null....643 SQL/JSON array....643 SQL/JSON object....643 SQL/JSON sequences....643 Parsing JSON....644 Serializing JSON....644 SQL/JSON Functions....644 Query functions....644 JSON value expression....645 Path expression....645 Passing clause....645 JSON output clause....646 JSON_EXISTS....646 JSON_VALUE....647 JSON_QUERY....648 JSON_TABLE....649 Constructor functions....650 JSON_OBJECT....650 JSON_OBJECTAGG....651 JSON_ARRAY....651 JSON_ARRAYAGG....652 IS JSON predicate....652 JSON nulls and SQL nulls....653 SQL/JSON Path Language....653 SQL:2023 JSON Enhancements....653 The JSON data type....654 Additional functions for the JSON data type....655 Chapter 5 Exploring Property Graph Queries....657 What Are Property Graph Queries?....657 Looking at node and edge properties....658 Connecting nodes by multiple edges....658 Using edges to connect a node to itself....659 Following paths with SQL queries....659 Examining SQL/PGQ....659 Working with SQL/PGQ....661 Building the property graph tables....661 Adding data to node and edge tables....664 Querying data in graph tables....666 Book 7 Optimizing Your Database....669 Chapter 1 Tuning the Database....671 Analyzing the Workload....672 Considering the Physical Design....673 Choosing the Right Indexes....674 Avoiding unnecessary indexes....674 Choosing a column to index....675 Using multicolumn indexes....676 Clustering indexes....676 Choosing an index type....678 Weighing the cost of index maintenance....678 Using composite indexes....678 Tuning Indexes....679 Tuning Queries....680 Tuning Transactions....681 Separating User Interactions from Transactions....681 Minimizing Traffic between Application and Server....682 Precompiling Frequently Used Queries....682 Chapter 2 Tuning the Environment....683 Surviving Failures with Minimum Data Loss....684 What happens to transactions when no failure occurs?....684 What happens when a failure occurs and a transaction is still active?....685 Tuning the Recovery System....685 Volatile and nonvolatile memory....686 Memory system hierarchy....687 Putting logs and transactions on different disks....688 Hard disk drive construction....689 Hard disk drive performance considerations....689 Tuning write operations....691 Performing database dumps....692 Setting checkpoints....693 Optimizing batch transactions....694 Tuning the Operating System....694 Scheduling threads....694 Context switching....696 Round-robin scheduling....696 Priority-based scheduling....696 Priority inversion....697 Deadlock....698 Determining database buffer size....698 Tuning the page usage factor....699 Maximizing the Hardware You Have....699 Optimizing the placement of code and data on hard disks....700 Tuning the page replacement algorithm....700 Tuning the disk controller cache....701 Adding Hardware....701 Faster processor....702 More RAM....702 Faster hard disks....702 More hard disks....703 Solid State Disk (SSD)....703 RAID arrays....703 Working in Multiprocessor Environments....704 Chapter 3 Finding and Eliminating Performance Bottlenecks....705 Pinpointing the Problem....706 Slow query....706 Slow update....706 Determining the Possible Causes of Trouble....707 Problems with indexes....707 B+ tree indexes....707 Index pluses and minuses....707 Index-only queries....708 Full table scans versus indexed table access....708 Pitfalls in communication....709 ODBC/JDBC versus native drivers....709 Locking and client performance....710 Application development tools making suboptimal decisions....710 Determining whether hardware is robust enough and configured properly....711 Implementing General Principles: A First Step Toward Improving Performance....711 Avoid direct user interaction....711 Examine the application/database interaction....712 Don’t ask for columns that you don’t need....712 Don’t use cursors unless you absolutely have to....713 Precompiled queries....713 Tracking Down Bottlenecks....713 Isolating performance problems....714 Performing a top-down analysis....714 DBMS operations....714 Hardware....715 Partitioning....716 Locating hotspots....717 Analyzing Query Efficiency....717 Using query analyzers....717 The Database Engine Tuning Advisor....721 SQL Server Profiler....723 The Oracle Tuning Advisor....725 Finding problem queries....725 Analyzing a query’s access plan....726 Examining a query’s execution profile....728 Managing Resources Wisely....729 The disk subsystem....729 The database buffer manager....730 The logging subsystem....731 The locking subsystem....731 Book 8 Appendixes....733 Appendix A SQL:2023 Reserved Words....735 Appendix B Glossary....745 Index....755 EULA....803