q1) it doesn't matter "how", only that it "does" at the end of the day.

Since version 8.0 with deferrable constraints, we've only needed AN INDEX to enforce uniqueness.  Not a unique index, just an index.

q2) not relevant to us.  only relevant that "it works".  the internal mechanics, not known to us.  all we need know is a primary key/unique constraint will

a) look for the columns to be on the leading edge of any index, if found, use it, consider:


ops$tkyte@ORA920PC> create table t ( x int, y int );
Table created.
 
ops$tkyte@ORA920PC> create index t_idx on t(x,y);
Index created.
 
ops$tkyte@ORA920PC> alter table t add constraint t_pk primary key(x);
Table altered.
 
ops$tkyte@ORA920PC> select index_name from user_indexes;
 
INDEX_NAME
------------------------------
T_IDX
 
ops$tkyte@ORA920PC> drop index t_idx;
drop index t_idx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

b) if not found and constraint is not deferrable, create unique index by default
c) if not found and constraint is deferrable, create non-unique index by default

q3) nothing horribly measurable really.  It would be rare for it to affect a plan, in light of statistics, the optimizer will understand about how many rows (1) would come back based on "where pk = value".  That and remember -- indexes are the tuning tools here.  If you needed a unique index for performance reasons, then you most certainly should not have created a non-unique one.  


 

1) if you heavily delete -- and never ever insert or update, this MIGHT hold water - if we actually always left deleted leaf rows there (we don't).

consider this table:

drop table t;
create table t ( x int, y char(20) default 'x' );
drop sequence s;
create sequence s;
alter table t add constraint t_pk primary key(x);
                                                                                                                    

Ok, I set up one pro*c program doing nothing but this:

void process( int rows )
{
int  i;
                                                                                                                    
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    for( i = 0; i < rows; i++ )
    {
        exec sql insert into t (x) values ( s.nextval );
        exec sql commit;
        if ( i%1000 == 0 )
            printf( "inserted row %d\n", i+1 );
    }
    exit(0);
}


and another to do this:

void process( int rows )
{
int  i;
                                                                                                                    
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    for( i = 0; i < rows; i++ )
    {
        exec sql delete from t where x = (select min(x) from t);
        exec sql commit;
        if ( i%1000 == 0 )
            printf( "deleted row %d\n", i+1 );
    }
    exit(0);
}


Now I let it run and run (started the insert ahead of the delete) and run and run (until i got bored).  After a couple hundred thousand inserts and a couple hundred thousand deletes -- I ctl-c'ed each one.


ops$tkyte@ORA9IR2> analyze index t_pk validate structure;
 
Index analyzed.
 
ops$tkyte@ORA9IR2> select del_lf_rows, lf_rows from index_stats;
 
DEL_LF_ROWS    LF_ROWS
----------- ----------
         15       2687
 

and it really really really depends on how you use the index and how the index is hit if a large DEL_LF_ROWS would affect your IO at all.

What if you 

a) remove old entries (low sequence numbers)
b) were always interested in new entries (high sequence numbers) 
c) so therefore you query:
   select /*+ first_rows */ * from t order by X desc;

and range scanned that way.  well, the high end of the index is going to be very packed -- and you never "page down" into the low end (you show the user the N newest entries and they page down and page down -- you probably never get to the "low end" of the index where the whitespace might be

2) yup, exactly.

3) If I were to observe over time that DEL_LF_ROWS was always *increasing* -- note "over time" "increasing" -- i would consider that a thing to take interest in.  

If "over time", it increased to say 50,000 and then stayed there, fluctuating up and down a couple of thousand even -- i would do nothing -- even if the number of leaf rows was 50,000 as well.  why ?  well, obviously this index wants to be that fat, with that much white space and <b>no matter how many times I put it on a diet...... it is going to get back to 50,000 deleted and 50,000 not</b>




Just apply some thought to what you are doing day to day, think about "why", think about what you are trying to accomplish.  Have a metric or two to validate it.  It is always nice to be to show what really happens. 

this is going to be a "hindsight is 20/20" sort of thing.

In order to determine "whats different", we would need to know "what was normal".

Unless you have tkprofs that profile these batch jobs, that show what they used to do -- it'll be hard to know.

Ok, so how would you see this?  I rigged up an example:

drop table t;
create table t ( x int, mod_x int, y char(20) default 'x' );
drop sequence s;
create sequence s;
alter table t add constraint t_pk primary key(x,mod_x);


we are going to populate this table like this:


void process( int rows )
{
int  i;
                                                                                                
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    for( i = 0; i < rows; i++ )
    {
        exec sql insert into t (x,mod_x) values 
                 ( s.nextval, mod(s.nextval,100) );
        exec sql commit;
        if ( i%1000 == 0 )
            printf( "inserted row %d\n", i+1 );
    }
    exit(0);
}



so, X will be an increasing sequence and mod_x will run 0..99, 0..99, 0..99 and so on.


The carefully crafted delete will be:

void logon()
{
EXEC SQL BEGIN DECLARE SECTION;
varchar   oracleid[31];
EXEC SQL END DECLARE SECTION;
                                                                                            
    strcpy( (char *)oracleid.arr, "/" );
    oracleid.len = strlen((char *)oracleid.arr);
                                                                                            
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
                                                                                            
    EXEC SQL CONNECT :oracleid;
    exec sql alter session set events '10046 trace name context forever, level 12';
}
                                                                                            
                                                                                            
void process( int rows )
{
int  i;
                                                                                            
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
    for( i = 1; i < rows; i++ )
    {
        exec sql
            delete from t where (x,mod_x) =
            (select x,mod_x
               from (select x, mod_x from t order by x, mod_x)
              where rownum = 1
                and mod_x <> 1 );
        exec sql commit;
        if ( i%1000 == 0 )
            printf( "deleted row %d\n", i+1 );
        if ( i%10000 == 0 )
        {
            exec sql commit work release;
            logon();
        }
    }
}


I did this in order to get a plan like this:

ops$tkyte@ORA9IR2> explain plan for
  2              delete from t where (x,mod_x) =
            (select x,mod_x
               from (select x, mod_x from t order by x, mod_x)
              where rownum = 1
                and mod_x <> 1 );
  3    4    5    6
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |       |       |       |
|   1 |  DELETE              | T           |       |       |       |
|*  2 |   INDEX UNIQUE SCAN  | T_PK        |       |       |       |
|*  3 |    COUNT STOPKEY     |             |       |       |       |
|   4 |     VIEW             |             |       |       |       |
|*  5 |      INDEX FULL SCAN | T_PK        |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(("T"."X","T"."MOD_X")= (SELECT
              "from$_subquery$_002"."X","from$_subquery$_002"."MOD_X" FROM
              (SELECT /*+ */ "T"."X" "X","T"."MOD_X" "MOD_X" FROM "T" "T" WHERE
 
 
              "T"."MOD_X"<>1) "from$_subquery$_002" WHERE ROWNUM=1))
   3 - filter(ROWNUM=1)
   5 - filter("T"."MOD_X"<>1)
 
Note: rule based optimization
 
23 rows selected.




<b>so, this delete will read the index -- scanning to find the first X,MOD_X such that MOD_X is not 1 -- and then delete it.  We are leaving behind in this index structure all of the x,mod_x's with mod_x = 1 (not a clean sweep as before).

Also, I'm logging out every 10k rows and logging back in and turning trace on again.

Here are the first 4 tkprofs:
</b>


delete  from t
where
 (x,mod_x)=(select x ,mod_x  from (select x ,mod_x  from t  order by x,mod_x )
   where (rownum=1 and mod_x<>1))
                                                                                                                                                     
                                                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      3.34       3.74          0     246225      40457       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      3.34       3.74          0     246225      40457       10000
                                                                                                                                                     
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 158
                                                                                                                                                     
Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  DELETE  (cr=20008 r=0 w=0 time=1411426 us)
  10000   INDEX UNIQUE SCAN OBJ#(30278) (cr=20000 r=0 w=0 time=69076 us)(object id 30278)
********************************************************************************
delete  from t
where
 (x,mod_x)=(select x ,mod_x  from (select x ,mod_x  from t  order by x,mod_x )
   where (rownum=1 and mod_x<>1))
                                                                                                                                                     
                                                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      4.71       5.19          0     681712      40479       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      4.71       5.19          0     681712      40479       10000
                                                                                                                                                     
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 158
                                                                                                                                                     
Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  DELETE  (cr=20011 r=0 w=0 time=1443698 us)
  10000   INDEX UNIQUE SCAN OBJ#(30278) (cr=20000 r=0 w=0 time=126472 us)(object id 30278)
********************************************************************************
delete  from t
where
 (x,mod_x)=(select x ,mod_x  from (select x ,mod_x  from t  order by x,mod_x )
   where (rownum=1 and mod_x<>1))
                                                                                                                                                     
                                                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      6.36       6.38          0    1130588      40484       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      6.36       6.38          0    1130588      40484       10000
                                                                                                                                                     
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 158
                                                                                                                                                     
Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  DELETE  (cr=20004 r=0 w=0 time=1460916 us)
  10000   INDEX UNIQUE SCAN OBJ#(30278) (cr=20000 r=0 w=0 time=91020 us)(object id 30278)
********************************************************************************
delete  from t
where
 (x,mod_x)=(select x ,mod_x  from (select x ,mod_x  from t  order by x,mod_x )
   where (rownum=1 and mod_x<>1))
                                                                                                                                                     
                                                                                                                                                     
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      7.54       8.39          0    1579542      40521       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      7.54       8.39          0    1579542      40521       10000
                                                                                                                                                     
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 158
                                                                                                                                                     
Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  DELETE  (cr=20016 r=0 w=0 time=1530803 us)
  10000   INDEX UNIQUE SCAN OBJ#(30278) (cr=20000 r=0 w=0 time=117590 us)(object id 30278)



See the LIOs go up from 

  246k
  681k
1,130k
1,579k

and see the increasing CPU times.  Now I rebuilt the index and restarted the "batches":

delete  from t
where
 (x,mod_x)=(select x ,mod_x  from (select x ,mod_x  from t  order by x,mod_x )
   where (rownum=1 and mod_x<>1))
                                                                                             
                                                                                             
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      4.19       5.26         15     192185      40502       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      4.19       5.27         15     192185      40502       10000


<b>and it went back down, funny thing is -- the "classic method for finding fragmented (whatever that means) indexes", would totally miss this</b>

ops$tkyte@ORA9IR2> analyze index t_pk validate structure;
Index analyzed.
 
ops$tkyte@ORA9IR2> select lf_rows, del_lf_rows from index_stats;
 
   LF_ROWS DEL_LF_ROWS
---------- -----------
      6505         194

<b>not many del_lf_rows here eh?</b>

So, ask yourself, do we have any degenerate cases like this -- a sweeping left to right index that leaves stragglers behind?


Now, to be fair, the other example above would show increased LIO's over time -- at a much much much much slower rate however (it took over 10 times the work to get the LIO's as high as we saw jump the first iteration here) -- but again, the "classic approach" (look for del_lf_rows) would have been meaningless!  utterly and totally.



So, look for slow by slow code that scans the left hand side, to find the first row to process, but leaves stragglers behind.   

and  That was then, this is now . 

but there are SOOO many things wrong in that article, it is hard to know where even to begin or what the author meant by "out of balance"

the second is:

I believe he was pointing out an odd "product issue" with 9i there. Not something that was supposed to happen.



ops$tkyte%ORA10GR2> create table t ( id number, value varchar2(10) );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(id);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2     for i in 1 .. 10000
  3     loop
  4        insert into t values (i,'xxxxx' );
  5        commit;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> analyze index t_idx validate structure;

Index analyzed.

ops$tkyte%ORA10GR2> select lf_blks, pct_used from index_stats;

                %
   LF_BLKS   Used
---------- ------
        19   94.0
 

... I'm very confused because i think it's a bad rule. ...

You are not confused, you are correct.


Not only will that rule flag indexes as being in need of a rebuild when they aren't, they won't catch those that are!

see my example above which includes:

ops$tkyte@ORA9IR2> select lf_rows, del_lf_rows from index_stats;
 
   LF_ROWS DEL_LF_ROWS
---------- -----------
      6505         194

(that index was in need of a coalesce/rebuild) 

ops$tkyte%ORA10GR2> create table case_loads( id number(10) primary key );

Table created.

ops$tkyte%ORA10GR2> insert into case_loads
  2  select level from dual connect by level <= 384
  3  /

384 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE case_data
  2  (
  3    ID                NUMBER(10),
  4    FK_CASELOAD_ID    NUMBER(10),
  5    CASE_NUMBER       NUMBER(10),
  6    ASSIGNED_DATE     DATE,
  7    STAFF_ID          NUMBER(10),
  8    AGENCY_ID         NUMBER(10),
  9    TRANSFERRED_DATE  DATE,
 10    CONSTRAINT PK_CASELOAD PRIMARY KEY (ID),
 11    CONSTRAINT FK_CASELOAD_CASE_LOADS
 12   FOREIGN KEY (FK_CASELOAD_ID)
 13   REFERENCES CASE_LOADS (ID)
 14  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into case_data
  2  select level, mod(level,384)+1, level, sysdate, level, level, sysdate
  3    from dual connect by level <= 502954
  4  /

502954 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE INDEX IDX_CASELOAD_CASELOADID ON case_data (FK_CASELOAD_ID);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'CASE_DATA' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select a.num_rows, a.blocks, b.clustering_factor,
  2         (select count(distinct dbms_rowid.rowid_block_number(rowid)) from case_data where fk_caseload_id=5) cnt_blocks
  3    from user_tables a, user_indexes b
  4   where a.table_name = 'CASE_DATA'
  5     and b.index_name = 'IDX_CASELOAD_CASELOADID'
  6  /

  NUM_ROWS     BLOCKS CLUSTERING_FACTOR CNT_BLOCKS
---------- ---------- ----------------- ----------
    501422       3142            502954       1310

<b>the clustering factor is near the number of rows in the table - large range scans will not be efficient against this data, it will have to visit a single block per row retrieved - we can see that the "5" data is on 1310 separate blocks - we'll have to use single block IO 1310 times to retrieve this data</b>


ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select * from case_data where fk_caseload_id=5;

1310 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2422329801

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |  1302 | 50778 |  1006   (5)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| CASE_DATA |  1302 | 50778 |  1006   (5)| 00:00:05 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FK_CASELOAD_ID"=5)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3236  consistent gets
          0  physical reads
          0  redo size
      48224  bytes sent via SQL*Net to client
       1341  bytes received via SQL*Net from client
         89  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1310  rows processed

ops$tkyte%ORA10GR2> select /*+ index( case_data IDX_CASELOAD_CASELOADID ) */ * from case_data where fk_caseload_id=5;

1310 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1867324150

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |  1302 | 50778 |  1315   (1)| 00:00:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CASE_DATA               |  1302 | 50778 |  1315   (1)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | IDX_CASELOAD_CASELOADID |  1306 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FK_CASELOAD_ID"=5)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1403  consistent gets
          0  physical reads
          0  redo size
      48224  bytes sent via SQL*Net to client
       1341  bytes received via SQL*Net from client
         89  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1310  rows processed

ops$tkyte%ORA10GR2> set autotrace off

<b>the cost of the full scan using multi-block reads is less than the cost of tons of single block reads in this case, if the data were organized on disk differently:</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t as select * from case_data;

Table created.

ops$tkyte%ORA10GR2> truncate table case_data;

Table truncated.

ops$tkyte%ORA10GR2> insert /*+ append */ into case_data select * from t order by fk_caseload_id;

502954 rows created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'CASE_DATA', cascade=> true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select a.num_rows, a.blocks, b.clustering_factor,
  2         (select count(distinct dbms_rowid.rowid_block_number(rowid)) from case_data where fk_caseload_id=5) cnt_blocks
  3    from user_tables a, user_indexes b
  4   where a.table_name = 'CASE_DATA'
  5     and b.index_name = 'IDX_CASELOAD_CASELOADID'
  6  /

  NUM_ROWS     BLOCKS CLUSTERING_FACTOR CNT_BLOCKS
---------- ---------- ----------------- ----------
    504905       3142              3455          9

<b>now the clustering factor is near the number of blocks in the table, large range scans are efficient - we can see that all of our "5" data is on 9 blocks...</b>


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select * from case_data where fk_caseload_id=5;

1310 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1867324150

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |  1391 | 54249 |    16   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CASE_DATA               |  1391 | 54249 |    16   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_CASELOAD_CASELOADID |  1391 |       |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FK_CASELOAD_ID"=5)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        192  consistent gets
          0  physical reads
          0  redo size
      48224  bytes sent via SQL*Net to client
       1341  bytes received via SQL*Net from client
         89  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1310  rows processed

<b>and the index is used naturally</b>

ops$tkyte%ORA10GR2> create table case_loads( id number(10) primary key );

Table created.

ops$tkyte%ORA10GR2> insert into case_loads
  2  select level from dual connect by level <= 384
  3  /

384 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create cluster case_data_cluster ( fk_caseload_id number(10) ) size 8k;

Cluster created.

ops$tkyte%ORA10GR2> create index case_data_cluster_idx on cluster case_data_cluster;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE case_data
  2  (
  3    ID                NUMBER(10),
  4    FK_CASELOAD_ID    NUMBER(10),
  5    CASE_NUMBER       NUMBER(10),
  6    ASSIGNED_DATE     DATE,
  7    STAFF_ID          NUMBER(10),
  8    AGENCY_ID         NUMBER(10),
  9    TRANSFERRED_DATE  DATE,
 10    CONSTRAINT PK_CASELOAD PRIMARY KEY (ID),
 11    CONSTRAINT FK_CASELOAD_CASE_LOADS
 12   FOREIGN KEY (FK_CASELOAD_ID)
 13   REFERENCES CASE_LOADS (ID)
 14  )
 15  cluster case_data_cluster(fk_caseload_id);

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into case_data
  2  select level, mod(level,384)+1, level, sysdate, level, level, sysdate
  3    from dual connect by level <= 502954
  4  /

502954 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'CASE_DATA', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>


ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select * from case_data where fk_caseload_id=5;

1310 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3427366092

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |  1303 | 50817 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS CLUSTER| CASE_DATA             |  1303 | 50817 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN  | CASE_DATA_CLUSTER_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FK_CASELOAD_ID"=5)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         97  consistent gets
          0  physical reads
          0  redo size
      48224  bytes sent via SQL*Net to client
       1341  bytes received via SQL*Net from client
         89  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1310  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> insert into t (x) select rownum from all_users;

58 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(distinct dbms_rowid.rowid_block_number(rowid) ) from t;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  1

ops$tkyte%ORA10GR2> alter table t move;

Table altered.

ops$tkyte%ORA10GR2> select count(distinct dbms_rowid.rowid_block_number(rowid) ) from t;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  1

ops$tkyte%ORA10GR2> update t set y = rpad('*',4000,'*');

58 rows updated.

<b> obviously, these do not fit on a  block anymore! </b>
ops$tkyte%ORA10GR2> select count(distinct dbms_rowid.rowid_block_number(rowid) ) from t;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                  1

<b>we are only counting the head rowid piece, when we reorg the table, we "unmigrate" the rows as well</b>

ops$tkyte%ORA10GR2> alter table t move;

Table altered.

ops$tkyte%ORA10GR2> select count(distinct dbms_rowid.rowid_block_number(rowid) ) from t;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
                                                 58

<b>it takes 58 blocks to hold those rows, not just one, when Y is filled with data</b>

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2005','dd-mon-yyyy')),
 10    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2006','dd-mon-yyyy')),
 11    PARTITION part3 VALUES LESS THAN (to_date('01-jan-2007','dd-mon-yyyy')),
 12    PARTITION part4 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy'))
 13  )
 14  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select add_months( to_date( '31-dec-2004','dd-mon-yyyy'), mod(rownum,4)*12 ), user_id, username
  3    from all_users;

45 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 'part1', count(*) from t partition(part1) union all
  2  select 'part2', count(*) from t partition(part2) union all
  3  select 'part3', count(*) from t partition(part3) union all
  4  select 'part4', count(*) from t partition(part4);

'PART   COUNT(*)
----- ----------
part1         11
part2         12
part3         11
part4         11

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) global;

Index created.

ops$tkyte%ORA10GR2> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX                          VALID

ops$tkyte%ORA10GR2> alter table t drop partition part1;

Table altered.

ops$tkyte%ORA10GR2> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX                          UNUSABLE

ops$tkyte%ORA10GR2> alter table t drop partition part2;

Table altered.

ops$tkyte%ORA10GR2> alter table t drop partition part3 update global indexes;

Table altered.

ops$tkyte%ORA10GR2> select index_name, status from user_indexes where table_name = 'T';

INDEX_NAME                     STATUS
------------------------------ --------
T_IDX                          UNUSABLE

ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t
ops$tkyte%ORA11GR2> as
ops$tkyte%ORA11GR2> select case when mod(rownum,2) = 0 then 0 else rownum end id,
ops$tkyte%ORA11GR2>        all_objects.*
ops$tkyte%ORA11GR2>   from all_objects
ops$tkyte%ORA11GR2> /
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user, 'T' );
ops$tkyte%ORA11GR2> create index t_idx on t(id);
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x number;
ops$tkyte%ORA11GR2> variable y varchar2(30)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :x := 1; :y := 'SCOTT'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select * from t where id = :x and owner = :y;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   100 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"=:Y)
   2 - access("ID"=TO_NUMBER(:X))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1239  bytes sent via SQL*Net to client
        409  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%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> exec :x := 0; :y := 'SCOTT'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select * from t where id = :x and owner = :y;


Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   100 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   100 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"=:Y)
   2 - access("ID"=TO_NUMBER(:X))


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

ops$tkyte%ORA11GR2> set autotrace off



select * from t where id = :x and owner = :y

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       1143          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0       1143          0           5

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         5          5          5  TABLE ACCESS BY INDEX ROWID T (cr=1143 pr=0 pw=0 time=9076 us cost=2 size=100 card=1)
     36436      36436      36436   INDEX RANGE SCAN T_IDX (cr=69 pr=0 pw=0 time=23315 us cost=1 size=0 card=2)(object id 114237)

ops$tkyte%ORA11GR2> drop index t_idx;
ops$tkyte%ORA11GR2> create index t_idx on t(id,owner);
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> variable x number;
ops$tkyte%ORA11GR2> variable y varchar2(30)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :x := 1; :y := 'SCOTT'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select * from t where id = :x and owner = :y;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   200 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   200 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:X) AND "OWNER"=:Y)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1239  bytes sent via SQL*Net to client
        409  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%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> exec :x := 0; :y := 'SCOTT'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select * from t where id = :x and owner = :y;


Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   200 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   200 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=TO_NUMBER(:X) AND "OWNER"=:Y)


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

ops$tkyte%ORA11GR2> set autotrace off