barcode font for crystal report free download IMPROVING DATABASE PERFORMANCE: SQL QUERY OPTIMIZATION in Font

Printer QR Code ISO/IEC18004 in Font IMPROVING DATABASE PERFORMANCE: SQL QUERY OPTIMIZATION

CHAPTER 21 IMPROVING DATABASE PERFORMANCE: SQL QUERY OPTIMIZATION
QR-Code Generator In None
Using Barcode printer for Font Control to generate, create QR image in Font applications.
www.OnBarcode.com
Creating PDF-417 2d Barcode In None
Using Barcode generator for Font Control to generate, create PDF417 image in Font applications.
www.OnBarcode.com
All SQL statements issued after the Autotrace feature is turned on will generate the execution plans (until you turn off the Autotrace facility with the command SET AUTOTRACE OFF), as shown in Listing 21-7. Listing 21-7. Using the Autotrace Utility SQL> SET AUTOTRACE ON; SQL> SELECT * FROM EMP; no rows selected Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=74) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=74) Statistics 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 511 bytes sent via SQL*Net to client 368 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> After showing the execution plan for the SQL statement, the Autotrace feature shows the details about the number of SQL recursive calls incurred in executing the original statement; the number of physical and logical reads, in memory and on disk sorts; and the number of rows processed. I provide a few simple examples to show how Autotrace helps you optimize SQL queries. In the following examples, the same query is used twice in the courses table, once without an index and once with an index. After the table is indexed, you run the query before you analyze the table. The results are instructive. In the first example, whose output is shown in Listing 21-8, you run the test query before you create an index on the courses table. Listing 21-8. The Execution Plan for a Query Without an Index SQL> SET AUTOTRACE ON SQL> SELECT COUNT(*) FROM courses 2 WHERE course_subject='medicine' 3* AND course_title = 'fundamentals of human anatomy'; COUNT(*) 98304 Execution Plan ----------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'COURSES' Statistics ----------------------------------------------------0 recursive calls 0 db block gets 753 consistent gets 338 physical reads 0 redo size
Code-128 Drawer In None
Using Barcode creator for Font Control to generate, create Code 128 Code Set B image in Font applications.
www.OnBarcode.com
DataMatrix Printer In None
Using Barcode maker for Font Control to generate, create Data Matrix 2d barcode image in Font applications.
www.OnBarcode.com
CHAPTER 21 IMPROVING DATABASE PERFORMANCE: SQL QUERY OPTIMIZATION
UPC - 13 Drawer In None
Using Barcode encoder for Font Control to generate, create EAN13 image in Font applications.
www.OnBarcode.com
Encode Code 39 In None
Using Barcode creation for Font Control to generate, create Code-39 image in Font applications.
www.OnBarcode.com
381 499 2 0 0 1 SQL>
UPC-A Supplement 5 Encoder In None
Using Barcode drawer for Font Control to generate, create Universal Product Code version A image in Font applications.
www.OnBarcode.com
Universal Product Code Version E Creator In None
Using Barcode creator for Font Control to generate, create Universal Product Code version E image in Font applications.
www.OnBarcode.com
bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
Create QR Code In None
Using Barcode creation for Font Control to generate, create QR Code image in Font applications.
www.OnBarcode.com
Generating Quick Response Code In Objective-C
Using Barcode printer for iPhone Control to generate, create QR Code image in iPhone applications.
www.OnBarcode.com
As you can see, the query used a full table scan because there are no indexes. There were a total of 338 physical reads. Note that the total number of rows in the courses table is 98,384. Out of this total, the courses with medicine as the course subject were 98,304. That is, the table values aren t distributed evenly among the courses at all. Now let s see what happens when you use an index. The following example uses a query with an index. However, no statistics are collected for either the table or the index. When you create an index on the courses table and run the same query, you ll see some interesting results. Listing 21-9 tells the story. Listing 21-9. The Execution Plan for a Query with an Index SQL> CREATE INDEX title_idx ON courses (course_title); Index created. SQL> SELECT count(*) FROM courses 2 WHERE course_subject='medicine' 3 AND course_title = 'fundamentals of human anatomy'; COUNT(*) 98304 Execution Plan -------------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'COURSES' 3 2 INDEX (RANGE SCAN) OF 'TITLE_IDX' (NON-UNIQUE) Statistics -------------------------------------------------------------0 recursive calls 0 db block gets 1273 consistent gets 1249 physical reads 0 redo size 381 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> After you created the index, the physical reads went from 338 to 1,249! The EXPLAIN PLAN shows that Oracle is indeed using the index, so you would expect the physical reads to be lower when compared to the no-index case. What happened here is that even if a table has an index, this doesn t mean that it s always good to use it under all circumstances. The CBO always figures the best way to get a query s results, with or without using the index. In this case, the query has to look at almost all the rows of the table, so using an index isn t the best way to go. However, you haven t collected statistics for the table and the index, so Oracle has no way of knowing the distribution of the actual data in the courses table. Lacking any statistics, it falls back to a rule-based approach. Under a rule-based optimization, using an index occupies a lower rank and therefore indicates that this is the optimal approach here. Let s see the results after analyzing the table.
Data Matrix 2d Barcode Printer In None
Using Barcode maker for Word Control to generate, create ECC200 image in Word applications.
www.OnBarcode.com
Data Matrix ECC200 Recognizer In VB.NET
Using Barcode reader for .NET framework Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Encoding Quick Response Code In Visual C#
Using Barcode maker for Visual Studio .NET Control to generate, create QR Code JIS X 0510 image in VS .NET applications.
www.OnBarcode.com
Scan GS1 128 In Visual C#
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
GS1 - 12 Drawer In Objective-C
Using Barcode printer for iPhone Control to generate, create UCC - 12 image in iPhone applications.
www.OnBarcode.com
Data Matrix Drawer In None
Using Barcode generator for Online Control to generate, create Data Matrix image in Online applications.
www.OnBarcode.com
Barcode Generation In VS .NET
Using Barcode generator for ASP.NET Control to generate, create Barcode image in ASP.NET applications.
www.OnBarcode.com
Code 128A Creation In Objective-C
Using Barcode encoder for iPhone Control to generate, create Code 128B image in iPhone applications.
www.OnBarcode.com
Generate UPC Code In Java
Using Barcode encoder for Android Control to generate, create UCC - 12 image in Android applications.
www.OnBarcode.com
Printing QR Code 2d Barcode In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create QR-Code image in ASP.NET applications.
www.OnBarcode.com
Copyright © OnBarcode.com . All rights reserved.