Database Reference
In-Depth Information
Let us also assume there are 50 distinct LNAME values in that slice. The
tuned program using CURSOR62 would read these 50 slices (with an average of
20 rows in each); each of the 1000 rows will be for the required CITY.
According to the QUBE (ignoring the FETCHes as we are simply making a
comparison), the elapsed times for the two cursors would be:
×
+
,
×
.
=
.
CURSOR 61
1
10 ms
10
000
0
01 ms
0
1s
×
(
×
+
×
.
)
=
.
5s
This is somewhat surprising but logical; sequential processing is sometimes
more efficient than skip-sequential processing because each skip may imply a
random touch. With different numbers, the tuned program with CURSOR62 could
be
much
faster than the simple program with CURSOR61.
To prove this last point, let's consider a case with a
bigger table
(100,000,000
rows) and a
higher filter
factor for the first index column as shown in SQL 6.3.
CURSOR 62
50
1
10 ms
20
0
01 ms
0
SQL 6.3
SELECT
CNO, FNAME
FROM
CUST
WHERE
LNAME BETWEEN :LNAME1 AND :LNAME2
FF max 10%
AND
CITY BETWEEN :CITY1 AND :CITY2
FF max 0.01%
ORDER BY
FNAME
The size of the result table will be 0
.
01%
×
10%
×
100
,
000
,
000
=
1000
rows. Let us assume an index (LNAME, CITY, FNAME, CNO) and 50 distinct
values of LNAME in the 1000 row result table. Now, the simple program (two
BETWEENs) elapsed time estimate (one thick slice) is
1
×
10 ms
+
10
,
000
,
000
×
0
.
01 ms
=
100 s
while the estimate for the tricky program (50 thin slices) is
still
50
5s
Oracle 9i is able to generate a skip-sequential access path instead of a full
index scan—
index skip scan
. In our example, this would mean scanning one
slice for each LNAME value. These slices have two matching columns, LNAME
and CITY.
×
(
1
×
10 ms
+
20
×
0
.
01 ms
)
=
0
.
Simple Is Beautiful (and Safe)
The solution using the auxiliary table for distinct LNAME values is a typical
example of a compromise between data integrity and performance. In theory,
there is no integrity risk if the auxiliary table is maintained with a trigger.
However, triggers are application programs and programs are not perfect. Is
Search WWH ::
Custom Search