no bug, you haven't any GLOBAL stats for the partitioned table, you only have LOCAL stats at the partition level.


ops$tkyte@ORA9IR2> l
  1  select partition_name, column_name, count(*) from user_PART_histograms where table_name = 'T'
  2* group by partition_name, column_name
ops$tkyte@ORA9IR2> /
 
PARTITION_NAME                 COLUMN_NAM   COUNT(*)
------------------------------ ---------- ----------
P1                             I                   9
P1                             J                   9
P1                             K                   9
 

not sure where you are going with the first paragraph.  it sounds like you are trying to pick up the conversation -- but not sure where it left off really?!? 

the answer to 

"Would they do a FTS i.e. full partition scan of all partitions?"

is -- well, it totally depends.  select count(*) would be looking for an index on a non-nullable column to fast full scan.  the select from predicate would be looking at everything available and would use whatever plan made sense (insufficient data to comment on what type of plan it might use, could be an index range scan of any column for all I know)



even the specific question doesn't provide sufficient data -- it could look for a non-partitioned index, it could use global indexes, it could use a local index.  but it'll be able to do any of them if it likes

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    temp_date  date,
  4    x          int,
  5    y int,
  6    constraint t_pk primary key(temp_date) using index LOCAL
  7  )
  8  PARTITION BY RANGE (temp_date)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /
 
Table created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows=>1000000, numblks=>10000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*) from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (ALL)
   3    2       INDEX (FAST FULL SCAN) OF 'T_PK' (UNIQUE) (Cost=4 Card=1000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


If you always access a SINGLE partition and that is clear in the predicate (eg: that constraint is ALWAYS in the predicate), then you may well not need global indexes.
 

ops$tkyte@ORA10GR2> CREATE TABLE t1
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  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> CREATE TABLE t2
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  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> CREATE TABLE t3
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  /

Table created.

ops$tkyte@ORA10GR2> insert into t1 select to_date('12-mar-2003','dd-mon-yyyy')+mod(rownum,4), rownum, 'T1' from all_users;

34 rows created.

ops$tkyte@ORA10GR2> insert into t2 select to_date('12-mar-2003','dd-mon-yyyy')+mod(rownum,4), rownum, 'T2' from all_users;

34 rows created.

ops$tkyte@ORA10GR2> select y, count(*) from t1 group by y;

Y                           COUNT(*)
------------------------- ----------
T1                                34

ops$tkyte@ORA10GR2> select y, count(*) from t2 group by y;

Y                           COUNT(*)
------------------------- ----------
T2                                34

ops$tkyte@ORA10GR2> alter table t1 exchange partition part1 with table t3;

Table altered.

ops$tkyte@ORA10GR2> alter table t2 exchange partition part1 with table t3;

Table altered.

ops$tkyte@ORA10GR2> alter table t1 exchange partition part1 with table t3;

Table altered.

ops$tkyte@ORA10GR2> select y, count(*) from t1 group by y;

Y                           COUNT(*)
------------------------- ----------
T2                                 8
T1                                26

ops$tkyte@ORA10GR2> select y, count(*) from t2 group by y;

Y                           COUNT(*)
------------------------- ----------
T2                                26
T1                                 8

 

You can have global indexes maintained during the exchange.

Yes - the exchange will obviously take longer as we are maintaining the index.

But - yes, there is no downtime as far as the end users querying the data are concerned since the index doesn't go invalid (so who cares if it takes longer... it is not unavailable).

for example:

ops$tkyte@ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  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> create index t_idx on t(x) global;
 
Index created.
 
ops$tkyte@ORA10GR2> insert into t select to_date( '12-mar-2003' )+mod(rownum,3), rownum, rownum from all_users;
 
42 rows created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE TABLE t2
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  /
 
Table created.
 
ops$tkyte@ORA10GR2> insert into t2 select to_date( '12-mar-2003' ), rownum, rownum from all_users;
 
42 rows created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select status from user_indexes where index_name = 'T_IDX';
 
STATUS
--------
VALID
 
ops$tkyte@ORA10GR2> alter table t exchange partition part1 with table t2;
 
Table altered.
 
ops$tkyte@ORA10GR2> select status from user_indexes where index_name = 'T_IDX';
 
STATUS
--------
UNUSABLE
 
<b>that is what you have.... But</b>

ops$tkyte@ORA10GR2> alter index t_idx rebuild;
 
Index altered.
 
ops$tkyte@ORA10GR2> select status from user_indexes where index_name = 'T_IDX';
 
STATUS
--------
VALID
 
ops$tkyte@ORA10GR2> alter table t exchange partition part1 with table t2 UPDATE GLOBAL INDEXES; 
Table altered.
 
ops$tkyte@ORA10GR2> select status from user_indexes where index_name = 'T_IDX';
 
STATUS
--------
VALID

<b>that is what you can do...</b>


Now, you would not locally partition an index necessarily WITHOUT LOOKING AT YOUR DESIGN.  there are huge differences between local and global and you chose one over the other at design time to make data retrieval more efficient.  Going local might remove the unusable aspect but kill runtime performance.

If you have access to my latest book (Expert Oracle Database Architecture), I cover these topics in more detail. 

ops$tkyte%ORA10GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> CREATE TABLE t2 ( x int, y int )
  2  partition by range ( x )
  3  ( partition p values less than (maxvalue) )
  4  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 select rownum, rownum from all_users;

39 rows created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 't1' nm, count(*) from t1 union all select 't2', count(*) from t2;

NM   COUNT(*)
-- ----------
t1         39
t2          0

ops$tkyte%ORA10GR2> alter table t2
  2  exchange partition p with table t1
  3  -- other options here for indexes, validation and the like
  4  /

Table altered.

ops$tkyte%ORA10GR2> alter table t1 rename to t1_old;

Table altered.

ops$tkyte%ORA10GR2> alter table t2 rename to t1;

Table altered.

ops$tkyte%ORA10GR2> alter table t1_old rename to t2;

Table altered.

ops$tkyte%ORA10GR2> select 't1' nm, count(*) from t1 union all select 't2', count(*) from t2;

NM   COUNT(*)
-- ----------
t1         39
t2          0

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

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

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("X")
 (PARTITION "P"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS )

ops$tkyte%ORA11GR2> create table TEMP_partition
  2  (
  3  empno number(10),
  4  name varchar2(20),
  5  join_date date
  6  )
  7  partition by hash(empno) partitions 1
  8  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> drop table composite_partition_1;

Table dropped.

ops$tkyte%ORA11GR2> create table composite_partition_1
  2  (
  3  empno number(10),
  4  name varchar2(20),
  5  join_date date
  6  )
  7  partition by range(join_date)
  8  subpartition by hash(empno) subpartitions 1
  9  (
 10  partition part_1 values less than
 11  (to_date('01-jan-2009','dd-mon-yyyy'))(subpartition part1_sub1)
 12  ,partition part_2 values less than
 13  (to_date('01-may-2010','dd-mon-yyyy'))(subpartition part2_sub2)
 14  )
 15  /

Table created.

ops$tkyte%ORA11GR2> alter table composite_partition_1 exchange partition PART_2 with table
  2  TEMP_partition
  3  excluding indexes without validation
  4  /

Table altered.