only thing I can think is that by partitioning by usedsegid -- you accidently or by design got all of the 'NE3' records nicely clustered together whereas before they where not.

To show you how this would affect it, I created your tables:

CREATE TABLE ADS_AlarmObject_nonpart
(
    AlarmID                 NUMBER(10)      default 1 NOT NULL,
    NEName                  VARCHAR2(60)    NOT NULL,
....
    UsedSegID        NUMBER(10)    NOT NULL
)
/
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
CREATE TABLE ADS_AlarmObject_part
(
    AlarmID                 NUMBER(10)      default 1 NOT NULL,
    NEName                  VARCHAR2(60)    NOT NULL,
....
    UsedSegID        NUMBER(10)    NOT NULL
)
    PARTITION BY RANGE (UsedSegID)
    (
        PARTITION SegA001 VALUES LESS THAN (1),
        PARTITION SegA002 VALUES LESS THAN (2),
        PARTITION SegA003 VALUES LESS THAN (3),
        PARTITION SegA004 VALUES LESS THAN (4),
        PARTITION SegA005 VALUES LESS THAN (5),
        PARTITION SegA006 VALUES LESS THAN (6),
        PARTITION SegA007 VALUES LESS THAN (7),
        PARTITION SegA008 VALUES LESS THAN (8),
        PARTITION SegA009 VALUES LESS THAN (9),
        PARTITION SegA010 VALUES LESS THAN (10)
    )
    ENABLE ROW MOVEMENT
/
                                                                                                                                                                                                                                             
insert /*+ APPEND */ into ads_alarmobject_nonpart( usedsegid, nename )
select mod(rownum,10), 'NE' || mod(rownum,10)
  from big_table.big_table
 where rownum <= 1000000;
commit;
insert /*+ APPEND */ into ads_alarmobject_part (usedsegid, nename )
select usedsegid, nename
  from ads_alarmobject_nonpart;
commit;
                                                                                                                                                                                                                                             
create index ads_ao_nn_part on ads_alarmobject_part(nename);
create index ads_os_nn_nonpart on ads_alarmobject_nonpart(nename);
                                                                                                                                                                                                                                             
ops$tkyte@ORA9IR2> update ads_alarmobject_part set intprocstatid = 5 where nename = 'NE3' and rownum <= 11111;
 
11111 rows updated.
 
ops$tkyte@ORA9IR2> update ads_alarmobject_nonpart set intprocstatid = 5 where nename = 'NE3' and rownum <= 11111;
 
11111 rows updated.

exec dbms_stats.gather_table_stats( user, 'ADS_ALARMOBJECT_NONPART', cascade=>true );
exec dbms_stats.gather_table_stats( user, 'ADS_ALARMOBJECT_PART', cascade=>true );


<b>but I defaulted everything to be a 1, sysdate or rpad('*',40,'*') and left the primary key off (not relevant to the example).

Note, how I just happened to have put the NE3 data together, it is all in sega004</b>


Now, I run your queries -- once no tracing, once with, results:



SELECT * FROM ADS_ALARMOBJECT_part WHERE NENAME='NE3' AND INTPROCSTATID=5
                                                                                                                                                       
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      742      0.65       0.69       6486       7952          0       11111
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      744      0.65       0.69       6486       7952          0       11111
                                                                                                                                                       
Rows     Row Source Operation
-------  ---------------------------------------------------
  11111  TABLE ACCESS BY GLOBAL INDEX ROWID ADS_ALARMOBJECT_PART PARTITION: ROW LOCATION ROW LOCATION (cr=7952 r=6486 w=0 time=624270 us)
 100000   INDEX RANGE SCAN ADS_AO_NN_PART (cr=1008 r=237 w=0 time=134169 us)(object id 38599)
********************************************************************************
SELECT * FROM ADS_ALARMOBJECT_nonpart WHERE NENAME='NE3' AND INTPROCSTATID=5
                                                                                                                                                       
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      742      2.93       3.02      61489      64090          0       11111
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      744      2.93       3.02      61489      64090          0       11111
                                                                                                                                                       
Rows     Row Source Operation
-------  ---------------------------------------------------
  11111  TABLE ACCESS BY INDEX ROWID OBJ#(38587) (cr=64090 r=61489 w=0 time=2936385 us)
 100000   INDEX RANGE SCAN OBJ#(38600) (cr=952 r=211 w=0 time=133005 us)(object id 38600)


<b>and these results are directly due to the implicit relationship between the nename='NE3' and the usedsegid I generated in my test data, could that be the case here?  Your partitioned table is accidently "very well clustered" and your non partitioned table is worst case clustered??  (I'm guessing YES)

Also, you know that index is the wrong index, it should be a concatenated index on both columns.  when you see 1000000 rows flowing out of an index set and 11111 rows flowing out of the table access step -- that means you want to add a column or two to the end in general</b>

Funny, I was just rewriting this section in Expert One on One Oracle over the weekend, here is the original explanation of clustering factor to go with the example -- to further explain "why"

<quote>

There are factors of course that change these calculations (aren't there always?).  Suppose you have a table such that the rows have a primary key populated by a sequence.  As data is added to the table, rows with sequential sequence numbers are in general "next" to each other.  They table is naturally clustered in order by the primary key (since the data is added in more or less that order).  It will not be strictly clustered in order by the key of course (we would have to use an IOT to achieve that) - but in general rows with primary keys that are close in value will be "close" in physical proximity.  Now when you issue the query: "select * from T where primary_key between :x and :y" - it will be the case that the rows you want are typically co-located on the same blocks.  Here an index range scan may be useful even if it accesses a large percentage of rows simply because the database blocks we need to read and re-read will most likely be cached - since the data is co-located.  On the other hand, if the rows are not co-located using that same index may be disastrous for performance.  A small demonstration will drive this fact home.  We'll start with a table that is pretty much ordered by its primary key:

tkyte@TKYTE816> create table colocated ( x int, y varchar2(2000) ) pctfree 0;
Table created.

tkyte@TKYTE816> begin
  2    for i in 1 .. 100000
  3    loop
  4           insert into colocated values ( i, rpad(dbms_random.random,75,'*') );
  5    end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.
 
tkyte@TKYTE816> alter table colocated    
  2  add constraint colocated_pk primary key(x);
Table altered.

This is a table fitting the description we laid out above - about 100 rows/block in my 8k database.  Here there is a very good chance that the rows with x = 1, 2, 3 are on the same block.  Now, we'll take this table and purposely "disorganize" it.  In the colocated table above, we created the Y column with a leading random number - we'll use that fact to "disorganize" the data - so it will definitely not be ordered by primary key anymore:

tkyte@TKYTE816> create table disorganized nologging pctfree 0
  2  as
  3  select x, y from colocated ORDER BY y
  4  /
Table created.
 
tkyte@TKYTE816> alter table disorganized 
  2 add constraint disorganized_pk primary key(x);
Table altered.

Arguably these are the same table - it is a relational database, physical organization plays no bearing on how things work (at least that's what they teach in theoretical database courses).  In fact, the performance characteristics of these two tables are night and day different given the same exact query:

tkyte@TKYTE816> select * from COLOCATED where x between 20000 and 40000;
20001 rows selected.

Elapsed: 00:00:01.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED'
   2    1     INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2909  consistent gets
        258  physical reads
          0  redo size
    1991367  bytes sent via SQL*Net to client
     148387  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed

tkyte@TKYTE816> select * from DISORGANIZED where x between 20000 and 40000;
20001 rows selected.

Elapsed: 00:00:23.34

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DISORGANIZED'
   2    1     INDEX (RANGE SCAN) OF 'DISORGANIZED_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      21361  consistent gets
       1684  physical reads
          0  redo size
    1991367  bytes sent via SQL*Net to client
     148387  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed
 
That is pretty incredible (I think so).  What a difference physical data layout can make.  To summarize the results:

    Elapsed Time    Logical I/O
Colocated    1.02 seconds    2,909
Disorganized    23.34 seconds    21,361

In my database using an 8k block size - these tables had 1,088 total blocks apiece.  The query against disorganized table bears out the simple math we did above - we did 20,000 plus logical I/Os.  We processed each and every block 20 times!!  On the other hand, the physically colocated data took the logical I/Os way down.  Here is the perfect example of why rules of thumb are so hard to provide - in one case, using the index works great, in the other - it stinks.  Consider this the next time you dump data from your development system and load it into production - this may very well be part of the answer to the question that typically comes up "why is it running differently on this machine - they are identical" (they are not identical).  

</quote> 

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    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> insert into t select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;

58 rows created.

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

Index created.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'T_IDX';

STATUS
--------
VALID

ops$tkyte%ORA10GR2> alter table t truncate partition part1 update global indexes;

Table truncated.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'T_IDX';

STATUS
--------
VALID

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

Table truncated.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'T_IDX';

STATUS
--------
UNUSABLE