jump to navigation

Clustering Factor: A Consideration in Concatenated Index Leading Column Decision (Sweet Thing) February 15, 2008

Posted by Richard Foote in Clustering Factor, Concatenated Indexes, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning.
trackback

Short but sweet today.

I last discussed how high cardinality columns shouldn’t necessarily be in the leading column of a concatenated index as  they don’t provide the performance benefit as sometimes claimed.

If all things are equal and the columns in the concatenated index are all likely to be referenced, a simple consideration that is often forgotten when deciding which column to have as the leading index column is the Clustering Factor of the corresponding columns.

As previously discussed, the Clustering Factor  determines how well aligned or ordered the index entries are in relation to the rows in the parent table. So if the rows are ordered within the table on a particular column or columns (such as a sequential ID column, a monotonically increasing date or time-stamp, etc), then an index on these columns is likely to have a very good Clustering Factor. Consequently less IOs will be required to retrieve all the required rows via the index as all the required rows will be housed in relatively few, well clustered data blocks.

It therefore makes sense to at least consider the Clustering Factor of the various columns in a concatenated index. Why ? Because if the leading column has a very good Clustering Factor, the concatenated index by definition must also have a very good Clustering Factor as all indexes are primarily ordered based on the leading indexed column. A concatenated index with a good Clustering Factor is going to be more efficient in retrieving rows from the table and more importantly, will be considered more desirably by the CBO when costing access path options.

Of course, the opposite is also true. By having a leading column with a poor Clustering Factor will mean the concatenated index will have a poor Clustering Factor, making it less efficient and less likely to be considered by the CBO.

As such, the Clustering Factor of each corresponding column in a concatenated index is at least worthy of some consideration when making the decision on how best to order the indexed columns.

This demo on Index Column Order and Clustering Factor  shows how the order of columns in a concatenated index has a big impact on the Clustering Factor of the resultant index.

UPDATE: However as Tom Kyte has stated in the comments, in virtually all cases, the Clustering Factor is not really a factor (yes, pun fully intended) as subsequently columns are generally going to impact the CF anyways or the selectivity of the index is such that the improved CF is not relevant anyways.

More relevant considerations regarding the ordering of columns in an index coming I promise :)

About these ads

Comments»

1. Houri Mohamed - February 15, 2008

Richard

Thanks for the valuable information you are providing in this blog. I don’t let one day spend without reading your blog.

I implemented the demo in my development environment (8.1.7) and have got the following results (using user_tables)

INDEX_NAME BLOCKS NUM_ROWS CF
———————– ———- ——- CF_TEST_GOOD_I 7 242 7

INDEX_NAME BLOCKS NUM_ROWS CF
———————– ———- ——- CF_TEST_GOOD_I 7 242

What does it mean when we have a Null Clustering Factor ?

PS : tabname=>’CF_TEST?, ====> tabname=>’CF_TEST’,

Kind regards

2. Thomas Kyte - February 16, 2008

Richard,

You wrote:

It therefore makes sense to at least consider the Clustering Factor of the various columns in a concatenated index. Why ? Because if the leading column has a very good Clustering Factor, the concatenated index by definition must also have a very good Clustering Factor as all indexes are primarily ordered based on the leading indexed column.

But that wouldn’t be so “in general”. If the first column is “well sorted” (that would mean C1,ROWID is well sorted in the index with respect to the table), that does not imply that C1,C2,ROWID will be well sorted – C2 is sorted within C1 and would mess up the clustering factor just as easily as if C2 were first.

Consider: C1 = constant, C2 = randomly arriving data. You cannot get “better” then constant for clustering factors – so:

ops$tkyte%ORA10GR2> create table t
2 as
3 select ‘N’ n, dbms_random.random id, a.*
4 from all_objects a
5 /
Table created.

ops$tkyte%ORA10GR2> create index n_idx on t(n);
ops$tkyte%ORA10GR2> create index id_idx on t(id);
ops$tkyte%ORA10GR2> create index n_id_idx on t(n,id);
ops$tkyte%ORA10GR2> create index id_n_idx on t(id,n);
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,’T’,estimate_percent=>100 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select i.index_name, i.clustering_factor, t.blocks, t.num_rows
2 from user_indexes i, user_tables t
3 where t.table_name = ‘T’
4 and i.table_name = ‘T’
5 /

INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
—————————— —————– ———- ———-
N_IDX 749 771 49704
ID_IDX 49630 771 49704
N_ID_IDX 49630 771 49704
ID_N_IDX 49630 771 49704

anytime ID is in the index, it drives the clustering factor way up – regardless of its position. Even if you make N non-constant, just clustered, eg:

ops$tkyte%ORA10GR2> create table t
2 as
3 select chr(ascii(‘A’)+trunc(rownum/2000)) n, dbms_random.random id, a.*
4 from all_objects a
5 /

the results are virtually identical – index on N is clustered well, indexes including the ID (non-sorted in table) value are having a clustering factor near the number of rows in the table.

INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
—————————— —————– ———- ———-
N_IDX 751 771 49684
ID_IDX 49636 771 49684
N_ID_IDX 48085 771 49684
ID_N_IDX 49636 771 49684

was the outcome in that case – having N first was *marginally* better than having ID first, but not really worth the thought.

It would only be true if the leading column where nearly unique (as they were in your test case):

ops$tkyte%ORA10GR2> create table t
2 as
3 select rownum n, dbms_random.random id, a.*
4 from all_objects a
5 /

then we would observe the indexes with N first being “better” clustering factor wise:

ops$tkyte%ORA10GR2> select i.index_name, i.clustering_factor, t.blocks, t.num_rows
2 from user_indexes i, user_tables t
3 where t.table_name = ‘T’
4 and i.table_name = ‘T’
5 /

INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
—————————— —————– ———- ———-
N_IDX 768 790 49689
ID_IDX 49620 790 49689
N_ID_IDX 768 790 49689
ID_N_IDX 49620 790 49689

but only because N is unique or almost unique (and hence the ability of ID to scramble the data is reduced or removed)

but – if N were nearly unique, then it really wouldn’t matter again what the order of the columns were.

Reasoning: the clustering factor is used by the optimizer to tell it when to STOP using the index for large range scans.

If the leading edge column of the index were nearly unique, you would not be doing large range scans on it in general, your query:

where id = ? and n = ?

would always be selective, retrieve few rows, and the clustering factor would be not so relevant.

only if you did something like:

where id = ? and n > ?

might that be the case, but I would argue I’d want the ID column on the leading edge (find that ID=? value, then range on N>?) rather then find all of the N’s >? and pick off the ID=? values.

3. Richard Foote - February 17, 2008

Hi Houri

I always wondered who the other person was that visits the Blog regularly ;)

If the CF is not populated, it usually means the index hasn’t had statistics collected on it. Note my specifc demo was run on 10g and as such Oracle automatically collects the stats on any new or rebuilt index. However in 8i, this would need to be performed “manually”.

Make sure you collect stats after you create the indexes.

4. Richard Foote - February 17, 2008

Hi Tom

Wooohooo, Tom Kyte has visited my blog. Sorry, big fan :)

I do wish however you would put some detail in your comments ;)

Seriously, thank you for your comments, they make perfect sense. You’re right my example isn’t particularly general and that if the leading column wasn’t so selectively, subsequent columns are likely to screw the CF.

Thanks again for your comments.

5. Thomas Kyte - February 17, 2008

Richard,

not only “visit” but actively read – this series on indexes is really good stuff, excellent. Really been enjoying it.

6. Richard Foote - February 17, 2008

Thanks Tom, glad you’ve been enjoying it, much appreciated.

7. Hemant K Chitale - February 20, 2008

Actually, I do have a case where CF in a Concatenated Index
did make a difference. The leading column was a Date column
and rows were being inserted into the table by Date.
So, this index actually helped reduce my physical read calls
(huge table, it can’t be cached) because the rows were co-located).
See http://hemantoracledba.blogspot.com/2008/01/impact-of-clustering-factor.html

8. Thomas Kyte - February 20, 2008

@Hermant

well, that would be the case where the leading edge of the index was nearly unique – and as pointed out – that would lead to a good clustering factor as a result but….

so what?

The clustering factor is something that will lead the optimizer to choose an index or not based on how many table blocks it believes it will access.

Your blog entry gives no details. But I can say this with some confidence – when you wrote:

“The difference lay in the Clustering Factor.”

you jumped to the WRONG conclusion. The clustering factor determines whether an index will be used. The clustering factor has no influence over the runtime performance of a query (other than it will help the optimizer decide to use it or not).

Going by your description, it sounds more likely that by reordering the columns in your concatenated index – you had to inspect less rows, that is all.

eg;

ops$tkyte%ORA10GR2> create table t
2 as
3 select rownum c1, dbms_random.value( 1, 30 ) c2, all_objects.*
4 from all_objects
5 /

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, ‘T’ );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create index t_c1_c2_idx on t(c1,c2);

Index created.

ops$tkyte%ORA10GR2> create index t_c2_c1_idx on t(c2,c1);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2 from user_indexes i, user_tables t
3 where t.table_name = ‘T’
4 and i.table_name = ‘T’
5 /

TABLE_NA INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
——– ———– ———- ———- —————–
T T_C1_C2_IDX 895 49807 873
T T_C2_C1_IDX 895 49807 49752

ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2> set autotrace traceonly statistics
ops$tkyte%ORA10GR2> select /*+ index( t t_c1_c2_idx ) */ * from t where c1 > 42 and c2 > 42;

no rows selected

Statistics
———————————————————-
0 recursive calls
0 db block gets
264 consistent gets
0 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

ops$tkyte%ORA10GR2> select /*+ index( t t_c2_c1_idx ) */ * from t where c1 > 42 and c2 > 42;

no rows selected

Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

ops$tkyte%ORA10GR2> set autotrace off

Now, the index with the poor clustering factor blew the doors off the one with the really good one.

but only because the index on c2,c1 was a better index given the nature of the data queried.

Now, you do not share the queries, nor the tables – so all we can do is guess that “because of your predicates, the index traversal was much more efficient – inspected FEWER index entries”

I found the blog entry you pointed to pretty hard to follow. It actually seems to me that you “fixed” this by using NO INDEX (which makes sense, you were doing a huge range scan)

9. Hemant K Chitale - February 21, 2008

I think that I will have to rewrite that posting to make it clearer.
Since the SQL statement and table and column names are from a
real case, I tried to mask the details. However, l’ll try to explain here :

0. Yes it is a range scan.

1. There are two indexes on the table. Both are multicolumn indexes. “index_a” is the bad index which the optimizer was choosing to use initially.

2. “index_a” resulted in this RowSourceOperation entry :
STAT #1 id=16 cnt=3354870 pid=15 pos=1 obj=14815979 op=’INDEX RANGE SCAN index_a ‘ and this for the Fetch :
FETCH #1:c=95480000,e=2366647483,p=691119,cr=1353212,cu=0,mis=0,r=1,dep=0,og=4

Thus, Oracle using this index in the Range Scan was doing 1.35million consistent gets.

3. I needed to disable usage of this index. I knew that there was a better index — index_b. I had 4 choices :
a) Drop index_a
b) Apply a function to the predicate
c) Manipulate column and index statistics
d) Put an explicit NO_HINT index
Actually, I could have tried the 5th option
e) Put an explicit HINT (table_1 index_b).
But I didn’t do that. I just wanted to see what alternate execution plan (not just index_b but the whole plan, nested loops whatever) Oracle chose withouth index_a.

4. The NO_INDEX Hint therefore, wasn’t really intended as the solution. My blog posting doesn’t assert it that way. I just said that I used a NO_INDEX Hint — the reason only was I wanted to disable usage of that index.

5. Oracle then switched to index_b. This resulted in :
STAT #1 id=17 cnt=3314718 pid=16 pos=1 obj=14815976 op=’INDEX RANGE SCAN index_b ‘
and
FETCH #1:c=10320000,e=257057504,p=78328,cr=159300,cu=0,mis=0,r=1,dep=0,og=4

6. The question, then, was :
“In the first SQL RowSourceOperations there 3.354million rowids fetched from index_a and the total count of Buffer Gets for the whole query was 1.353million.
In the second SQL’s RowSourceOperations there 3.315million rowids fetched from index_b and yet the total count of Buffer Gets for the whole query was only 159,300 !
Why the difference ?”

7. The assertion “The difference lay in the Clustering Factor” was actually an answer to that exact question. I didn’t assert outright that the changed execution plan or better performance was a result of the Clustering Factor.

8. These were the statistics :
INDEX_NAME LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
—————————— ———– ———————–
AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR DISTINCT_KEYS
———————– —————– ————-
index_b 463706 1 13 3490380 261796
index_a 456250 1 4 21444164 4845082

9. What I tried to put forward was that the better Clustering Factor with index_b was that although Oracle did read just as many rowids in the index range scan, the number of block gets for the *table* was much lower — the total consistent gets from 1.35million to only 159,300 !

10. Obviously, the number of rows is still similar. The number of *blocks* is much lower. That is my assertion about the Clustering Factor.

11. index_b’s leading column is a date column and rows are inserted into the table by date — ascending order. Much better clustering of rows into the same block and contiguous blocks when I fetch them ordered by the date.

12. So, the Clustering Factor didn’t result in inspection of fewer index entries. It did result in a significant reduction in consistent gets. Unfortunately, the row source operation statistics don’t really show how many index blocks and how many table blocks were read but I’m pretty sure that, because it was a Range Scan, the reduction was in table blocks.

10. Thomas Kyte - February 21, 2008

@Hemant

you will want to put up supporting information regarding point #9 – A before and after tkprof of both queries would be infinitely better than a one or two like grep from a trace file, it would really show something.

#10 – that was the result of using a different index, not the result of an index having a better clustering factor. I demonstrated that an index with a horrific clustering fact can do the work in 2 IO’s what took the other 246 IO’s. It wasn’t the clustering factor – it was an index that was more efficient.

#12 – show us the tkprof and row source operations, that’ll go a long way here.

11. Thomas Kyte - February 21, 2008

one more thing..

Yes, if you have indexes:

t(a,b)
t(b,a)

and the table is clustered by A, not by B – then it would be true that a LARGE index range scan that used t(a,b) would be more efficient than a range scan of equal proportion that used t(b,a) [in general, i'll caveat that in a second].

But my point above was

a) putting the most clustered element first will not normally reduce the clustering factor unless that first element is almost unique (as the above example was)

b) and then it is usually a a big ho-hum that it does – because you would presumably be using it in a highly selective fashion.

It seems to me that NO INDEX for you might have been even better than an index. It would have to be a table of huge size for me to want to retrieve more than a million rows via the index (unless I could use the index INSTEAD of the table of course)

And for the caveat, there are opportunities to get plans that look like:

table T access by index rowid
nested loops
index range scan index_on_t2
index range scan index_on_t

for a query like:

select t1…. t2…..
from t1, t2
where t1.columns = something
and t1.key = t2.key;

the rows are gotten from the table AFTER the nested loop operation and internally we sort them to make the access more efficient – there it would not matter so much what the clustering factor was – we fix it by sorting the rowids after the fact.

I’d still be interested in full up tkprofs that show what was happening – the bits of trace you have do not tell us much.

12. Richard Foote - February 22, 2008

Hi Guys

I guess that’s the point. The (a, b) would have a better CF, but only if the “a” column was extremely selective. And if it’s very selective and used mainly in a highly selective manner, then the better CF is somewhat pointless.

But if it’s used in a large range scan, then you would want the selectivity on the “b” column to be likewise very unselective (if that’s the right word ?), else you would be better off with column “a” as the leading column anyways.

So the scenario where the CF would actually be better is somewhat small and the scenario when it would be useful would generally be likewise small as well.

Still, it’s all good points of discussion as I sit here with a cold beer on a balmy Canberra evening !!

13. Hemant K Chitale - February 22, 2008

Tom,
1. You ask about more supporting information item 9. The facts already presented twice are :
a. Index index_a : Clustering Factor 21.441million, Index index_b : Clustering Factor 3.490million.
I guess you don’t dispute that index_b has a better clustering factor.
b. The first SQL execution with index_a did 1.3million consistent gets. The second SQL execution with index_b did 159,300 consistent gets. (The STAT entry value for “cr” is consistent read which I am merely repositioning as consistent gets).
I guess you don’t disupted that 159,300 blocks is less than 1.3million blocks.
I, personally, think that going down from 1.3million blocks to 159,300 blocks is a result of a better clustering factor.

2. This is 9i. tkprof doesn’t give me more information then the raw trace file STAT lines. 9i tkprof doesn’t provide individual times (but the raw trace file does)
However, since you need to see the tkprofs, these are they :
For the first SQL execution : where you see table_1 as alias “b” and index_a is obvious
SELECT /* First SQL Execution */
b.col_1, b.col_2, b.col_3, a.col_1,
TO_CHAR (b.col_x, ‘YYYY-MM-DD’), b.col_4, c.col_1,
b.col_5, b.col_6, b.col_7, b.col_8,
b.col_9, b.col_10, b.col_11,
b.col_11, b.col_12, c.col_2, c.col_3
FROM first_table a,
table_1 b,
a_view b1,
another_view c
WHERE b.col_1 = b1.col_1
AND b.col_2 = b1.col_2
AND b.col_x = b1.col_3
AND b.col_12 = b1.col_4
AND b.col_3 = b1.col_5
AND b.col_13 = b1.col_6
AND b1.col_7 = ‘ACONSTANT’
AND ( a.col_2 = b.col_1
AND a.col_3 = b.col_2
AND a.col_4 = b.col_x
AND a.col_5 = b.col_12
AND c.col_3 = b.col_4
AND a.col_6 IN (‘A’, ‘G’)
AND ‘THIS’ = c.col_4
AND b.col_13 = ‘BCONSTANT’
AND b.col_1 = ’12345′
AND b.col_4 BETWEEN ’100001′ AND ’900009′
AND b.col_14 BETWEEN ’1001′ AND ’9009′
AND b.col_15 BETWEEN ’100′ AND ’909′
AND a.col_6 BETWEEN TO_DATE (’2007-01-01′, ‘YYYY-MM-DD’)
AND TO_DATE (’2007-01-31′, ‘YYYY-MM-DD’)
AND b.col_16 = ‘XYZ’
)
ORDER BY 6, 8, 9, 10, 11, 12, 5, 2, 3

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.60 0.61 4 7 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 3 95.48 2366.64 691119 1353212 0 16
——- —— ——– ———- ———- ———- ———- ———-
total 6 96.08 2367.25 691123 1353219 0 16

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows Row Source Operation
——- —————————————————
16 SORT ORDER BY
16 FILTER
16 NESTED LOOPS
16 NESTED LOOPS
22 NESTED LOOPS
2710 MERGE JOIN CARTESIAN
1 MERGE JOIN CARTESIAN
1 NESTED LOOPS
1 INDEX UNIQUE SCAN index_abc (object id 12147556)
1 INDEX RANGE SCAN index_def (object id 12161927)
1 BUFFER SORT
1 INDEX RANGE SCAN index_def (object id 12161927)
2710 BUFFER SORT
2710 INDEX FAST FULL SCAN index_klm (object id 14815994)
22 TABLE ACCESS BY INDEX ROWID table_1
3354870 INDEX RANGE SCAN index_a (object id 14815979)
16 TABLE ACCESS BY INDEX ROWID a
22 INDEX UNIQUE SCAN index_taba_1 (object id 14815980)
16 TABLE ACCESS BY INDEX ROWID table_1
16 INDEX UNIQUE SCAN index_tab1_2 (object id 14815975)
4 SORT AGGREGATE
4 FIRST ROW
4 INDEX RANGE SCAN (MIN/MAX) index_klm (object id 14815991)
0 TABLE ACCESS BY INDEX ROWID index_nop
0 INDEX RANGE SCAN index_qrs (object id 12161760)
0 INDEX RANGE SCAN index_qrs (object id 12161760)
0 TABLE ACCESS BY INDEX ROWID table_tuv
0 INDEX RANGE SCAN index_tuv (object id 12161761)
0 INDEX RANGE SCAN index_tuv (object id 12161761)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.00 0.00
db file sequential read 691119 0.87 2293.16

For the second SQL execution, with the NO_INDEX Hint to disable index_a and we see index_b coming into play :

SELECT /*+ NO_INDEX ( b index_a ) */
.. the rest of the SQL statement being exactly the same

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.57 0.56 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 3 10.32 257.05 78328 159300 0 16
——- —— ——– ———- ———- ———- ———- ———-
total 6 10.89 257.61 78328 159300 0 16

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows Row Source Operation
——- —————————————————
16 SORT ORDER BY
16 FILTER
16 NESTED LOOPS
16 NESTED LOOPS
16 NESTED LOOPS
1914 MERGE JOIN CARTESIAN
1 MERGE JOIN CARTESIAN
1 NESTED LOOPS
1 INDEX UNIQUE SCAN index_abc (object id 12147556)
1 INDEX RANGE SCAN index_def (object id 12161927)
1 BUFFER SORT
1 INDEX RANGE SCAN index_def (object id 12161927)
1914 BUFFER SORT
1914 TABLE ACCESS BY INDEX ROWID a
1914 INDEX RANGE SCAN index_taba_2 (object id 12154504)
16 TABLE ACCESS BY INDEX ROWID table_1
3314718 INDEX RANGE SCAN index_b (object id 14815976)
16 TABLE ACCESS BY INDEX ROWID table_klm
16 INDEX RANGE SCAN index_klm (object id 14815991)
4 SORT AGGREGATE
4 FIRST ROW
4 INDEX RANGE SCAN (MIN/MAX) index_klm (object id 14815991)
16 TABLE ACCESS BY INDEX ROWID table_1
16 INDEX UNIQUE SCAN index_tab1_2(object id 14815975)
0 TABLE ACCESS BY INDEX ROWID index_nop
0 INDEX RANGE SCAN index_qrs (object id 12161760)
0 INDEX RANGE SCAN index_qrs (object id 12161760)
0 TABLE ACCESS BY INDEX ROWID table_tuv
0 INDEX RANGE SCAN index_tuv (object id 12161761)
0 INDEX RANGE SCAN index_tuv (object id 12161761)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.00 0.00
db file sequential read 78328 0.44 218.20
buffer busy waits 9715 0.40 30.53
latch free 26 0.02 0.41

Does that provide enough information ? tkprof providing more
information than the STAT lines ?

14. Hemant K Chitale - February 22, 2008

And why I did not want a Full Table Scan ?
The table has more than 24million rows and 2.4million blocks.
Extent sizes are 128KB so the largest multiblock reads that
I will get are not very large.
The index range scan, I find was satisfactory.
And why I did I not change the statement to change the execution
plan any further ?
Having brought a query down from 40minutes execution time
to 4minutes execution time, I decided not to indulge in
compulsive tuning disorder.
It was also easier in terms of change management to put a Hint in
then to have an SQL rewrite put in.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,913 other followers

%d bloggers like this: