深入理解Oracle SQL：核心概念与实践

需积分: 9 81 浏览量更新于2024-07-18 收藏 3.53MB PDF 举报

"Pro Oracle SQL 2nd Edition.pdf" 是一本深入探讨Oracle SQL的书籍，适合SQL新手和有经验的用户提升技能。本书涵盖了SQL的基础到高级特性，旨在帮助读者更好地理解和优化SQL查询。关于作者和技术审阅者: 书中包含关于作者和审阅者的介绍，他们可能具有丰富的数据库管理和SQL开发经验，为书中的内容提供了专业知识和保障。核心内容概览: 1. **Chapter 1: Core SQL** - 这一章主要介绍了SQL的基础概念和语句，包括数据定义（DDL）、数据操作（DML）以及基本的查询语句，如SELECT、INSERT、UPDATE和DELETE。 2. **Chapter 2: SQL Execution** - 讨论了SQL语句的执行过程，包括解析、优化和执行阶段，帮助读者理解Oracle如何处理SQL请求。 3. **Chapter 3: Access and Join Methods** - 详细讲解了访问方法和联接技术，包括嵌套循环、索引扫描、哈希联接和归并联接等，以及何时选择合适的联接策略。 4. **Chapter 4: SQL Is about Sets** - 强调SQL是集合操作的语言，讨论了集合运算如并集（UNION）、交集（INTERSECT）和差集（EXCEPT），以及子查询在集合操作中的应用。 5. **Chapter 5: It’s about the Question** - 关注如何根据问题构建有效的SQL查询，强调理解业务需求和数据模型对编写正确查询的重要性。 6. **Chapter 6: SQL Execution Plans** - 揭示了SQL执行计划的内部工作原理，包括成本估算、CBO（成本基础优化器）和访问路径的选择。 7. **Chapter 7: Advanced Grouping** - 探讨了复杂的分组操作，包括GROUP BY、HAVING和ROLLUP/GROUPING SETS等。 8. **Chapter 8: Analytic Functions** - 介绍了窗口函数，如RANK、LEAD、LAG、ROW_NUMBER和SUM OVER等，用于复杂的数据分析和排序。 9. **Chapter 9: The MODEL Clause** - 详细讲解了MODEL子句，这是一个强大的工具，用于解决复杂的数据建模和计算问题。 10. **Chapter 10: Subquery Factoring** - 介绍了子查询因子（Subquery Factoring, WITH子句），以提高查询的可读性和效率。 11. **Chapter 11: Semijoins and Antijoins** - 讨论了半联接和逆联接，这些是优化查询性能的重要技巧，特别是在大型数据集中。 12. **Chapter 12: Indexes** - 深入讲解了索引的类型、创建和使用，以及如何通过索引来优化查询性能。 13. **Chapter 13: Beyond the SELECT** - 超出SELECT语句的范围，探讨了事务处理、存储过程、触发器和其他数据库对象的使用。 14. **Chapter 14: Transaction Processing** - 阐述了事务管理的概念，包括ACID属性、并发控制和恢复机制。 15. **Chapter 15: Testing and Quality Assurance** - 提供了关于测试SQL脚本和确保代码质量的最佳实践。 16. **Chapter 16: Plan Stability** - 讨论了计划稳定性问题，解释了如何保持SQL执行计划的一致性。 17. **Chapter 17: Plan Control** - 介绍了计划控制技术，如SQL Profile、SQL Plan Baseline和SQL Plan Directives，以管理优化器的行为。 18. **Chapter 18: Miscellaneous SQL Constructs** - 包含了其他各种SQL构造和用法，可能是特定场景下的解决方案或优化技巧。最后，书中有详细的索引，方便读者查找和查阅特定主题。这本书全面而深入地覆盖了Oracle SQL的各个方面，是数据库管理员、开发人员和数据分析师提升SQL技能的宝贵资源。

CHAPTER 1 ■ CORE SQL

Listing 1-13. Partial Query Execution through the GROUP BY Clause

SQL> select c.customer_id, count(o.order_id) as orders_ct

2 from oe.customers c

3 join oe.orders o

4 on c.customer_id = o.customer_id

5 where gender = 'F'

6 group by c.customer_id;

CUSTOMER_ID ORDERS_CT

----------- ----------

156 1

123 1

166 1

154 1

169 1

105 4

103 4

107 4

104 4

147 5

146 5

11 rows selected.

Notice that the output from the query, although grouped, is not ordered. The display makes it appear as though

the rows are ordered by order_ct, but this is more coincidence and not guaranteed behavior. This is an important

item to remember; the GROUP BY clause does not ensure ordering of data. If you want the list to display in a specific

order, you have to specify an ORDER BY clause.

The HAVING Clause

The HAVING clause restricts the grouped summary rows to those for which the condition(s) in the clause are TRUE.

Unless you include a HAVING clause, all summary rows are returned. The GROUP BY and HAVING clauses are actually

interchangeable positionally (it doesn’t matter which one comes first). However, it seems to make more sense to code

them with the GROUP BY clause first, because GROUP BY is processed logically first. Essentially, the HAVING clause is

a second WHERE clause that is evaluated after GROUP BY occurs, and is used to filter on grouped values. Because the

WHERE clause is applied before the GROUP BY occurs, you cannot filter grouped results in the WHERE clause; you must

use the HAVING clause instead.

In our example query, the HAVING clause, HAVING COUNT(o.order_id) > 4, limits the grouped result data of

11 rows down to two rows. You can confirm this by reviewing the list of rows returned after GROUP BY is applied,

as shown in Listing 1-13. Note that only customers 146 and 147 have placed more than four orders. The two rows that

make up the final result set are now ready.

The SELECT List

The SELECT list is where the columns included in the final result set from your query are provided. A column can be an

actual column from a table, an expression, or even the result of a SELECT statement, as shown in Listing 1-14.

CHAPTER 1 ■ CORE SQL

Listing 1-14. Example Query Showing SELECT List Alternatives

SQL> select c.customer_id, c.cust_first_name||' '||c.cust_last_name,

2 (select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id) acct_mgr)

3 from oe.customers c;

CUSTOMER_ID CUST_NAME ACCT_MGR

--------------- ----------------------------------------- --------------

147 Ishwarya Roberts Russell

148 Gustav Steenburgen Russell

...

931 Buster Edwards Cambrault

981 Daniel Gueney Cambrault

319 rows selected.

When another SELECT statement is used to produce the value of a column, the query must return only one row

and one column value. These types of subqueries are referred to as scalar subqueries. Although this can be very useful

syntax, keep in mind that the scalar subquery is executed once for each row in the result set. There are optimizations

available that may eliminate some duplicate executions of the subquery, but the worst-case scenario is that each row

requires this scalar subquery to be executed. Imagine the possible overhead involved if your result set had thousands,

or millions, of rows! I review scalar subqueries later in the book and discuss how to use them optimally.

Another option you may need to use in the SELECT list is the DISTINCT clause. The example provided here doesn’t

use it, but I want to mention it briefly. The DISTINCT clause causes duplicate rows to be removed from the dataset

produced after the other clauses have been processed.

After the SELECT list is processed, you now have the final result set for your query. The only thing that remains to

be done, if it is included, is to sort the result set into a desired order.

The ORDER BY Clause

The ORDER BY clause is used to order the final set of rows returned by the statement. In this case, the requested sort

order was to be by orders_ct and customer_id. The orders_ct column is the value computed using the COUNT

aggregate function in the GROUP BY clause. As shown in Listing 1-13, there are two customers that each placed more

than four orders. Because each customer placed five orders, the order_ct is the same, so the second ordering column

determines the final display order. As shown in Listing 1-15, the final sorted output of the query is a two-row dataset

ordered by customer_id.

Listing 1-15. Example Query Final Output

SQL> select c.customer_id, count(o.order_id) as orders_ct

2 from oe.customers c

3 join oe.orders o

4 on c.customer_id = o.customer_id

5 where c.gender = 'F'

6 group by c.customer_id

7 having count(o.order_id) > 4

8 order by orders_ct, c.customer_id

9 /

CUSTOMER_ID ORDERS_CT

----------- ----------

146 5

147 5

CHAPTER 1 ■ CORE SQL

When ordered output is requested, Oracle must take the final set of data after all other clauses have been

processed and sort them as specified. The size of the data that needs to be sorted is important. When I say size, I mean

total bytes of data in the result set. To estimate the size of the dataset, multiply the number of rows by the number of

bytes per row. The bytes per row are determined by summing the average column lengths of each of the columns in

the SELECT list.

The example query requests only the customer_id and orders_ct column values in the SELECT list. Let’s use ten

as our estimated bytes-per-row value. In Chapter 6, I show you where to find the optimizer’s estimate for this value.

So, given that we only have two rows in the result set, the sort size is actually quite small, approximately 20 bytes.

Remember, this is only an estimate, but the estimate is an important one.

Small sorts should be accomplished entirely in memory whereas large sorts may have to use temporary disk

space to complete the sort. As you may likely deduce, a sort that occurs in memory is faster than a sort that must use

disk. Therefore, when the optimizer estimates the effect of sorting data, it has to consider how big the sort is to adjust

how to accomplish getting the query result in the most efficient way. In general, consider sorts as a fairly expensive

overhead to your query processing time, particularly if the size of your result set is large.

The INSERT Statement

The INSERT statement is used to add rows to a table, partition, or view. Rows can be inserted in either a single-table

or multitable method. A single-table insert inserts values into one row of one table either by specifying the values

explicitly or by retrieving the values using a subquery. The multitable insert inserts rows into one or more tables and

computes the row values it inserts by retrieving the values using a subquery.

Single-Table Inserts

The first example in Listing 1-16 illustrates a single-table insert using the VALUES clause. Each column value is entered

explicitly. The column list is optional if you include values for each column defined in the table. However, if you only

want to provide values for a subset of the columns, you must specify the column names in the column list. A good

practice is to include the column list regardless of whether you specify values for all the columns. Doing so acts to

self-document the statement and also helps reduce possible errors that might occur in the future should someone add

a new column to the table.

Listing 1-16. Single-Table Insert

SQL> insert into hr.jobs (job_id, job_title, min_salary, max_salary)

2 values ('IT_PM', 'Project Manager', 5000, 11000) ;

1 row created.

SQL> insert into scott.bonus (ename, job, sal)

2 select ename, job, sal * .10

3 from scott.emp;

14 rows created.

The second example in Listing 1-16 illustrates an insert using a subquery, which is a very flexible option for

inserting rows. The subquery can be written to return one or more rows. Each row returned is used to supply column

values for the new rows to be inserted. The subquery can be as simple or as complex as needed to satisfy your needs.

In this example, we use the subquery to compute a 10 percent bonus for each employee based on his or her current

salary. The bonus table actually has four columns, but we only populate three of them with this insert. The comm

column isn’t populated with a value from the subquery and we do not include it in the column list. Because we don’t

include this column, the value for it is null. Note that if the comm column had a NOT NULL constraint, you would get a

constraint error and the statement would fail.

剩余567页未读，继续阅读

mamba键盘侠

粉丝: 70
资源: 2

深入理解Oracle SQL：核心概念与实践

pro oracle sql 2

Pro Oracle SQL (2010)

Pro Oracle SQL 无水印pdf

oracle sql教程.pdf

oracle11.2.0.4.windows.zip

Oracle如何执行.sql文件

oracle sqlnet.ora限制访问IP地址

用java实现 String 转 oracle.sql.CLOB

最新资源