jump to navigation

Big Tables, Sorts and Indexes Solution (Right On Mother) September 19, 2011

Posted by Richard Foote in CBO, Oracle Indexes, Quiz.
trackback

My, what a clever lot we have reading this blog :)

Indeed, most of the work has already been done for me as we already have a couple of excellent demos in the list of comments.

The answer is Yes, the CBO will consider using the index and Yes, there may be a number of scenarios when the index will actually be selected by the CBO as it’s the genuinely cheaper option.

At the end of the day, it comes down to whether performing a Full Index Scan, one little block at a time but with the data coming out pre-sorted is cheaper than performing a multiblock Full Table Scan AND a subsequent sort of the data. It of course depends on various factors such as:

  • The most crucial of all, the Clustering Factor of the index. If the data is very well clustered according to the index, then the cost of reading the entire table via the index can be vastly reduced and the index access path becomes viable. A poor (or average) CF, and using the index is just too expensive. Radoslav Golian has an excellent example in the comments on when an index with an excellent CF is chosen by the CBO.
  • The cost of the sort. Sorts are really expensive, especially if Oracle is forced to go to disk and even more so if it has to perform a multi pass sort, so the more costly the sort, the more likely the index is the cheaper option.

An important point for the index to be considered is that it must have a NOT NULL constraint on the column(s), else the index is ignored as the CBO can’t guarantee all rows can referenced within the index.

The moral of this story is this. There is no selectivity by which an index is not considered by the CBO. An index can potentially select 100% of all rows, if doing so is the cheapest option available to the CBO.

I’ve discussed using an index to select 100% of all data before if anyone is interested.

New question coming soon !!

About these ads

Comments»

1. albertofrosi - September 20, 2011

Hi Richard,
Good post, i’m totally agree with you and with Radoslav Golian example.
But in real cases is it really so?
Sometimes i’ve table for example with 714183 blocks and all related indexes (in this case 20) have a CF about 8077080 or 7716011 or 7380963 or 7369745.
It’s very poor CF.
How do I restore Clustering Factor of the indexes in this case?
Ciao

Alberto

Radoslav Golian - September 20, 2011

albertofrosi,
if you want to change the CF you have to reorganize the table (sort it according the index for which you want improve the CF). But if you have more indexes on that table, then improving CF for one index will probably worse CF for another index (If there is no correlation between the ordered sets of indexed columns)..

My example could be a real one, e.g: if a table has an indexed column populated by a monotonic function (sequence, sysdate – almost every table has such column), we are ordering the table using that column and rows are not deleted from that table

Richard Foote - September 20, 2011

Hi Alberto

I believe Radoslav has answered your question. A really good CF is possible with monotonically increasing values, depending on how concurrency is handled.

A common issue is one in which you have well clustered data but a terrible CF. This might be best addressed by setting the CF statistic to a more realistic value (something I’ll touch on in coming posts).

Richard Foote - September 20, 2011

Thanks Radoslav.

I might get you to handle the “night shift” for me :)

2. albertofrosi - September 20, 2011

Hi Radoslav,
thanks a lot for your explanation but having a big table with 20 index (distributed in more than 100 columns) , i can improve 1 index but the others 19 continue to have a bad CF.
is it only a no correlation between the ordered sets of indexed columns or there may be other?
In ERP system this situations are very common and very complexes too.
Ciao

Alberto

Richard Foote - September 20, 2011

Hi Alberto

The CF is based entirely on how well ordered the rows in the table are in relation to the index, which is always ordered based on the indexed column values. Fix the order for one index column, you’ll impact the order for other indexes.

3. Donatello Settembrino - September 20, 2011

Hi,
on the CF nothing to add, I also discussed this on other occasions:

http://hoopercharles.wordpress.com/2011/03/21/nested-loops-join-the-smaller-table-is-the-driving-table-the-larger-table-is-the-driving-table/

http://jonathanlewis.wordpress.com/2011/06/10/quiz-night-13/#comment-40770

http://jonathanlewis.wordpress.com/2010/05/18/double-trouble/#comment-36291

and I think that the opinions are similar ….

Ciao Alberto, in your case with the presence of such a large number of indexes, I think it is very difficult to do so to optimize the CF for all indexes.
You can “play” with the ordering of the data being loaded in the table, but it certainly can not do that for all indexes, maybe you can do , only for some of them.

But the thing that you could do is to “encourage”, sort the data in the table for the columns of the indexes that are frequently used (excluding any bitmap index from the list) looking for example in recent times which were the indexes the most widely used.

IMHO, I don’t see many other possibilities

HTH

Regards

Donatello Settembrino

4. albertofrosi - September 20, 2011

Hi Richard and Radoslav,
thanks a lot for your good explanations, i’ll wait Richard’s post to set CF statistic to a realistic value ;-)..

Radoslav would not be bad for the “night shift” …

“Go Azzurri” in the World Rugby Cup….

Ciao

Alberto

5. albertofrosi - September 21, 2011

Ciao Donatello,
good advice…
I was thinking about this, it’s a very hard work… ;-)
Ciao

Alberto

6. Radoslav Golian - September 21, 2011

I will just add an algorithm for CF computation:

SQL> begin dbms_stats.gather_index_stats(user, 'PK_T_CONTRACT'); end;
  2  /
 
PL/SQL procedure successfully completed

SQL> SELECT SUM(block_change)
  2    FROM (SELECT CASE lag(dbms_rowid.rowid_block_number(ROWID)) over(ORDER BY contract_id) -- ordered by indexed column
  3                   WHEN dbms_rowid.rowid_block_number(ROWID) THEN
  4                    0
  5                   ELSE
  6                    1
  7                 END block_change
  8            FROM t_contract);
 
SUM(BLOCK_CHANGE)
-----------------
                9
SQL> select clustering_factor from user_indexes where index_name = 'PK_T_CONTRACT';
 
CLUSTERING_FACTOR
-----------------
                9
 

Basically CF is a number of block changes during reading all rows from the table using the index..

The more block changes, the more work from oracle.. When block change occurs oracle has to read and to pin a new block..

albertofrosi - September 21, 2011

Hi Radoslav,
good stuff, I tryed your algorithm for a big table but in my case CF it’s always greater.

begin
dbms_stats.gather_index_stats(user, ‘FABC_1′);
end;

SELECT SUM(block_change)
FROM (SELECT CASE lag(dbms_rowid.rowid_block_number(ROWID)) over(ORDER BY 1, 2, 3, 4) — ordered by indexed column
WHEN dbms_rowid.rowid_block_number(ROWID) THEN
0
ELSE
1
END block_change
FROM FABC);

SUM(BLOCK_CHANGE)
———————-
4277066

select clustering_factor from user_indexes where index_name = ‘FABC_1′;

CLUSTERING_FACTOR
———————-
7638688

Thanks a lot
Ciao
Alberto

Radoslav Golian - September 22, 2011

Alberto, try estimate_percent=>NULL (or 100)
when no value is supplied AUTO_SAMPLE_SIZE is used for ESTIMATE_PERCENT..

The part of the table that was sampled has probably worse clustering factor than the whole table.

and maybe rowid should be added to ORDER BY clause, ORDER BY 1,2,3,4,rowid – to handle duplicates (as Richard pointed out), because every index is ordered by (x, rowid) where x is an ordered set of indexed columns.

Richard Foote - September 21, 2011

Thanks Radoslav

I have to have a play with it :)

I’m unsure how it will handle dulplicate indexed values but I the washing up to do so it will have to wait !!

albertofrosi - September 23, 2011

Hi Radoslav,
I’ve tryed to re-run this procedure with your advices but the result remains the same.
Here the output:

begin
dbms_stats.gather_index_stats(user, ‘FABC_1′, estimate_percent=>NULL );
end;

SELECT SUM(block_change) FROM (SELECT CASE lag(dbms_rowid.rowid_block_number(ROWID)) over(ORDER BY 1, 2, 3, 4,rowid) column
WHEN dbms_rowid.rowid_block_number(ROWID) THEN 0
ELSE 1
END block_change
FROM FABC);

SUM(BLOCK_CHANGE)
———————-
4201619

select clustering_factor from user_indexes where index_name = ‘FABC_1′

CLUSTERING_FACTOR
———————-
7271595

Thanks,
Ciao

Alberto

7. Donatello Settembrino - September 23, 2011

Hi Alberto,
try running this query:

select /*+ no_parallel_index(t, “T2_IX1″)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,”T2_IX1″) */
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from “SETTEMBRINO”.”T2″ t
where “N1″ is not null;

need to replace your table in the from clause, the fields
index in the where conditions and in the hints section, the index name you are considering

let me know if you get the same result as Radoslav ….
I posted a comment on this issue,
I think it is awaiting review by Richard

Regards,
Donatello

albertofrosi - September 24, 2011

Hi Donatello,
Thanks for your reply, I run your statement ,here is the output:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from FABC t
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

CLF
———————-
7259626

Yes CF seems about the same.

Ciao
Alberto

8. Donatello Settembrino - September 24, 2011

Ciao Alberto,
you did an upgrade version of the DB?
(example from 10.2.0.3 to 10.2.0.5 or similar?)
and you haven’t recalculated the statistics
after ugrade to the new version of the db, because for example use
the OPTIONS GATHER STALE parameter of DBMS_STATS?

9. albertofrosi - September 24, 2011

Hi Donatello,
No I did upgrade in this DB, anyway i calculate statistics every night with this procedure:

dbms_stats.gather_schema_stats (ownname => ‘Test’,
estimate_percent=>dbms_stats.auto_sample_size,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1′,
granularity => ‘ALL’,
cascade => TRUE,
degree => 7);

I use auto_sample_size paramenter of dbms_stats
Thanks,
Ciao

Alberto

10. Donatello Settembrino - September 24, 2011

that was not what I had in mind…

however,your table is populated by more cuncurrent process?

The query that you run:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from FABC t
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

is the query executed by Oracle to compute the CF
and you can see, running a trace, while DBMS_STATS
is running

alter session set sql_trace=true;

exec DBMS_STATS.GATHER_TABLE_STATS (……);

alter session set sql_trace=false;

Your problem may be related to the question that
I have done of cuncurrent insert.
In fact, resading your problem I came up with a test
that some time ago I read Randolf Blog:

http://oracle-randolf.blogspot.com/2010/01/clusteringfactor-what-if-analysis.html

I suggest you try increasing the last parameter (1) of
function

sys_op_countchg(substrb(t.rowid,1,15),1)

and rerun the query.
If my assumptions are correct
the resulting value should coincide with the value
Radoslav query, which should calculate the correct value of CF
I assume that since the CF of your table is wrong.
I say this because the query is very similar to a query
I reported some time ago on the blog of Charles Hooper
where they spoke of CF

http://hoopercharles.wordpress.com/2011/03/21/nested-loops-join-the-smaller-table-is-the-driving-table-the-larger-table-is-the-driving-table/

I await your results ..I’m very curious to discover the cause

Regards,

Donatello

11. Donatello Settembrino - September 24, 2011

In addition, the query that you run is wrong …


where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

In your case, suppose you have created the following index for the table fabc_1:

create index on ix_fabc1 fabc_1 (c1, c2, c3, c4);

the query that you have to do is the following:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from FABC t
where C1 is not null
and C2 is not null
and C3 is not null
and C4 is not null;

where c1, c2, c3, c4 are the 4 fields of your index…

12. albertofrosi - September 24, 2011

Hi Richard,
I’ ve tryed to post my comment, desn’t appear in your blog… :-(
Are there problems ?
Ciao

Alberto

13. albertofrosi - September 26, 2011

Hi Donatello,
Thanks for your reply I run:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’TEST’,TABNAME=>’FABC’,CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL);

I’ve tryed this:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),2) as clf
from FABC t
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

CLF
———————-
5723474

Where my columns 1,2,3,4 are unique index FABC_1, and add +1 on sys_op_countchg parameter.

I re-run Radoslav query:

SELECT SUM(block_change) FROM (SELECT CASE lag(dbms_rowid.rowid_block_number(ROWID)) over(ORDER BY 1, 2, 3, 4,rowid) —- ordered by indexed column
WHEN dbms_rowid.rowid_block_number(ROWID) THEN
0
ELSE
1
END block_change
FROM FABC);

SUM(BLOCK_CHANGE)
———————-
4203647

to be countinued….

14. albertofrosi - September 26, 2011

I read you post on Charles Hooper’s blog and so:

select count(*) clusf
from (
with dist_key_blk as (
select distinct 1
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999) over (ORDER BY 1) as next_blk
from FABC
order by 1, 2
)
select 1
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk
)
;

CLUSF
———————-
1867794

select count(*) clusf
from (
with dist_key_blk as (
select distinct 2
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999) over (ORDER BY 2) as next_blk
from FABC
order by 1, 2
)
select 2
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk
)
;

CLUSF
———————-
3903963

select count(*) clusf
from (
with dist_key_blk as (
select distinct 3
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999) over (ORDER BY 3) as next_blk
from FABC
order by 1, 2
)
select 3
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk
)
;

CLUSF
———————-
423607

select count(*) clusf
from (
with dist_key_blk as (
select distinct 4
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999) over (ORDER BY 4) as next_blk
from FABC
order by 1, 2
)
select 4
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk
)
;

CLUSF
———————-
1443219

to be continued….

15. albertofrosi - September 26, 2011

Eventually I run this query also:

SELECT a.table_name,
a.index_name,
a.clustering_factor,
a.num_rows,
b.BLOCKS as table_blocks
FROM user_indexes a
join user_tables b
on a.TABLE_NAME = b.TABLE_NAME
where a.TABLE_NAME = ‘FABC’

ORDER BY a.TABLE_NAME,
a.INDEX_NAME;

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR NUM_ROWS TABLE_BLOCKS
——————————————————————

FABC FABC_1 7015754 8273302 716357

Adding the single four CF result for columns 1,2,3,4 the result it’s about the same,
but table blocks are always 716357.
Did i some mistakes?
Let me know, i’m very curious.
Ciao

Alberto

P.S.:Sorry I had to split one post in three separate because I gave an error. ;-)

16. Donatello Settembrino - September 26, 2011

Ciao Alberto,

I think we need to clarify some things and I need
you answer me these 3 questions:

1)

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),2) as clf
from FABC t
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

to ensure that the query is correct,
in where conditions where you should change the condition in

….
where field_index1 is not null
and field_index2 is not null
and field_index3 is not null
and field_index4 is not null;

then, view the result…

albertofrosi - September 26, 2011

Hi Donatello,
thanks for your reply, i have 1 unique index FABC_1 with 4 fields (1,2,3,4).
My where condition are:

where field(1)_index1(FABC_1 ) is not null
and field(2)_index1(FABC_1 ) is not null
and field(3)_index1(FABC_1 ) is not null
and field(4)_index1(FABC_1 ) is not null

applying this I have:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),2) as clf
from FABC t
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

and this is the result:

CLF
———————-
3921956

17. Donatello Settembrino - September 26, 2011

Then you can use the query Radoslav or my query, but if
you use my query you should do it like this:

select count(*) clusf
from (with dist_key_blk as
(select distinct index_field1
, index_field2
, index_field3, index_field4
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999)
over (ORDER BY c1) as next_blk
from t2
order by 1, 2, 3, 4, 5 )
select index_field1, index_field2, index_field3, index_field4
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk;

and replace the fields index_field1, index_field2, index_field3,
index_field4 with the fields of your index

then, view the result

2) your table is populated by more cuncurrent process?

3) If your goal is to, improve the CF index
FABC_1, you must, if possible, to intervene in the loading
the table, sorting the data for the 4 columns in the index:

eg. if the table is populated with an insert statement

insert into fabc
select …
from…
order by index_field1, index_field2, index_field3, index_field4;

where index_field1, index_field2, index_field3, index_field4,
are the fields fabc_1 index.

In fact, observing the data you provided:

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR NUM_ROWS TABLE_BLOCKS
———- ———– —————— ——— ————
FABC FABC_1 7015754 8273302 716357

I see that the CF is very close to the number of rows in the table
and this could be that the index is not used.

If you use my suggestion, of course after having recalculated the statistics,
you’ll notice that the CF is much closer to the number of blocks
in table, making the index “more interesting” to the CBO.

Regards

P.S. Sorry, I have to break the answer into several pieces..

18. albertofrosi - September 26, 2011

Hi Donatello,
thanks for your reply, I executed your query:

select count(*) clusf
from (with dist_key_blk as
(select distinct 1
, 2
, 3, 4
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999)
over (ORDER BY 1
, 2
, 3, 4) as next_blk
from FABC
order by 1, 2, 3, 4, 5 )
select 1
, 2
, 3, 4
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk);

CLUSF
———————-
4185072

2) Yes it’s a big table and may be populated by more concurrent process.

3) Yes my goal is improve the CF on index FABC_1 for example.
I’m agree with you about CF, but in this table there would other 27 indexes over 108 fields.
even if delete and recreate the table are sure that your example works well, but for the other 26 indexes how could I do?
My goal is making the index (this or another one for example) “more interesting” to CBO.
what could be the right approach for this problem?
Thanks a lot in advance for all your help.
Ciao

Alberto

19. Donatello Settembrino - September 26, 2011

2) could you tell me how many concurrent processes are used to load your table?

3) the only way I know is to see the history of access for your table … and try to improve the CF of those who can bring significant improvements … there are too many indexes and obviously you can not improve the CF for all …. you should see the CF for all the indices, if the CF is “bad” for all, improving the CF, for example, the index fabc_1 not have problems … but if
the CF for some of them is “good” might “damage” access to these indexes.

20. albertofrosi - September 27, 2011

2) usually 2.

3) Exactly….in theory I can try to execute:
insert into fabc
select …
from…
order by index_field1, index_field2, index_field3, index_field4;
where these fields are my unique index.
Surely FABC_1 index will have a good CF much closer to the number of blocks. The others (for example FABC_2) will have maybe a poor CF but so making the index FABC_1 “more interesting” to the CBO.
In this table FABC there are 27 indexes but none of these are CF near to blocks table, all are very far
What do you think?

Ciao
Alberto

21. Donatello Settembrino - September 27, 2011

2) after you have increased sys_op_countchg (SUBSTRB (t.rowid, 1.15), 1) to sys_op_countchg (SUBSTRB (t.rowid, 1.15), 2) in your query Your CF is decreased, so I think that your case is very similar to the problem mentioned by Randolf, in his blog (see link above). To arrive at the correct CF, you should know the exact number of concurrent processes that load your table to find the correct number to be included in the function sys_op_countchg,
to arrive at the correct CF (to be corrected later with DBMS_STATS.SET_INDEX_STATS ….).
Try to increase, however, still sys_op_countchg …. IMHO the current CF (those calculated using DBMS_STATS) is wrong

3) I think we have to solve point 2), then to focus on point 3).
However, you can see all the CF of your indexes?

22. albertofrosi - September 27, 2011

ok perfect Donatello, i create a table to monitor concurrent processes
like Randolf’s post:

create table t1 (
run_id integer not null,
batch_id integer not null,
a_value number null,
a_random number null,
a_date timestamp default systimestamp not null,
filler char(1) default ‘x’ not null
)
tablespace &tblspace;

create index t1_idx1 on
t1 (
batch_id,
a_value
)
tablespace &tblspace;

create sequence seq_t1_run_id;

create sequence seq_t1_seq_id;

create or replace procedure populate_t1
(i_run_id in integer, i_iter in integer) as
begin
dbms_output.put_line(
dbms_lock.request(
1
, dbms_lock.s_mode
, release_on_commit => true
)
);
commit;
for i in 1..i_iter loop
for j in 1..100 loop
insert into t1 (
run_id
, batch_id
, a_value
, a_random
)
values (
i_run_id
, i
, seq_t1_seq_id.nextval
, trunc(dbms_random.value(1, 1000))
);
commit;
dbms_lock.sleep(0.01);
end loop;
end loop;
end;
/

is it correct these statements?
So i can view how many concurrent process that load my table.
We can solve point 2.
thanks,
Ciao
Alberto

23. albertofrosi - September 27, 2011

I read the post more carefully I have to create a similar table as t1 in the example of Randolf and then associate a trigger for each insert to the table FABC, so that it goes to populate the table T1.
In the Randolf’s example the insert were generated by another session which run populate_t1 procedure.
is it correct?

Alberto

24. Donatello Settembrino - September 27, 2011

Alberto, I have not tried the script Randolf …
initially try to simply increase the number in the function
for example sys_op_countchg (SUBSTRB (t.rowid, 1.15), 4) to see if the CF decreases again and if our reasoning is correct …

25. albertofrosi - September 27, 2011

Hi Donatello,
I executed some tests changing the number in the function.
I start with 4:

select /*+ no_parallel_index(t, ‘FABC_1′)
dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad
no_expand index (t,’FABC_1′) */
sys_op_countchg(substrb(t.rowid,1,15),4) as clf
from FABCt
where 1 is not null
and 2 is not null
and 3 is not null
and 4 is not null;

CLF
———————-
3585939

sys_op_countchg(substrb(t.rowid,1,15),5)

CLF
———————-
2927266

sys_op_countchg(substrb(t.rowid,1,15),10)

CLF
———————-
2420546

sys_op_countchg(substrb(t.rowid,1,15),15)

CLF
———————-
2369279

After sys_op_countchg(substrb(t.rowid,1,15),15) I can see no change in the CF number.
Ciao

Alberto

26. Donatello Settembrino - September 27, 2011

Ok,
now run my query

select count(*) clusf
from (with dist_key_blk as
(select distinct 1
, 2
, 3, 4
, dbms_rowid.rowid_block_number(rowid) as current_blk
, lead(dbms_rowid.rowid_block_number(rowid), 1, 99999999)
over (ORDER BY 1
, 2
, 3, 4) as next_blk
from FABC
order by 1, 2, 3, 4, 5 )
select 1
, 2
, 3, 4
, current_blk
, next_blk
from dist_key_blk
where current_blk next_blk);

27. albertofrosi - September 27, 2011

this is the result:

CLUSF
———————-
4187152

Richard Foote - September 28, 2011

You two should meet over a few beers :)

Donatello Settembrino - September 29, 2011

I love beer… :)
but usually after the beer I never get to talk bad clustering factor …
wrong clustering factor indicate me that something is not working properly, just like when too much beer dulls the brain :)

28. albertofrosi - September 28, 2011

In fact we do this!
indeed we are already doing … :-)

we’re working on, after when we have the solution (maybe) you say … :-)

29. | Sing's Tech Musings - January 12, 2012

[...] Big Tables, Sorts and Indexes Solution (Right On Mother) (richardfoote.wordpress.com) Share this:EmailPrintFacebookShareDiggStumbleUponRedditRelated posts: [...]

30. Full Table Scan / Sequential Scan in MySQL: Understanding when this happens with EXPLAIN statement and avoiding optimizer’s choice of incorrect table scan | Sing's Tech Musings - January 12, 2012

[...] Big Tables, Sorts and Indexes Solution (Right On Mother) (richardfoote.wordpress.com) Share this:EmailPrintFacebookShareDiggStumbleUponRedditNo related posts. [...]


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,862 other followers

%d bloggers like this: