First, I was commenting on the precepts -- they are not the right ones.

Second, we can in fact predict this stuff -- without waiting for it to break!  We don't need to wait for a structure to break apart before knowing it will break apart!


All we need know is how the structure is used.  

Take the 

    * Multi-block Index range scans will run far faster when the data blocks are 
arranged in index-key order (as evidenced by clustering_factor in dba_indexes).

precept.  ABSOLUTELY true (I've written chapters on this in Expert one on one Oracle and Effective Oracle by Design).  Now, what can we do about it? And better yet how do you fix it?

a) reorg the structure every week/month over and over and over and over again....

b) recognize the futility in that (stuff works good for a day, but breaks down again) or ..... implement the proper structure that will enforce as data is added this "locale" of information.  You see, I do not want to predicte (actually, I'm going to stop saying predict because I keep writing predicate and because it is not predicting -- it is realizing "houston we have a problem here and our structure has (past tense) broken down").

You see I do not want to have to keep fixing the thing, I want to understand how the data is used and then employ the proper structure (this is what is missing, this is not about "when to reorg" this is about "stop reorging and start living man, we do not need to keep going to this play over and over again")

Here is a "for example" fix to 

    * Multi-block Index range scans will run far faster when the data blocks are 
arranged in index-key order (as evidenced by clustering_factor in dba_indexes).

because you have to remember -- how many ways can a table be sorted?  (another option could be a b*tree or hash cluster as well)

<quote Effective Oracle by Design>

Use IOTs to Colocate Randomly Inserted Data

In addition to being a space-saving device, by obviating the need for some redundant tables, IOTs excel in their ability to physically colocate related information for fast access. If you recall, one of the downsides to a cluster is that you need to have some control over the arrival of the data in order to optimize the physical colocation of the data. IOTs do not suffer from this condition, because they will readjust themselves structurally in order to accommodate the data as it is inserted.

Consider an application that frequently retrieves a list of documents owned by a given user. In the real world, the user would not insert all of the documents he or she will ever own in a single session. This is a dynamic list of unpredictable size that will constantly be changing as the user adds and removes documents in the system. Therefore, in a traditional heap table, the rows that represent this user's documents would be scattered all over the place. Consider what would happen when you run a query such as this:

select * from document table where username = :bind_variable

Oracle would use an index to read many dozens of blocks from all over the table. If we used an IOT to physically cluster the data together, this would not happen. We can observe this behavior with a simple simulation and AUTOTRACE. For this example, we'll set up a pair of tables: one using an IOT and the other using a heap-based implementation.

ops$tkyte@ORA920> create table iot
  2  ( username varchar2(30),
  3    document_name varchar2(30),
  4    other_data       char(100),
  5    constraint iot_pk
  6    primary key (username,document_name)
  7  )
  8  organization index
  9  /
Table created.

ops$tkyte@ORA920> create table heap
  2  ( username varchar2(30),
  3    document_name varchar2(30),
  4    other_data       char(100),
  5    constraint heap_pk
  6    primary key (username,document_name)
  7  )
  8  /
Table created.

We use a CHAR(100) just to make the average width of a row in these tables about 130 bytes or so, since a CHAR(100) will always consume 100 characters of storage (it is a fixed-width datatype). So, the only difference between these two tables (besides their names) is the addition of the ORGANIZATION INDEX clause on the second one. That instructs Oracle to store the table data in an index segment instead of a table segment, so that all of the data for that table will be stored in an index structure.

Next, we populate these tables with some sample data. We construct a loop that adds 100 documents for each user in the ALL_USERS table. We do this in a fashion that emulates real life, in that the documents for a given user are added not all at once, but rather over time after many other documents have been added by other users. 


ops$tkyte@ORA920> begin
  2      for i in 1 .. 100
  3      loop
  4          for x in ( select username
  5                       from all_users )
  6          loop
  7              insert into heap
  8              (username,document_name,other_data)
  9              values
 10              ( x.username, x.username || '_' || i, 'x' );
 11
 12              insert into iot
 13              (username,document_name,other_data)
 14              values
 15              ( x.username, x.username || '_' || i, 'x' );
 16          end loop;
 17      end loop;
 18      commit;
 19  end;
 20  /
PL/SQL procedure successfully completed.


And now we are ready for our performance comparison. Here, we will read all of the data from our table, user by user; that is, for USER1, we'll read out all of the rows that correspond to that user, and then for USER2, and so on. Additionally, we'll do this reading in two ways-one time using BULK COLLECT and the next using single-row fetches-just to see the difference that array processing can have on performance and scalability, and to show how significantly different this IOT can be from a heap table. Our benchmark routine is as follows:

<b>NOTE: in 10g, plsql silently array fetches 100 rows at a time, so this example will show similar performance for the two.  A silent bonus of 10g plsql</b>


ops$tkyte@ORA920> alter session set sql_trace=true;
Session altered.

ops$tkyte@ORA920> declare
  2      type array is table of varchar2(100);
  3      l_array1 array;
  4      l_array2 array;
  5      l_array3 array;
  6  begin
  7  for i in 1 .. 10
  8  loop
  9      for x in (select username from all_users)
 10      loop
 11          for y in ( select * from heap single_row
 12                      where username = x.username )
 13          loop
 14              null;
 15          end loop;
 16          for y in ( select * from iot single_row
 17                      where username = x.username )
 18          loop
 19              null;
 20          end loop;
 21          select * bulk collect
 22            into l_array1, l_array2, l_array2
 23            from heap bulk_collect
 24           where username = x.username;
 25          select * bulk collect
 26            into l_array1, l_array2, l_array2
 27            from iot bulk_collect
 28           where username = x.username;
 29      end loop;
 30  end loop;
 31  end;
 32  /
PL/SQL procedure successfully completed.


The TKPROF report for the single-row fetches shows the following statistics:


select * from heap single_row where username = :b1

call     count    cpu elapsed disk   query current     rows
------- ------  ----- ------- ---- ------- -------  -------
Parse        1   0.00    0.00    0       0       0        0
Execute    440   0.05    0.05    0       0       0        0
Fetch    44440   1.50    1.42    0   88886       0    44000
------- ------  ----- ------- ---- ------- -------  -------
total    44881   1.56    1.48    0   88886       0    44000

Rows     Row Source Operation
-------  ---------------------------------------------------
  44000  TABLE ACCESS BY INDEX ROWID HEAP
  44000   INDEX RANGE SCAN HEAP_PK (object id 43271)
***********************************************************
select * from iot single_row where username = :b1

call     count    cpu elapsed disk   query current     rows
------- ------  ----- ------- ---- ------- -------  -------
Parse        1   0.00    0.00    0       0       0        0
Execute    440   0.07    0.05    0       0       0        0
Fetch    44440   1.11    0.99    0   44987       0    44000
------- ------  ----- ------- ---- ------- -------  -------
total    44881   1.18    1.04    0   44987       0    44000

Rows     Row Source Operation
-------  ---------------------------------------------------
  44000  INDEX RANGE SCAN IOT_PK (object id 43273)

The heap table, with its read-the-index-then-the-table approach, will do at least twice the I/O row by row by row. It must read the index block, and then read the table block. The IOT approach, on the other hand, simply reads the index and is finished. So, this is pretty good. Any day we can cut in half the number of I/Os our system must perform is a good day. Can it be even better? Yes, it can. 

In Chapter 2, we used AUTOTRACE to demonstrate the effect that different array sizes (how many rows Oracle fetches in response to each fetch request) could have on an application. We saw that we can significantly reduce the I/O performed by a query if we fetch many rows at a time instead of fetching single rows. Here, we can really show the power of the IOT over the heap table in this regard. Reading further on in the TKPROF report we see this:

SELECT * from heap bulk_collect where username = :b1

call     count    cpu elapsed disk   query current     rows
------- ------  ----- ------- ---- ------- ------- --------
Parse        1   0.00    0.00    0       0       0        0
Execute    440   0.06    0.05    0       0       0        0
Fetch      440   0.49    0.48    0   36100       0    44000
------- ------  ----- ------- ---- ------- ------- --------
total      881   0.55    0.54    0   36100       0    44000

Rows     Row Source Operation
-------  ---------------------------------------------------
  44000  TABLE ACCESS BY INDEX ROWID HEAP
  44000   INDEX RANGE SCAN HEAP_PK (object id 43271)
***********************************************************
SELECT * from iot bulk_collect where username = :b1

call     count    cpu elapsed disk   query current     rows
------- ------  ----- ------- ---- ------- -------  -------
Parse        1   0.00    0.00    0       0       0        0
Execute    440   0.06    0.05    0       0       0        0
Fetch      440   0.24    0.24    0    2110       0    44000
------- ------  ----- ------- ---- ------- -------  -------
total      881   0.31    0.30    0    2110       0    44000

Rows     Row Source Operation
-------  ---------------------------------------------------
  44000  INDEX RANGE SCAN IOT_PK (object id 43273)

The IOT using bulk fetches did less than 6% of the I/O of the query against the heap table using the same bulk collect. When running this test with Runstats instead of TKPROF, I discovered the heap table approach used 600% more latches. These are some serious differences here!
</quote>



So, do you still want to reorg tables?  (please anyone -- list out the steps to reorg a table "sorted" in 10gr1 and before and do it online)


We can predict based on your usage pattern of the structure -- before you use it -- what issues will arise.  That is were we humans can focus our energies -- because the database is getting smarter at realizing our mistakes and doing the best it can to help us out.

But remember, the database will NEVER be able to turn:


for x in ( select * from t1 ) 
loop
   insert into t2 values ( x.a, x.b, x.c, ... );
end loop;

into 

insert into t2 (a,b,c,...) select a,b,c,... from t1;

That is -- the database will never be able to fix a bad implementation, it can only try to do it as fast as it can.



<quote>
I have a source code control system that chains rows in one of the 
tables.  After complaints from the development staff and looking into the 
problem I did reorg to fix the chaining.  A 20 second process went to 2 or 3 
seconds.  The reorg did exactly what I predicted.  Now, it may happen again in 
the future, but . . . can it be predicted ahead of time.  Or, even provide me 
with a warning?
</quote>

can you predict that?  absolutely.  Do you insert rows with lots of missing (null) or short fields?  Do you update them over time?  Did you leave the default 10% free?

You will migrate the rows.

Then, do you frequently access this object ALOT by plans similar to this:

table access by index rowid
   index range or unique scan

If so, the migration of rows (note: not a full scan, but rather the number of table access by index rowids is what will be an issue here -- that was another point that the precept was wrong on) should be anticipated -- to the point that you can either


a) upon table creation say "look the row starts life small, but ends life at about 1,000 bytes.  I have an 8k blocksize.  So I want at most 7 or 8 rows per block.  Pctfree should be really high to accomidate this (more than the default 10%)

b) figure out the say 7/8 and use the miminize rows per block above



I would not want to win the contest based on the above 4 precepts, no, still not.  I'd rather expend energy helping you figure out how never to get into the situations where a reorganization would be called for (the software is helping you there already over time). 

So you see, I find the above 4 precepts as rather dangerous -- too narrow and not really accurate.

It is true that index fast full scans will run faster against 500 blocks versus 1000.  But -- where was the discussion of what will not run faster?  And for how long does this fix apply?  How long does it run faster?  This precept needs much fleshing out.

Multi-block index range scans -- still like the list of things to do to "fix that", it is not a reorganziation in the current sense of the word.

Last table full-table scans will run faster after un-chaining and un-migrating.   They will not.

Table updates being affected by "unbalanced freelists" performance wise?  


I only hope you don't start with these 4 "laws" 

But if you do gain an understanding of the structures available to you -- beyond the HEAP table and the B*TREE index -- you'll find many ways to avoid many of these issues and to be able to recognize that something might be unavoidable (but then again, 10g r1 will catch many things for you -- like the "low density table" and offer to shrink it -- the table.  That in fact, the shrink, was one of my favorite top 10 things about 10g.  If you cannot use partitioning to solve your purge problem, at least we do have that, but the database actually tells us about it)


My entire initial point was "I have problems with these precepts", and I still do. 

<b>(do you really use this?  i mean, you join dba_tables to dba_segments by table_name to segment_name?

So you join MY index to YOUR table
and your table to your table
and your table to their rollback segment

seem to be missing quite a few predicates.

or did you just whip this freelist GROUP thing up (and why just 2 freelist groups -- what if it was the 3rd one? or the 4th, or the 5th?  no loop over the number of freelist groups?


and with a locally managed tablespace, well, this predicate:

empty_blocks > 3*(a.next_extent/4096)

returns "I don't know" because a.next_extent is null in DBA_TABLES -- but given that freelist management differents not a whit between locally and dictionary managed tablespaces (I keep saying that, but you don't seem to hear it -- there is a difference between ASSM and Manual segment space managment -- but as far as locally/dictionary managed tablespaces - nope)

I don't see how this would frequently "see" anything in many/most 9i databases and even in 8i since it would not actually see most tables! (and given the missing predicates the comparisions with b. anything in the predicate are somewhat "random" -- you might be looking at b.extents for an index name 'FOO' and comparing that to a table named 'FOO')
</b>

that could show unbalanced FREELIST GROUPS to which there is no dispute whatever -- that has never been in question.  The question was about process freelists.

So Don (same IP address within minutes of prior posting, have to assume it was you -- feel free to clarify otherwise), you've just proven that <b>freelist groups</b> may become unbalanced -- but again, we ask "in the context of your statement regarding performance -- what does this show?  a performance issue OR a space management issue?"


And "alter table xxx storege (freelists x);"  does not result in "Oracle adds 
an addional segment header somewhere in the table."

It simply adds an initially empty freelist for the segment which will pull 5 blocks from the master freelist when it needs.

Since a "segment header" would be a block -- one would expect that adding a freelist would change the blocks used by a segment if this were true (sorry, this is a single user test, but it might still be valid):

ops$tkyte@ORA9IR2> create table t as select * from all_objects;
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................               0
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................               3
Unused Bytes............................          24,576
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             265
Last Used Block.........................             125
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter table t storage (freelists 2 );
Table altered.
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................               0
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................               3
Unused Bytes............................          24,576
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             265
Last Used Block.........................             125
 
PL/SQL procedure successfully completed.


Now,  adding a freelist group -- that would do that (add another separate data structure), but that is not something you can "alter in", that requires a rebuild of the segment.



So, again, we are back to "where or how did you see freelists becoming unbalanced? and more importantly with regards to this discussion, what impact on *performance of modifications* did this have?" 

lauging out loud, that is what I've been saying (keep it technical, address the points), as I stated -- I don't edit other peoples content.  I don't make people log in.  If they use racist or foul language, I'll delete it -- but whatever.




Mr. Cunningham described chained rows, the result of inserting "small rows" and updating them to big rows.  Something notoriously hard to fix with a simple reorg (ground hog day all over again), but by taking a second to look at the problem -- and applying your knowledge of how the row starts in life, how it grows -- you can come up with solutions (not just a rebuild which you'll do over and over).  You can say "ah, all of the rows are grown right now, reorg, alter set pctfree higher for new stuff".  You can say "ah, the rows are a mess right now, some with status='X' are done, status='Y' are half done, and newly inserted rows need something yet different for their pctfree -- lets rebuild in two stages and correct the problem".  Or you can use Jonathans approach with the minimize records per block.








Your space utilization issue as you describe is classic freelist groups -- it would not happen with simple process freelists.  <b>Your script as it is above would show you FREELIST GROUP issues, not freelist issues.</b>  Your script as it is, would not show you problems with locally managed tablespaces -- even though they definitely have them.  It would not show you issues with tables that were not analyzed via the ANALYZE command.  And it is not an internals note to know this -- it is somewhat inherit in the design of freelist groups.  One of the reasons ASSM was created. (since almost all freelist group use is under RAC or OPS).

I cannot find any such note and you have access to basically the same system I do, just with blue images and proportional fonts.

Would you care to comment on this:

<quote>
After a single-threaded purge, dba_free_space showed 3 gig free, yet the table immediately began extending,
</quote>

and help me understand how that relates to 50 freelists?  That is, what exactly is the relationship between space that is not allocated to any segment -- dba_free_space -- and the management of space within a segment on freelists?  

I would have to assume that BEFORE the purge there was 3 gig free in dba_free_space or someone else truncated a table (no freelists), dropped something (no freelists) or added disk) but it was not related to the purge -- and if 200 clerks typed in 3gig of data in a short time, well, ok (you see the 3 gig in dba_free_space was there to satisfy the extends)

<b>Could it be that your purge didn't put anything ON the freelists?</b> (hey, remove one row per block and perhaps nothing will happen, you can remove alot of rows, but still not put anything on the freelists).

You weren't looking at the freelists but unallocated space (apparently).  That could certainly be an issue.  

In retrospect, wouldn't it be nice to know "why" though?  I get that question many times "we'd like to know why so we can avoid it in the future".  I'd want to know why, but that is me.  Again, this is a measely single user test, but it does demonstrate that a purge will not necessarily free anything

(and might be instructive as to why you want to design for a purge if possible so you would not be forced to reorg and rebuild after them?)



ops$tkyte@ORA9IR2> create table t pctfree 10 pctused 5
  2  as
  3  select rownum id, all_objects.* from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................               0
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................             113
Unused Bytes............................         925,696
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             521
Last Used Block.........................              15
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> delete from t where mod(id,10) <> 0;
 
25020 rows deleted.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................               0
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................             113
Unused Bytes............................         925,696
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             521
Last Used Block.........................              15
 
PL/SQL procedure successfully completed.

<b>I just purged 90% of the data -- nothing on the freelist though.  dba_free_space, it is not really relevant here, there are not blocks on the freelist for inserts... There could have been:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t
  2  as
  3  select rownum id, all_objects.* from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................               0
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................             113
Unused Bytes............................         925,696
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             521
Last Used Block.........................              15
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> delete from t where mod(id,10) <> 0;
 
25020 rows deleted.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
Free Blocks.............................             398
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................             113
Unused Bytes............................         925,696
Last Used Ext FileId....................               8
Last Used Ext BlockId...................             521
Last Used Block.........................              15
 
PL/SQL procedure successfully completed.

<b>Now I have almost 400 blocks on the freelist -- pretty much all of them (and if I had multiple freelists -- they would not have all ended on one).</b>



<quote>
Whenever someone makes a bold statement 
yet remains anonymous, Im naturally curious to know if the person REALLY knows 
what they are talking about
</quote> 

and so I still cannot figure out who it was that prompted you to create that article and link to it here then. 


I look at peoples work, experience, their writing, their contributions, their attention to detail.  Lot's of people  went to college and university.  Does not imply anything at all.


<b>the point of this entire thread was to me the precepts.</b>

The proposed method -- building a predictive model, fine.  

As someone you apparently respect said above:

..  has proved that the precepts are naive and simplistic, and that anyone who spends the effort writing a progam against them is almost certainly wasting their time.

That was my point here, that was the point of the thread.  And hey, if Sushil is building it -- and he has internals knowledge, isn't our time perhaps spent better on understanding it...


 

you mean even worse idea than you thought.

<b>disclaimer, nothing in this followup here means, infers, implies that the converse of what I'm saying is not true, I can come up with examples that show that reordering of the columns in a concatenated index can in fact improve performance -- but it would be the rarest of all cases where the clustering fact was the driving reason.  HOW the data is used is paramount.  Your DOMAIN KNOWLEDGE of the data is second (what you know about the data itself).  The clustering factor -- if considered -- would be down my list</b>


for the thing that drives the selection of the order of the columns in a concatenated index is......

mostly the questions you ask of the data.


Unless you want to go back to the drawing board that is and come up with a complete new set of indices to go with your nicely clustered index that is.



Take the simple example:

select * from T where ..... (t copy of all objects)

where owner = :x                            (show me all of SCOTT's objects)
where owner = :x and object_type = :y       (show me SCOTT's tables)
where owner = :x and object_type = :y and object_name = :z (show scotts emp table)


you can either have

a) one index on (owner,object_type,object_name) to satisfy all three.

or say you discovered the table was 'sorted' by object_name, object_type, owner -- you created the index in that order, you would need:

b) index on (owner,object_type)
   index on (object_name,object_type,owner)

To maintain the performance of the first two queries and make the third (which at this point is actually pretty much a single row fetch and hence clustering factor doesn't really matter at all but we'll ignore that) have a better clustering factor. 

(hopefully that is obvious to all or most -- that you need two indexes in order to even come a tiny bit close to the performance of the first ordering).  




Or, say you have a predicate as simple as:

where a = :a and b > :b and c = :c

and there is an index on (a,b).  You discover the table is sorted by B,A however.  What can happen to us?


ops$tkyte@ORA9IR2> create table t ( a int, b date, c int, d char(50) );
Table created.
 
ops$tkyte@ORA9IR2> insert into t
  2  select dbms_random.value(0,50), sysdate-10000+rownum, dbms_random.value(0,1000), 'x'
  3    from all_objects;
27982 rows created.
 
<b>Now, since B is unique, this table is in fact "pretty much sorted by B, A" -- we'll see that with the cluster factor in a moment..</b>


ops$tkyte@ORA9IR2> create index t_idx on t(a,b); 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select *
  2    from ( select num_rows, blocks from user_tables where table_name = 'T'),
  3         ( select clustering_factor from user_indexes where index_name = 'T_IDX' );
 
  NUM_ROWS     BLOCKS CLUSTERING_FACTOR
---------- ---------- -----------------
     27982        275             12053

<b>the closer to the number of rows and the further from the number of blocks a clustering fact is, the "worse" it is. This one is far from the number of blocks</b>

 

ops$tkyte@ORA9IR2> variable a number;
ops$tkyte@ORA9IR2> variable b varchar2(25)
ops$tkyte@ORA9IR2> variable c number

ops$tkyte@ORA9IR2> exec :a := 42;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec :b := to_char(sysdate,'yyyymmdd')
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec :c := 55;
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select a,b,c
  2    from t bad_cf
  3   where a = :a
  4     and b > to_date(:b,'yyyymmdd')
  5     and c = :c;
 
no rows selected
 
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> select a,b,c
  2    from t bad_cf
  3   where a = :a
  4     and b > to_date(:b,'yyyymmdd')
  5     and c = :c;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=15)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=5 Card=1 Bytes=15)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=5)
 
 
<b>Note the cost of using the index, note the number of IO's -- 157</b> 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        157  consistent gets
          0  physical reads
          0  redo size
        315  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
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> @traceoff
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context off';
 
Session altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop index t_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> create index t_idx on t(b,a);
 
Index created.
 
<b>so, we reorder the index columns...</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select *
  2    from ( select num_rows, blocks from user_tables where table_name = 'T'),
  3         ( select clustering_factor from user_indexes where index_name = 'T_IDX' );
 
  NUM_ROWS     BLOCKS CLUSTERING_FACTOR
---------- ---------- -----------------
     27982        275               275

<b>and note that the clustering factor -- well, it just don't get better than that</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable a number;
ops$tkyte@ORA9IR2> variable b varchar2(25)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :a := 42;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec :b := to_char(sysdate,'yyyymmdd')
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select a,b,c
  2    from t good_cf
  3   where a = :a
  4     and b > to_date(:b,'yyyymmdd')
  5     and c = :c;
 
no rows selected
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
ops$tkyte@ORA9IR2> select a,b,c
  2    from t good_cf
  3   where a = :a
  4     and b > to_date(:b,'yyyymmdd')
  5     and c = :c;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=15)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=252)
 
 
<b>cost of the query went down....but</b> 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        211  consistent gets

<b>the amount of work went up :(</b>


          0  physical reads
          0  redo size
        315  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
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> @traceoff
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context off';
 
Session altered.
 
ops$tkyte@ORA9IR2> spool off


<b>
Here, when we could "goto a=42, find b > sysdate", it was easy - all of the a=42's where together and we range scanned a small set of b > sysdate within that.  We looked at less then 1/50 of the rows in the table via the index (because a has 50 values -- a=42 would be about 1/50th)

When we could not do that, we had to "goto b > sysdate and for each b you find, see if a isn't 42".  Well, that was hard because 65% of the rows satisifed the b>sysdate and we had to look for a=42 in all of them


Might you want to understand the QUESTIONS asked of the data.....

and how the data is used in those questions...... and what the data looks like

before you look at a clustering factor?  I would.  I surely would.  

Because in the real world, we don't have such simple examples -- they are much more complex -- lots of predicates involved.  It is not this simple.
</b> 

for an interesting article on scientists.  Turns out Don your article on credentials was very useful, your own readers did one of my tricks and used a dictionary.  That indeed was useful.  


But back to the names -- the ones offering "expert" advice that you cannot find resumes for.  They are the only "harmful" ones right -- which ones cannot you find?  Many of them have written books -- that seems to put them in a category that is cool with you (publish and you are OK right -- your 'credentials' page says so)

But come on - a name, one to start, we'll tackle them one by one

And yes, desperate -- you wrote the credentials page, you asked more than once here.  You must have at least ONE person by name?  



I've never said good predictive models cannot be built Don.

I've said your precepts don't hold water.  Period.

And a model has to be built on premises that can actually happen.  "People taking showers or baths" -- sure that can be used perhaps, because it actually happens.

Unbalanced freelists being a performance issue -- nope, not true (even if it could happen)

Migrated/Chained Rows with full table scans -- nope, not true

But tell you what, go ahead -- no one is stopping you.  Build those facts in.


and speaking of credibility.... hmmm.