METHOD_OPT : 'FOR ALL INDEXED COLUMNS' Vs 'FOR ALL COLUMNS SIZE AUTO'

Create a table with 3 columns with randomly generated numbers . Below is the some of the attributes of the table columns :

SQL> desc TEST ;
 Name                Type
  ----------------------------
 PRIMKEY         NUMBER
 COL2                 NUMBER
 COL3                 NUMBER

• Total rows in table = 800000
• Column PRIMKEY is having 800000 distinct values
• Column COL2 has only 4 distinct values
• Column COL3 has only 3 distinct values

begin
      dbms_stats.gather_table_stats( ownname => 'SYS' ,tabname => 'test' ,  estimate_percent => 100 ,
       cascade => true);
   end;
   /

Check Column statistics  :

SQL > select COLUMN_NAME COL_NAME, NUM_DISTINCT NDV, HISTOGRAM, NUM_BUCKETS, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
   from user_tab_col_statistics where table_name='TEST'
    /

COL_NAME        NDV        HISTOGRAM                  NUM_BUCKETS     LAST_ANALYZED
------------------------------ ------------ --------------- ----------- ---------------------------------------------
PRIMKEY              800000   NONE                                    1                         2015-19-12 16:12:22
COL2                         4           FREQUENCY                      4                         2015-19-12 16:12:22
COL3                         3           NONE                                    1                        2015-19-12 16:12:22


Create a unique index on primkey :

 create unique index PK_T1 on TEST(PRIMKEY);

Gather stat : method_opt => 'for all indexed columns' 

If you are using FOR ALL INDEXED COLUMNS as part of your METHOD_OPT you probably should not be.   "FOR ALL INDEXED COLUMNS"  tells DBMS_STATS to only gather stats on columns that participate in an index . No stats are calculated for non indexed columns of the table .

SQL> begin
    dbms_stats.delete_table_stats(user,'TEST');
   dbms_stats.gather_table_stats( ownname => user ,tabname => 'TEST' ,estimate_percent => 100 ,        method_opt => 'for all indexed columns'
  ,cascade => true);
  end;
  /

PL/SQL procedure successfully completed.


SQL>select COLUMN_NAME COL_NAME, NUM_DISTINCT NDV, HISTOGRAM, NUM_BUCKETS, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
   from user_tab_col_statistics where table_name='TEST'
    /

COL_NAME        NDV  HISTOGRAM                  NUM_BUCKETS     LAST_ANALYZED
------------------------------ ------------ --------------- ----------- ---------------------------------------------------
PRIMKEY         800000   HEIGHT BALANCED      75                    2015-19-12 16:13:36

Notice that in above cases only column PRIMKEY has stats on it. Columns COL2  and COL3 do not have any stats collected on them. Also note that when no SIZE clause is specified, it defaults to 75 buckets.

It is wasteful to create a histogram on a column that is never referenced by a SQL statement.  It is the SQL workload that determines if a column needs histograms.

Gather stat : method_opt => 'for all indexed columns size auto' 

Using MENTOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO' tells DBMS_STATS: “only gather stats on columns that participate in an index and based on data distribution and the workload of those indexed columns decide if a histogram should be created and how many buckets it should contain”.

The default value of  "for all columns size auto" is the Oracle default and this option will analyze histograms on what the optimizer considers to be "important columns".  The optimizer does this by examining your current workload when you run the dbms_stats command, interrogating all current SQL in the library cache to see which columns might benefit from a histogram.

SQL> begin
   dbms_stats.delete_table_stats(user,'TEST');

   dbms_stats.gather_table_stats(
     ownname => user ,
     tabname => 'TEST' ,
     estimate_percent => 100 ,
     method_opt => 'for all indexed columns size auto' ,
     cascade => true);
 end;
 /

PL/SQL procedure successfully completed.


SQL>select COLUMN_NAME COL_NAME, NUM_DISTINCT NDV, HISTOGRAM, NUM_BUCKETS, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
   from user_tab_col_statistics where table_name='TEST'
    /

COL_NAME        NDV     HISTOGRAM    NUM_BUCKETS     LAST_ANALYZED
------------------------------ ------------ --------------- ----------- -----------------------------------------
PRIMKEY            800000        NONE                        1                     2015-19-12 16:14:39
 

Impact of METHOD_OPT on cardinality  estimates and access path 

CASE1 : method_opt => 'for all indexed columns size auto'  with only single predicate in query

SQL> select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 ;

  COUNT(*)
----------
    660000

SQL>  select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 288h7750wj3z1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test where COL2=4000
Plan hash value: 1950795681
-------------------------------------------------------------------------------------
| Id  | Operation    | Name | Starts | E-Rows | A-Rows |  A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   |  1 |     |    1 |00:00:00.27 |    1856 |
|   1 |  SORT AGGREGATE    |   |  1 |   1 |    1 |00:00:00.27 |    1856 |
|*  2 |   TABLE ACCESS FULL| TEST |  1 |   8000 |  660K|00:00:00.18 |    1856 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - filter("COL2"=4000)


Notice the E-Rows estimate for TEST. The Optimizer is estimating 8000 rows when in reality there is 660000. The estimate is off by more than an order of magnitude (80x).

Normally the calculation for the cardinality would be (for a one table single equality predicate): number of rows in TEST * 1/NDV = 800000 * 1/4 = 200K but in this case 8K is the estimate. Because there are no column stats for TEST.COL2, the Optimizer is forced to make a guess and that guess is 8k/200k =0.04 (4%)

CASE 2: Now that we have seen  how poor the cardinality estimation was with a single equality predicate, let’s see what two equality predicates gives us for a cardinality estimate.

SQL> select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 and col3=3000 ;

  COUNT(*)
----------
     30000

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 216fcwk49zkn2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 and col3=3000 ;

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
| Id  | Operation    | Name | Starts | E-Rows | A-Rows |  A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   |  1 |     |    1 |00:00:00.03 |    1856 |
|   1 |  SORT AGGREGATE    |   |  1 |   1 |    1 |00:00:00.03 |    1856 |
|*  2 |   TABLE ACCESS FULL| TEST |  1 |  80 |  30000 |00:00:00.03 |    1856 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("COL2"=4000 AND "COL3"=3000))


Hopefully you can see where this is going. If you don’t, here is the all too common chain of events:

Non representative (or missing) statistics lead to :

---> Poor cardinality estimates which leads to
--->Poor access path selection which leads to
---> Poor join method selection which leads to
---> Poor join order selection which leads to
---> Poor SQL execution times

CASE 3 :  method_opt => 'for all columns size auto'   with a single equality predicate

 In this case we have statistics calculated not only for indexed columns but for all table columns :
Now let us check the cardinality estimates in case of single predicates in query .
Query will have spot on cardinality estimates,in this case see the details below :

  SQL> begin
    dbms_stats.delete_table_stats(user,'TEST');
dbms_stats.gather_table_stats(ownname => user , tabname => 'TEST' , estimate_percent => 100 ,degree => 8, method_opt => 'for all columns size auto' , cascade => true);
  end;
  /

PL/SQL procedure successfully completed.

SQL>select COLUMN_NAME COL_NAME, NUM_DISTINCT NDV, HISTOGRAM, NUM_BUCKETS, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
   from user_tab_col_statistics where table_name='TEST'
    /

COL_NAME        NDV     HISTOGRAM         NUM_BUCKETS     LAST_ANALYZED
------------------------------ ------------ --------------- ----------- --------------------------------------------------
PRIMKEY         800000                   NONE                       1                2015-19-12 16:34:58
COL2                  4                            FREQUENCY          4                2015-19-12 16:34:58
COL3                  3                            FREQUENCY          3                2015-19-12 16:34:58


SQL> select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 ;

  COUNT(*)
----------
    660000

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 288h7750wj3z1, child number 1
-----------------------------------------------------------------------------------
select /*+ gather_plan_statistics */ count(*) from test where COL2=4000

Plan hash value: 1950795681
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |  A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  1        |          |    1 |00:00:00.14 |    1856 |
|   1 |  SORT AGGREGATE      |           |  1        |   1     |    1 |00:00:00.14 |    1856 |
|*  2 |   TABLE ACCESS FULL| TEST | 1        | 660K   | 660K |00:00:00.09 |    1856 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COL2"=4000)

CASE 4 :  method_opt => 'for all columns size auto'   with two equality predicates

In this case we have statistics calculated not only for indexed columns but for all table columns :
Now let us check the cardinality estimates in case of multiple predicates in query .
Query will have spot on cardinality estimates,in this case see the details below :

  SQL> begin
    dbms_stats.delete_table_stats(user,'TEST');
dbms_stats.gather_table_stats(ownname => user , tabname => 'TEST' , estimate_percent => 100 ,degree => 8, method_opt => 'for all columns size auto' , cascade => true);
  end;
  /

PL/SQL procedure successfully completed.

SQL>select COLUMN_NAME COL_NAME, NUM_DISTINCT NDV, HISTOGRAM, NUM_BUCKETS, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
   from user_tab_col_statistics where table_name='TEST'
    /

COL_NAME        NDV     HISTOGRAM         NUM_BUCKETS     LAST_ANALYZED
------------------------------ ------------ --------------- ----------- --------------------------------------------------
PRIMKEY         800000                   NONE                       1                2015-19-12 16:34:58
COL2                  4                            FREQUENCY          4                2015-19-12 16:34:58
COL3                  3                            FREQUENCY          3                2015-19-12 16:34:58


SQL> select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 and col3=3000 ;

  COUNT(*)
----------
     30000

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 216fcwk49zkn2, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test where COL2=4000 and col3=3000

Plan hash value: 1950795681

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |  A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |  1 |              |    1        |00:00:00.03 |    1856 |
|   1 |  SORT AGGREGATE        |            |  1 |   1         |    1        |00:00:00.03 |    1856 |
|*  2 |   TABLE ACCESS FULL| TEST |  1 |  30000     |  30000 |00:00:00.03 |    1856 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("COL3"=3000 AND "COL2"=4000))

Oracle Clustering factor

Clustering factor statistic is used by the optimizer during query optimization to determine the relative efficiency of an index. In short, the index clustering factor is a measure of how many I/Os the database would perform if it were to read every row in that table via the index in index order. 

If the rows of a table on disk are sorted in about the same order as the index keys, the database will perform a minimum number of I/Os on the table to read the entire table via the index. That is because the next row needed from an index key would likely be the next row in the table block as well. The query would not be skipping all over the table to find row after row—they are naturally next to each other on the block.

Conversely, if the rows in the table are not in the same order on disk as the index keys—if the data is scattered—the query will tend to perform the maximum number of I/Os on the table, as many as one I/O for every row in the table. That is because as the database scans through the index, the next row needed will probably not be on the same block as the last row. The database will have to discard that block and get another block from the buffer cache. The query will end up reading every block from the buffer as many times as it has rows on it.

So if a table and an index key are in about the same order, the clustering factor will be near the number of blocks in the table and the index will be useful for very large index range scans and for retrieving numerous rows from the table. 

On the other hand, if the data is randomly scattered, the clustering factor will be near the number of rows in the table, and given that the number of rows in a table is usually at least an order of magnitude more than the number of blocks, the index will be less efficient for returning numerous rows.

Example of clustering factor and query cost :
================================

---> Create a table with data sorted on column object_name :

SQL> create table GoodCF as select x.* from ( select * from all_objects order by object_name)x ;

Table created.

---> Create a table with disorganized/scattered data . Used dbms_random to sort it randomly :

SQL> create table Scattered as select x.* from ( select * from all_objects order by dbms_random.random)x ;

Table created.

---> Created index on sorted column :

SQL> create index GoodCF_IDX on GoodCF(object_name) ;

Index created.

---> Created index on scattered data column :

SQL> create index Scattered_IDX on Scattered(object_name) ;

Index created.

---> Gather stat on table GOODCF :

SQL> begin dbms_stats.gather_table_stats ( 'SYS', 'GOODCF',estimate_percent => 100,method_opt=>'for all indexed columns size 254'); end ;
  /
PL/SQL procedure successfully completed.

----> Gather stat on table scattered :

SQL> begin dbms_stats.gather_table_stats ( 'SYS', 'SCATTERED',estimate_percent => 100,method_opt=>'for all indexed columns size 254'); end ;
    /
PL/SQL procedure successfully completed.

----> Let's check the number of block and rows for both the tables . Both tables are having same data and same number of BLOCKS :

SQL> select table_name, blocks, num_rows from dba_tables where table_name in ('GOODCF','SCATTERED') order by 1;

TABLE_NAME   BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
GOODCF     1020      71713
SCATTERED     1020      71714

----> Let's check the CLUSTERING_FACTOR for both the indexes . I see a large difference between the two. The GOODCF_IDX index has a clustering factor very near the number of blocks in the table, whereas the SCATTERED_IDX index has a clustering factor near the number of rows in the table. 

Again, this clustering factor metric is a measure of how many I/Os the database will perform against the table in order to read every row via the index.We can verify this with the below example :

SQL> select table_name, index_name, clustering_factor from dba_indexes where table_name in ('GOODCF','SCATTERED') order by 1;

TABLE_NAME       INDEX_NAME         CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------------------------------------- -----------------
GOODCF       GOODCF_IDX      1020
SCATTERED               SCATTERED_IDX       71640

SQL> show parameter user_dump_dest

NAME             TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest   string /data01/app/oracle/diag/rdbms/ggtrg/ggtrg/trace

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER SESSION SET tracefile_identifier='MYTRACE_GoodCF' ;

Session altered.

SQL> select /*+ index( GOODCF GOODCF_IDX) */ count(subobject_name) from GOODCF;

COUNT(SUBOBJECT_NAME)
---------------------
 457

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

========================================================================
I/O performed against the index   = 357 I/Os 
Total I/Os performed by the query = 1377 I/Os
Note : 1377 - 357 = 1020  ( Equal to the Clusterfing factor of the index GOODCF_IDX ) 
========================================================================

PARSING IN CURSOR #1 len=73 dep=0 uid=0 oct=3 lid=0 tim=1450372523290892 hv=966910787 ad='a266a890' sqlid='9d45484wu3su3'
select /*+ index( GOODCF GOODCF_IDX) */ count(subobject_name) from GOODCF
END OF STMT
PARSE #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2836254517,tim=1450372523290891
EXEC #1:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2836254517,tim=1450372523290955
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1450372523291015
*** 2015-12-17 12:15:23.334
FETCH #1:c=38994,e=43776,p=0,cr=1377,cu=0,mis=0,r=1,dep=0,og=1,plh=2836254517,tim=1450372523334808
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1377 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=71713 pid=1 pos=1 obj=74669 op='TABLE ACCESS BY INDEX ROWID GOODCF (cr=1377 pr=0 pw=0 time=37284 us cost=1378 size=1219121 card=71713)'
STAT #1 id=3 cnt=71713 pid=2 pos=1 obj=74671 op='INDEX FULL SCAN GOODCF_IDX (cr=357 pr=0 pw=0 time=11189 us cost=358 size=0 card=71713)'
WAIT #1: nam='SQL*Net message from client' ela= 193 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1450372523335123
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2836254517,tim=1450372523335144
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1450372523335155
*** 2015-12-17 12:15:33.973
WAIT #1: nam='SQL*Net message from client' ela= 10637912 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1450372533973100
CLOSE #1:c=1000,e=763,dep=0,type=0,tim=1450372533973915
========================================================================

SQL> show parameter user_dump_dest

NAME             TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest   string /data01/app/oracle/diag/rdbms/ggtrg/ggtrg/trace


SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Session altered.

SQL> ALTER SESSION SET tracefile_identifier='MYTRACE_SCATTERED' ;

Session altered.

SQL> select /*+ index( GOODCF GOODCF_IDX) */ count(subobject_name) from GOODCF;

COUNT(SUBOBJECT_NAME)
---------------------
 457

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

========================================================================
I/O performed against the index   = 357 I/Os 
Total I/Os performed by the query = 71997 I/Os
Note : 71997 - 357 = 71640  ( Equal to the Clusterfing factor of the index SCATTERED_IDX ) 
========================================================================

PARSING IN CURSOR #1 len=81 dep=0 uid=0 oct=3 lid=0 tim=1450373201636876 hv=15917473 ad='a24955d0' sqlid='8fzruxc0g5sd1'
select /*+ index(SCATTERED SCATTERED_IDX) */ count(subobject_name) from SCATTERED
END OF STMT
FETCH #1:c=117983,e=124417,p=1377,cr=71997,cu=0,mis=0,r=1,dep=0,og=1,plh=300164232,tim=1450373201761488
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=71997 pr=1377 pw=0 time=0 us)'
STAT #1 id=2 cnt=71714 pid=1 pos=1 obj=74670 op='TABLE ACCESS BY INDEX ROWID SCATTERED (cr=71997 pr=1377 pw=0 time=116666 us cost=72014 size=1219138 card=71714)'
STAT #1 id=3 cnt=71714 pid=2 pos=1 obj=74672 op='INDEX FULL SCAN SCATTERED_IDX (cr=357 pr=357 pw=0 time=19344 us cost=358 size=0 card=71714)'
WAIT #1: nam='SQL*Net message from client' ela= 154 driver id=1650815232 #bytes=1 p3=0 obj#=74672 tim=1450373201761751
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=300164232,tim=1450373201761780
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=74672 tim=1450373201761790
========================================================================

AWR : Response Time Per Txn

select min(begin_time), max(end_time),
       sum(case metric_name when 'Response Time Per Txn' then average end) ResponseTimePerTxn,
       snap_id
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;

TPS Historical

WITH hist_snaps
     AS (SELECT instance_number,
                snap_id,
                round(begin_interval_time,'MI') datetime,
                  (  begin_interval_time
                   + 0
                   - LAG (
                        begin_interval_time + 0)
                     OVER (PARTITION BY dbid, instance_number
                           ORDER BY snap_id))
                * 86400      -- seconds per day
                   diff_time -- calculate difference in time from
                             -- previous snapshot
                   FROM dba_hist_snapshot )
   , hist_stats
     AS (SELECT dbid,
                instance_number,
                snap_id,
                stat_name,
                  VALUE
                - LAG (
                     VALUE)
                  OVER (PARTITION BY dbid,instance_number,stat_name
                        ORDER BY snap_id)
                  delta_value -- difference in value from previous
                              -- snapshot for each stat_name
           FROM dba_hist_sysstat
          WHERE stat_name IN ('user commits', 'user rollbacks'))
  SELECT to_char(cast(datetime as date ), 'YYYY/MM/DD HH24:MI'),
         ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
     FROM hist_snaps sn, hist_stats st
   WHERE     st.instance_number = sn.instance_number
         AND st.snap_id = sn.snap_id
         AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;

How Do Indexes Become Unusable

Maintenance operations causing index partitions to become INDEX UNUSABLE (IU):

   1. IMPORT PARTITION or conventional path SQL*Loader.

   2. Direct-path SQL*Loader leaves affected local index partitions and  global indexes in an IU state if it does not complete successfully.

   3. Partition maintenance operations like ALTER TABLE MOVE PARTITION.

   4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION.

   5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION.

   6. Index maintenance operations like ALTER INDEX SPLIT PARTITION.

   7. Adding a Partition to a Hash-Partitioned Table

Adding a Partition to a Hash-Partitioned Table

When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.

The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear ADD PARTITION p_named TABLESPACE gear5;

Indexes may be marked UNUSABLE as explained in the following table:

Table Type               Index Behavior
Regular (Heap)         Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
                              The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and                               must be rebuilt.
                              All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.


Index-organized       For local indexes, the behavior is identical to heap tables.
                              All global indexes remain usable.

Each of these operations may cause index partitions to be marked IU which will require the index partitions to be rebuilt.

Version 10.2
=============
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then
rebuild the entire index or, in the case of a global index, each of its partitions. The database lets you override this default behavior if you specify
UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the index at the time it
executes the maintenance operation DDL statement.

The following operations support the UPDATE INDEXES clause:
    *  ADD PARTITION | SUBPARTITION
    *  COALESCE PARTITION | SUBPARTITION
    *  DROP PARTITION | SUBPARTITION
    *  EXCHANGE PARTITION | SUBPARTITION
    *  MERGE PARTITION | SUBPARTITION
    *  MOVE PARTITION | SUBPARTITION
    *  SPLIT PARTITION | SUBPARTITION
    *  TRUNCATE PARTITION | SUBPARTITION

Reference : Oracle support metalink