However, DBAs should be involved in reviewing the SQL that is written as part of the application.
A well-designed application may still experience performance problems if the SQL it uses is poorly
tuned. Application design and SQL problems cause most of the performance problems in properly
designed databases.
The key to tuning SQL is to minimize the search path that the database uses to find the data.
In most Oracle tables, each row has a RowID associated with it. The RowID contains information
about the physical location of the row??”its file, the block within that file, and the row within the
database block.
When a query with no where clause is executed, the database will usually perform a full table
scan, reading every block from the table. During a full table scan, the database locates the first
block of the table and then reads sequentially through all the other blocks in the table. For large
tables, full table scans can be very time-consuming.
When specific rows are queried, the database may use an index to help speed the retrieval
of the desired rows. An index maps logical values in a table to their RowIDs??”which in turn map
them to specific physical locations. Indexes may either be unique??”in which case there is no
more than one occurrence for each value??”or nonunique. Indexes only store RowIDs for NOT
NULL values in the indexed columns.
You may index several columns together. This is called a concatenated or composite index,
and it will be used if its leading column is used in the query??™s where clause.
Pages:
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415