if you are using oracle9i, you can use the online redefinition package..

or you can

a) export the table 
b) drop and recreate just the table with the new definition
c) import it

or you can


a) prevent dml from happening on this table somehow
b) create table a new with the modified definition
c) insert /*+ append */ into it, index it, etc
d) drop old
e) rename new to old

or you can use steps like this (you'd have to create indexes on t1/t2 to match your local indexes and remember to add global indexes and grants to new before using it)

ops$tkyte@ORA920LAP> CREATE TABLE t
  2  (
  3    pkey       varchar2(5),
  4    data       number
  5  )
  6  PARTITION BY RANGE (pkey) (
  7    PARTITION part1 VALUES LESS THAN ('M'),
  8    PARTITION part2 VALUES LESS THAN (MAXVALUE)
  9  )
 10  ;

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t values( 'A', 5 );

1 row created.

ops$tkyte@ORA920LAP> insert into t values( 'Z', 10 );

1 row created.

ops$tkyte@ORA920LAP> alter table t modify pkey varchar2(10)
  2  /
alter table t modify pkey varchar2(10)
                     *
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not
be changed


ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table new_table
  2  (
  3  pkey       varchar2(15),
  4  data       number
  5  )
  6  PARTITION BY RANGE (pkey) (
  7  PARTITION part1 VALUES LESS THAN ('M'),
  8  PARTITION part2 VALUES LESS THAN (MAXVALUE)
  9  )
 10  ;

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t1 ( pkey varchar2(5), data number );

Table created.

ops$tkyte@ORA920LAP> create table t2 ( pkey varchar2(5), data number );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table t exchange partition part1 with table t1;

Table altered.

ops$tkyte@ORA920LAP> alter table t exchange partition part2 with table t2;

Table altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table t1 modify pkey varchar2(15);

Table altered.

ops$tkyte@ORA920LAP> alter table t2 modify pkey varchar2(15);

Table altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table new_table exchange partition part1 with table t1 without validation;

Table altered.

ops$tkyte@ORA920LAP> alter table new_table exchange partition part2 with table t2 without validation;

Table altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> drop table t;

Table dropped.

ops$tkyte@ORA920LAP> rename new_table to t;

Table renamed.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from t;

PKEY                  DATA
--------------- ----------
A                        5
Z                       10

ops$tkyte@ORA920LAP> desc t;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 PKEY                                         VARCHAR2(15)
 DATA                                         NUMBER

ops$tkyte@ORA920LAP>
 

well, all that means is the data you imported has usercodes other than the ones you set up for.  Consider:


ops$tkyte@ORA920PC> drop table t;
Table dropped.
 
ops$tkyte@ORA920PC> create table t
  2  ( usercode1 varchar2(5),
  3    data      varchar2(10)
  4  )
  5  PARTITION BY LIST (usercode1)
  6      (partition p_acga VALUES('ACGA') ,
  7           partition p_aclj VALUES('ACLJ')  )
  8  /
Table created.
 
ops$tkyte@ORA920PC> !imp userid=/ full=y ignore=y
 
Import: Release 9.2.0.4.0 - Production on Mon Feb 23 10:45:57 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"<b>
IMP-00058: ORACLE error 14400 encountered
ORA-14400: inserted partition key does not map to any partition
IMP-00028: partial import of previous table rolled back: 2 rows rolled back
Import terminated successfully with warnings.</b>

<b>that is your error, it is because:

ops$tkyte@ORA817DEV> select * from t;
 
USERC DATA
----- ----------
ACGA  hello
ACLJ  world
XXXX  ugh

the source table has usercodes we did not anticipate - and your data must also have the same issue.  to get this imported:</b>

 
ops$tkyte@ORA920PC> drop table t;
Table dropped.
 
ops$tkyte@ORA920PC> create table t
  2  ( usercode1 varchar2(5),
  3    data      varchar2(10)
  4  )
  5  PARTITION BY LIST (usercode1)
  6      (partition p_acga VALUES('ACGA') ,
  7           partition p_aclj VALUES('ACLJ') ,<b>
  8           partition the_rest values ( DEFAULT ) )</b>
  9  /
Table created.
 
ops$tkyte@ORA920PC> !imp userid=/ full=y ignore=y
 
Import: Release 9.2.0.4.0 - Production on Mon Feb 23 10:45:58 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"          3 rows imported
Import terminated successfully without warnings.
 

<b>and there you go!  And then you can:


ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select * from t partition(the_rest);
 
USERC DATA
----- ----------
XXXX  ugh
 

to see what the bad rows are</b> 

I used full=y cause I had only one table in there.

You have *bad* data -- I'm 100% on that.  Use the default and you'll see.

  1  select count(*),
  2         count( case when usercode1 not in('ACGA','ACLJ') then 1 end) x,
  3         count( case when usercode1 in('ACGA','ACLJ') then 1 end) y
  4*   from t
ops$tkyte@ORA9IR2> /
 
  COUNT(*)          X          Y
---------- ---------- ----------
         3          0          2


Hmmm, 3 rows -- none are "not in", but only 2 are "in"....

(beware the NULL)


 

tell you what, add:

ops$tkyte@ORA9IR2> create index t_idx1 on t(data) local;
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(data,pkey) global;
 
Index created.
 
ops$tkyte@ORA9IR2> select index_name from user_indexes where table_name = 'T';
 
INDEX_NAME
------------------------------
T_IDX1
T_IDX2


to the original example (right after you create T, before anything else)

and see if you have any indexes when you are done..... (you can even add 'including indexes' to the alter exchange)


You cannot get something for nothing here, you either index those intermediate tables or you have no indexes when you are done. 

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    part_key int,
  4    y   varchar2(30)
  5  )
  6  PARTITION BY RANGE (part_key)
  7  (
  8    PARTITION part1 VALUES LESS THAN (100),
  9    PARTITION part2 VALUES LESS THAN (200)
 10  )
 11  /

Table created.

ops$tkyte%ORA10GR2> insert into t select rownum, object_name from all_objects where rownum < 200;

199 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify part_key number;
alter table t modify part_key number
                     *
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be
changed


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table part1 (part_key int, y varchar2(30) );

Table created.

ops$tkyte%ORA10GR2> create table part2 (part_key int, y varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t exchange partition part1 with table part1 including indexes without validation;

Table altered.

ops$tkyte%ORA10GR2> alter table t exchange partition part2 with table part2 including indexes without validation;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> alter table part1 modify part_key number;

Table altered.

ops$tkyte%ORA10GR2> alter table part2 modify part_key number;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    part_key number,
  4    y   varchar2(30)
  5  )
  6  PARTITION BY RANGE (part_key)
  7  (
  8    PARTITION part1 VALUES LESS THAN (100),
  9    PARTITION part2 VALUES LESS THAN (200)
 10  )
 11  /

Table created.

ops$tkyte%ORA10GR2> alter table t exchange partition part1 with table part1 including indexes without validation;

Table altered.

ops$tkyte%ORA10GR2> alter table t exchange partition part2 with table part2 including indexes without validation;

Table altered.

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 PART_KEY                                          NUMBER
 Y                                                 VARCHAR2(30)

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('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
 11  )
 12  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2
  2  as
  3  select to_date('11-mar-2003','dd-mon-yyyy')+rownum x, rownum y, rownum z
  4  from all_users where rownum <= 4;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select * from t2;
insert into t select * from t2
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


ops$tkyte%ORA10GR2>
<b>one approach to finding "offending rows" is to query the source table for anything greater than or equal to your highest partition key</b>

ops$tkyte%ORA10GR2> select * from t2 where x >= to_date('14-mar-2003','dd-mon-yyyy');

X                  Y          Z
--------- ---------- ----------
14-MAR-03          3          3
15-MAR-03          4          4

ops$tkyte%ORA10GR2>
<b>another is to use dml error logging (10gr2 and above)

</b>
ops$tkyte%ORA10GR2> exec dbms_errlog.create_error_log('T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> insert into t select * from t2 log errors reject limit unlimited;

2 rows created.

ops$tkyte%ORA10GR2> select * from t;

DT                 X Y
--------- ---------- ------------------------------
12-MAR-03          1 1
13-MAR-03          2 2

ops$tkyte%ORA10GR2> select * from err$_t;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
-------------------------------------------------------------------------------
ORA_ERR_ROWID$
-------------------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
-------------------------------------------------------------------------------
DT
-------------------------------------------------------------------------------
X
-------------------------------------------------------------------------------
Y
-------------------------------------------------------------------------------
          14400
ORA-14400: inserted partition key does not map to any partition

I

14-MAR-03
3
3

          14400
ORA-14400: inserted partition key does not map to any partition

I

15-MAR-03
4
4


ops$tkyte%ORA10GR2>
<b>and yes, you can use a "default" partition with maxvalue</b>

ops$tkyte%ORA10GR2> alter table t add PARTITION junk VALUES LESS THAN (MAXVALUE);

Table altered.

ops$tkyte%ORA10GR2> insert into t select * from t2;

4 rows created.

ops$tkyte%ORA11GR2> CREATE TABLE "NEW_T_R_TMP"
  2  ( "R_TMP_SID" ,
  3    "FSID" ,
  4    "BD" ,
  5    FD ,
  6    "PN" ,
  7    "FC" ,
  8    "RT" ,
  9    "CUSR_ID" ,
 10    "UUSR_ID" )
 11  PARTITION BY RANGE (BD)
 12  ( PARTITION S1 VALUES LESS THAN ( TO_DATE('22/12/2005','DD/MM/YYYY')),
 13    PARTITION S2 VALUES LESS THAN ( TO_DATE('07/01/2009','DD/MM/YYYY')),
 14    PARTITION S3 VALUES LESS THAN (TO_DATE('10/01/2009','DD/MM/YYYY')),
 15    PARTITION S4 VALUES LESS THAN (TO_DATE('13/01/2009','DD/MM/YYYY')),
 16    PARTITION S5 VALUES LESS THAN ( MAXVALUE)
 17  )
 18  as
 19  select * from t_r_tmp;

Table created.

ops$tkyte%ORA11GR2> CREATE TABLE "T_R_TMP"
  2  ( "R_TMP_SID" NUMBER(20,0) ,
  3  "FSID" NUMBER(20,0) ,
  4  "BD" DATE ,
  5  FD Date,
  6  "PN" NUMBER(38,0) ,
  7  "FC" VARCHAR2(6) ,
  8  "RT" TIMESTAMP (6) ,
  9  "CUSR_ID" VARCHAR2(8) ,
 10  "UUSR_ID" VARCHAR2(8))
 11  PARTITION BY RANGE (BD)
 12  ( PARTITION S1 VALUES LESS THAN ( TO_DATE('22/12/2005','DD/MM/YYYY')),
 13  PARTITION S2 VALUES LESS THAN ( TO_DATE('07/01/2009','DD/MM/YYYY')),
 14  PARTITION S3 VALUES LESS THAN (TO_DATE('10/01/2009','DD/MM/YYYY')),
 15  PARTITION S4 VALUES LESS THAN (TO_DATE('13/01/2009','DD/MM/YYYY')),
 16  PARTITION S5 VALUES LESS THAN ( MAXVALUE)
 17  )
 18  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t_r_tmp
  2  split partition s2
  3  at ( to_date( '30/01/2006', 'dd/mm/yyyy' ) )
  4  into ( partition new, partition s2 );

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t_r_tmp
  2  merge partitions s1, new into partition new;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t_r_tmp rename partition new to s1;

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T_R_TMP' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T_R_TMP')
--------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T_R_TMP"
   (    "R_TMP_SID" NUMBER(20,0),
        "FSID" NUMBER(20,0),
        "BD" DATE,
        "FD" DATE,
        "PN" NUMBER(38,0),
        "FC" VARCHAR2(6),
        "RT" TIMESTAMP (6),
        "CUSR_ID" VARCHAR2(8),
        "UUSR_ID" VARCHAR2(8)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("BD")
 (PARTITION "S1"  VALUES LESS THAN (TO_DATE(' 2006-01-30 00:00:00', 'SYYYY-MM-DD
 HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "S2"  VALUES LESS THAN (TO_DATE(' 2009-01-07 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "S3"  VALUES LESS THAN (TO_DATE(' 2009-01-10 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "S4"  VALUES LESS THAN (TO_DATE(' 2009-01-13 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ,
 PARTITION "S5"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" )

ops$tkyte%ORA11GR2> 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('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace function get_high_value( p_table_name in varchar2, p_partition_name in varchar2 ) return date
  2  authid current_user
  3  as
  4      l_high_value long;
  5      l_date       date;
  6  begin
  7      select high_value
  8        into l_high_value
  9        from user_tab_partitions
 10       where table_name = p_table_name
 11         and partition_name = p_partition_name;
 12  
 13      if ( l_high_value <> 'MAXVALUE' )
 14      then
 15          execute immediate 'begin :x := ' || l_high_value || '; end;' using OUT l_date;
 16      end if;
 17  
 18      return l_date;
 19  end;
 20  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select table_name, partition_name, get_high_value( table_name, partition_name )
  2    from user_tab_partitions
  3   where table_name = 'T';

TABLE_NAME                     PARTITION_NAME                 GET_HIGH_
------------------------------ ------------------------------ ---------
T                              JUNK
T                              PART1                          13-MAR-03
T                              PART2                          14-MAR-03

ops$tkyte%ORA11GR2> create table organized
  2  as
  3  select x.*
  4    from (select * from stage order by object_name) x
  5  /

Table created.

ops$tkyte%ORA11GR2> create table disorganized
  2  as
  3  select x.*
  4    from (select * from stage order by dbms_random.random) x
  5  /

Table created.

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> create index organized_idx on organized(object_name);
Index created.

ops$tkyte%ORA11GR2> create index disorganized_idx on disorganized(object_name);
Index created.

ops$tkyte%ORA11GR2> begin
  2  dbms_stats.gather_table_stats
  3  ( user, 'ORGANIZED',
  4    estimate_percent => 100,
  5    method_opt=>'for all indexed columns size 254'
  6  );
  7  dbms_stats.gather_table_stats
  8  ( user, 'DISORGANIZED',
  9    estimate_percent => 100,
 10    method_opt=>'for all indexed columns size 254'
 11  );
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> clear screen
ops$tkyte%ORA11GR2> select table_name, blocks, num_rows, 0.05*num_rows, 0.10*num_rows from user_tables
  2  where table_name like '%ORGANIZED' order by 1;

TABLE_NAME                         BLOCKS   NUM_ROWS 0.05*NUM_ROWS 0.10*NUM_ROWS
------------------------------ ---------- ---------- ------------- -------------
DISORGANIZED                         1065      72939       3646.95        7293.9
ORGANIZED                            1066      72939       3646.95        7293.9

ops$tkyte%ORA11GR2> select table_name, index_name, clustering_factor from user_indexes
  2  where table_name like '%ORGANIZED' order by 1;

TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
DISORGANIZED                   DISORGANIZED_IDX                           72877
ORGANIZED                      ORGANIZED_IDX                               1040

<b>so, we have two tables, exactly the same data - just in different orders.  In the
organized table, the rows are sorted by object_name, in the disorganized - they are not.

Notice the clustering factor statistic there - in the organized table it is close to the
number of blocks in the table, in the disorganized - near the number of rows.  the
clustering factor is a measure of how many IO's it would take to read the entire table
via the index.  That metric shows that the organized table index would be used to read
more rows out via the index than the disorganized - because the table rows just happen
to be sorted like the index keys are.  </b>

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from organized where object_name like 'F%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1925627673

-------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CP
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   144 | 13968 |     6   (
|   1 |  TABLE ACCESS BY INDEX ROWID| ORGANIZED     |   144 | 13968 |     6   (
|*  2 |   INDEX RANGE SCAN          | ORGANIZED_IDX |   144 |       |     3   (
-------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME" LIKE 'F%')
       filter("OBJECT_NAME" LIKE 'F%')

<b>that query has a very low cost because the optimizer thinks it will do about 3 IO's
against the index and 3 more against the table - which is just about right.  My table
gets about 70 rows per block - so it would take three table blocks to read the rows out
- and the index would have to read a root block, branch block and leaf block to get the
rowids for the 144 rows</b>

ops$tkyte%ORA11GR2> select * from disorganized where object_name like 'F%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3767053355

-------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   144 | 13968 |   148
|   1 |  TABLE ACCESS BY INDEX ROWID| DISORGANIZED     |   144 | 13968 |   148
|*  2 |   INDEX RANGE SCAN          | DISORGANIZED_IDX |   144 |       |     3
-------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME" LIKE 'F%')
       filter("OBJECT_NAME" LIKE 'F%')

<b>but look at the cost of the same query against the other table.  It is 148.  It would
take 3 IO's against the index as before - but somewhere around 144 IO's against the
table since the data is so spread out!  so much higher cost.  But both were using
indexes so far.

remember this is a 72,000 row table.  1% of that is 720.</b>


ops$tkyte%ORA11GR2> select * from organized where object_name like 'A%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1925627673

-------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CP
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1826 |   172K|    39   (
|   1 |  TABLE ACCESS BY INDEX ROWID| ORGANIZED     |  1826 |   172K|    39   (
|*  2 |   INDEX RANGE SCAN          | ORGANIZED_IDX |  1826 |       |    12   (
-------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME" LIKE 'A%')
       filter("OBJECT_NAME" LIKE 'A%')

<b>if we go after 1,826 rows - this query on this table will still use an index - the
cost is still very low since the 1,826 rows we want are all next to each other...

but...</b>

ops$tkyte%ORA11GR2> select * from disorganized where object_name like 'A%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2727546897

-------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |  1826 |   172K|   291   (1)| 00:00:0
|*  1 |  TABLE ACCESS FULL| DISORGANIZED |  1826 |   172K|   291   (1)| 00:00:0
-------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME" LIKE 'A%')

<b>getting the same 1,826 rows will not use an index here.  the full scan cost is 291 -
way below the cost of getting 1,826 using single block IO's all over the place.

in fact, we can see that if we were to get around 290 rows out of this table - we would
stop using an index.  that is about 0.4% of the rows in the table!!!!

we would not use this index to retrieve less than 0.5% of the table - no where near
10%!!!!
</b>