crystal report barcode font free download IM PR OVING DA TA BAS E PERFORM ANC E: S QL QUE RY OPTIMIZA TION in Font

Make Code 39 in Font IM PR OVING DA TA BAS E PERFORM ANC E: S QL QUE RY OPTIMIZA TION

CHAPTER 19 IM PR OVING DA TA BAS E PERFORM ANC E: S QL QUE RY OPTIMIZA TION
Drawing Code39 In None
Using Barcode generation for Font Control to generate, create Code 39 Full ASCII image in Font applications.
www.OnBarcode.com
Print Barcode In None
Using Barcode creation for Font Control to generate, create Barcode 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 19-7. Listing 19-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 19-8, you run the test query before you create an index on the courses table. Listing 19-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
PDF 417 Maker In None
Using Barcode encoder for Font Control to generate, create PDF 417 image in Font applications.
www.OnBarcode.com
Drawing UPCA In None
Using Barcode generator for Font Control to generate, create GTIN - 12 image in Font applications.
www.OnBarcode.com
CHAPTER 19 IMPROV IN G DA TA BAS E PERFORMAN CE: SQL QUERY OPTIMIZA TION
Creating Data Matrix 2d Barcode In None
Using Barcode maker for Font Control to generate, create DataMatrix image in Font applications.
www.OnBarcode.com
Code 128C Creator In None
Using Barcode generation for Font Control to generate, create Code128 image in Font applications.
www.OnBarcode.com
338 0 381 499 2 0 0 1 SQL>
EAN13 Printer In None
Using Barcode printer for Font Control to generate, create EAN13 image in Font applications.
www.OnBarcode.com
Drawing ISBN - 10 In None
Using Barcode printer for Font Control to generate, create Bookland EAN image in Font applications.
www.OnBarcode.com
physical reads redo size 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
Code-39 Reader In VS .NET
Using Barcode reader for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Read Code 3/9 In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java 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 19-9 tells the story. Listing 19-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
QR Code JIS X 0510 Generator In Objective-C
Using Barcode generator for iPad Control to generate, create QR-Code image in iPad applications.
www.OnBarcode.com
Recognizing Denso QR Bar Code In Visual Studio .NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in Visual Studio .NET applications.
www.OnBarcode.com
Decode Code 128B In VS .NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Paint EAN128 In None
Using Barcode generation for Office Word Control to generate, create GTIN - 128 image in Word applications.
www.OnBarcode.com
Encode Barcode In Java
Using Barcode generator for Android Control to generate, create Barcode image in Android applications.
www.OnBarcode.com
Drawing EAN13 In None
Using Barcode encoder for Excel Control to generate, create EAN / UCC - 13 image in Microsoft Excel applications.
www.OnBarcode.com
QR Code 2d Barcode Scanner In VB.NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Data Matrix 2d Barcode Generator In Visual Studio .NET
Using Barcode generation for Reporting Service Control to generate, create Data Matrix image in Reporting Service applications.
www.OnBarcode.com
Encode QR Code ISO/IEC18004 In Java
Using Barcode maker for Java Control to generate, create QR Code image in Java applications.
www.OnBarcode.com
Drawing Barcode In Objective-C
Using Barcode drawer for iPad Control to generate, create Barcode image in iPad applications.
www.OnBarcode.com
Copyright © OnBarcode.com . All rights reserved.