Skip to main content
Fachgebiete
Automobil + Motoren Bauwesen + Immobilien Business IT + Informatik Elektrotechnik + Elektronik Energie + Nachhaltigkeit Finance + Banking Management + Führung Marketing + Vertrieb Maschinenbau + Werkstoffe Versicherung + Risiko
DE
EN
Bücher
Zeitschriften
Themenseiten
Organisationspsychologie Projektmanagement Marketing Smart Manufacturing
Weitere Formate
Podcasts Webinare Technik Webinare Wirtschaft Kongresse Veranstaltungskalender Awards
Jetzt Einzelzugang starten
Zugang für Unternehmen
Referenzkunden
SLX-Digitalkonferenz: Zukunftswerkstatt 2024

Mehr Umsatz mit Top-Kontakten

Am 7. Mai erfahren Sie, wie Vertriebsteams Leadgenerierung, Leadnurturing und Leadstrategien effizient steuern können.
Erweiterte Suche
Anmelden
nach oben

2013 | Buch

Core SQL Kapitel lesen Erstes Kapitel lesen

Pro Oracle SQL

verfasst von: Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten
insite
SUCHEN

Über dieses Buch

Pro Oracle SQL, Second Edition unlocks the power of SQL in the Oracle database—one of the most potent SQL implementations on the market today. To master it requires a multi-pronged approach: learn the language features, learn how and why the language features work, learn the supporting features that Oracle provides to help use the language effectively, and learn to think and work in sets.

Karen Morton has updated the content for Oracle version 12c and helps you master powerful aspects of Oracle SQL from the inside-out. You’ll learn analytic functions, the MODEL clause, and advanced grouping syntax—features that will help in creating good queries for reporting and business intelligence applications. Pro Oracle SQL, Second Edition also helps you minimize parsing overhead, read execution plans, test for correct results, understand performance management, and exert control over SQL execution in your database. You’ll learn when to create indexes, how to verify that they make a difference, how to use SQL Baselines and Profiles to optimize and stabilize SQL execution plans, and much more. You’ll also understand how SQL is optimized for working in sets, and that the key to getting accurate results lies in making sure that queries ask clear and precise questions.

Pro Oracle SQL, Second Edition helps you work at a truly professional level in the Oracle dialect of SQL. You’ll master the language, the tools to work effectively with the language, and the right way to think about a problem in SQL.

Endorsed by the OakTable Network, a group of Oracle technologists well-known for their rigorous and scientific approach to Oracle Database performance Comprehensive—goes beyond the language with a focus on what you need to know to write successful queries and data manipulation statements. Performance focused—teaches you how to measure the performance of your SQL statements and not just the syntax.

Inhaltsverzeichnis

Frontmatter
Chapter 1. Core SQL
Abstract
Whether you’re relatively new to writing SQL or you’ve been writing it for years, learning to write “good” SQL is a process that requires a strong knowledge foundation of core syntax and concepts. This chapter provides a review of the core concepts of the SQL language and its capabilities, along with descriptions of the common SQL commands with which you should already be familiar. For those of you who have worked with SQL previously and have a good grasp on the basics, this chapter will be a brief refresher to prepare you for the more detailed treatment of SQL we examine in later chapters. If you’re a new SQL user, you may want to read Beginning Oracle SQL first to make sure you’re comfortable with the basics. Either way, Chapter 1 “level sets” you with a whirlwind tour of the five core SQL statements and provides a quick review of the tool we’ll be using to execute SQL: SQL*Plus.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 2. SQL Execution
Abstract
You likely learned the mechanics of writing basic SQL in a relatively short period of time. Throughout the course of a few weeks or few months, you became comfortable with the general statement structure and syntax, how to filter, how to join tables, and how to group and order data. But, how far beyond that initial level of proficiency have you traveled? Writing complex SQL that executes efficiently is a skill that requires you to move beyond the basics. Just because your SQL gets the job done doesn’t mean it does the job well.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 3. Access and Join Methods
Abstract
The optimizer must determine how to access the data your SQL statements require. You formulate your statement and the optimizer, during a hard parse, figures out which operations should provide the data in the most effective way possible. Using statistics as the primary guide, the optimizer computes the cost of the possible alternatives first to access data and then to join multiple tables to get the final result set. The more you understand the different access and join methods the optimizer considers, the more likely you are to formulate your SQL to help the optimizer make the best choices. And, when the operation chosen by the optimizer doesn’t provide the performance you need, you can determine more accurately which operations would be more suited to produce the response times you want.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 4. SQL Is about Sets
Abstract
One of the most difficult transitions to make to become highly proficient at writing SQL well is to shift from thinking procedurally to thinking declaratively (or in sets). It is often hardest to learn to think in sets if you’ve spent time working with virtually any programming language. If this is the case for you, you are likely very comfortable with constructs such as IF-THEN-ELSE, WHILE-DO, LOOP-END LOOP, and BEGIN-END. These constructs support working with logic and data in a very procedural, step-by-step, top-down–type approach. The SQL language is not intended to be implemented from a procedural point of view, but from a set-oriented one. The longer it takes you to shift to a set-oriented point of view, the longer it takes for you to become truly proficient at writing SQL that is functionally correct and also highly optimized to perform well.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 5. It’s about the Question
Abstract
“It’s not about the query; it’s about the question.” This is one of my favorite sayings when it comes to writing SQL. Regardless of your level of proficiency, writing SQL well is as much about questions as it is about queries.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 6. SQL Execution Plans
Abstract
You’ve seen quite a few execution plans in the first chapters of this book, but in this chapter I go into detail about how to produce and read plans correctly. I’ve built the foundation of knowledge you need to understand the most common operations you’ll see used in execution plans, but now you need to put this knowledge into practice.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 7. Advanced Grouping
Abstract
The GROUP BY clause is a venerable member of the SQL statement family. After learning basic SELECT statements, it is one of first specialized parts of SQL that many practitioners cut their teeth on when learning to create aggregations from raw data and transforming that data into useful information.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 8. Analytic Functions
Abstract
The use of analytic functions, also known as windowing functions, is often overlooked even though they’ve been around since Oracle 8i. Perhaps because the primary documentation for these functions is found in the Oracle Database Data Warehousing Guide ( http://www.oracle.com/technetwork/indexes/documentation/index.html ), they are often thought useful only in data warehousing SQL. In the previous chapter, we examined how advanced grouping techniques can be used to accomplish so much that you may be wondering why you need to bother with analytic functions. Well, just like really good carpenters have numerous tools in their toolbox, so should we make sure our developer toolbox is filled with many different tools to help us write good SQL. If we limit ourselves to only a couple tools, we're bound to use constructs every now and then that are less performance friendly than others.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 9. The MODEL Clause
Abstract
The MODEL clause introduced in Oracle Database version 10g provides an elegant method to replace the spreadsheet. With the MODEL clause, it is possible to use powerful features such as aggregation, parallelism, and multidimensional, multivariate analysis in SQL statements. If you enjoy working with Excel spreadsheets to calculate formulas, you will enjoy working with the MODEL clause, too.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 10. Subquery Factoring
Abstract
You may not be familiar with the term subquery factoring. Prior to the release of Oracle 11gR2, the official Oracle documentation barely mentions it, providing just a brief synopsis of its use, a couple of restrictions, and a single example. If I instead refer to the WITH clause of the SELECT statement, you probably know immediately what I mean, because these terms are more recognizable. Both terms are used in this chapter.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 11. Semijoins and Antijoins
Abstract
Semijoins and antijoins are two closely related join methods (options of join methods, actually) that the Oracle optimizer can choose to apply when retrieving information. The SQL language is designed to specify the set of data the user wishes to retrieve, but to leave the decisions regarding how to navigate to the data up to the database itself. Therefore, there is no SQL syntax to invoke a particular join method specifically. Of course, Oracle does provide the ability to give the optimizer directives via hints. In this chapter, I explain these two join optimization options, the SQL syntax that can provoke them, requirements for and restrictions on their use, and, last, some guidance on when and how they should be used.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 12. Indexes
Abstract
Indexes are critical structures needed for efficient retrieval of rows, for uniqueness enforcement, and for the efficient implementation of referential constraints. Oracle Database provides many index types suited for different needs of application access methods. Effective choice of index type and critical choice of columns to index are of paramount importance for optimal performance. Inadequate or incorrect indexing strategy can lead to performance issues. In this chapter, I discuss basic implementation of indexes, various index types, their use cases, and strategy to choose optimal index type. Indexes available in Oracle Database as of version 12c can be classified broadly into one of three categories based on the algorithm they use: B-tree indexes, bitmap indexes, and index-organized tables, or IOTs.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 13. Beyond the SELECT
Abstract
This chapter is a collection of topics involving SQL statements that are not straight SELECTs. These statements are often referred to as Data Manipulation Language (or DML) statements. In this chapter, I provide some information on some of the less well-known options to the standard DML commands—namely, INSERT, UPDATE, DELETE, and MERGE. I also focus on alternate approaches, with an eye toward improving performance.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 14. Transaction Processing
Abstract
After you use Oracle Database for a while, you might have certain expectations of how it behaves. When you enter a query, you expect a consistent result set to be returned. If you enter a SQL statement to update several hundred records and the update of one of those rows fails, you expect the entire update to fail and all rows to be returned to their prior state. If your update succeeds and you commit your work to the database, you expect your changes to become visible to other users and remain in the database, at least until the data are updated again by someone else. You expect that when you are reading data, you never block a session from writing, and you also expect the reverse to be true. These are fundamental truths about how Oracle Database operates, and after you’ve become comfortable working with Oracle, you tend to take these truths for granted.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 15. Testing and Quality Assurance
Abstract
As you’ve worked through the chapters of this book, you may have written some code to test the examples. And because you chose this particular book instead of a “Welcome to SQL”–style book, it’s likely you have written quite a few SQL statements before you ever picked it up. As you’ve been reading this book, did some of the chapters remind you of your prior work? If so, how do you feel about the code you’ve written in the past?
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 16. Plan Stability
Abstract
One of the most frustrating things about Oracle’s Cost-Based Optimizer (CBO) is its tendency to change plans for statements at seemingly random intervals. Of course, these changes are not random at all. But, because the optimizer code is so complex, it’s often difficult to determine why a plan changes. Oracle recognized this issue years ago and has been working to improve the situation for at least a decade. It has provided many tools for identifying when plans change and why they change. Oracle has also provided numerous tools that allow you to exert varying degrees of control over the execution plans the optimizer chooses, but let’s save that discussion for the next chapter.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 17. Plan Control
Abstract
As discussed in Chapter 16 on plan stability, Oracle’s CBO can seem to change plans at random intervals, causing a great deal of frustration. However, these changes are not random at all, and we reviewed the reasons behind plan changes and examined how you can identify when plans change and why they change. This chapter’s focus, as you can probably guess, covers various techniques for controlling execution plans. I probably should say “influencing” instead of “controlling,” because there is really no foolproof method of locking in an execution plan. The tools we have at our disposal to help provide plan stability have evolved quite significantly in the more recent versions of Oracle. In this chapter, we take a walk through time and look at when plan control started and where it stands today in Oracle 12c.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Chapter 18. Miscellaneous SQL Constructs
Abstract
The SQL language offers a wide variety of constructs—from the very simple to the extremely complex. In this book, we looked at many examples that demonstrate the core topics. This chapter is devoted to a review of several use cases for constructs that should help round out your knowledge.
Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Still
Backmatter
Metadaten
Titel
Pro Oracle SQL
verfasst von
Karen Morton
Kerry Osborne
Robyn Sands
Riyaj Shamsudeen
Jared Still
Copyright-Jahr
2013
Verlag
Apress
Electronic ISBN
978-1-4302-6221-3
Print ISBN
978-1-4302-6220-6
DOI
https://doi.org/10.1007/978-1-4302-6221-3