sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 7 12:50:42 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select  table_name ,  count(*)  from  dba_tab_partitions  group by table_name    having count(*) >1000; 

TABLE_NAME                       COUNT(*)
------------------------------ ----------
XXX_COYYY                           4172
SYSAUD$                              1103
ZZZZ_KEEPER                          3278
WERT                                 5301
MMNBV                                2897
ASD_LOG                              4172

7 rows selected.

select  max (cnt ) from (select  table_owner,  table_name ,  count(*) cnt   from  dba_tab_partitions  group by table_name , table_owner    having count(*) >1000);

  MAX(CNT)
----------
      3297

SQL> explain plan for (select max(time)  from xxxx.xxx);

Explained.

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 727408685
[FIXED]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |     8 |  4012   (1)| 00:00:49 |       |       |
|   1 |  SORT AGGREGATE             |               |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL       |               |     1 |     8 |  4012   (1)| 00:00:49 |     1 |  2004 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| IDX_xxx_TIME |     1 |     8 |  4012   (1)| 00:00:49 |     1 |  2004 |
-------------------------------------------------------------------------------------------------------------

[/FIXED]10 rows selected.

Elapsed: 00:00:00.02
SQL> select max(time)  from xxxx.xxx;

MAX(TIME)
---------
07-OCT-16

Elapsed: 00:00:00.02
SQL> select min(time)  from xxxx.xxx;

MIN(TIME)
---------
07-APR-11

Elapsed: 00:00:00.03

SQL> select count(*) from   dba_ind_partitions where  index_owner='xxxx' and  index_name='IDX_xxx_TIME' ;

  COUNT(*)
----------
      2004

Elapsed: 00:00:00.10

SQL> explain plan for (select count(*)   from xxxx.xxx);

Explained.

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2893698907

----------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |  2594K  (2)| 08:38:56 |       |       |
|   1 |  SORT AGGREGATE        |             |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL  |             |  2929M|  2594K  (2)| 08:38:56 |     1 |  2004 |
|   3 |    INDEX FAST FULL SCAN| IDX_xxx_ID |  2929M|  2594K  (2)| 08:38:56 |     1 |  2004 |
----------------------------------------------------------------------------------------------

10 rows selected.

Elapsed: 00:00:00.02
SQL> select count(*) from  xxxx.xxx ;

  COUNT(*)
----------
1.6203E+10

Elapsed: 01:05:13.12

SQL> explain plan for (select max(id)   from xxx.xxx);

Explained.

Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2769239955

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |     7 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL        |             |     1 |     7 |            |          |  2004 |     1 |
|   2 |   SORT AGGREGATE            |             |     1 |     7 |            |          |       |       |
|   3 |    INDEX FULL SCAN (MIN/MAX)| IDX_xxx_ID |     1 |     7 |     3   (0)| 00:00:01 |  2004 |     1 |
-----------------------------------------------------------------------------------------------------------

10 rows selected.

Elapsed: 00:00:00.33
SQL> select max(id) from  xxx.xxx ;

   MAX(ID)
----------
2.0801E+10

Elapsed: 00:00:00.00