关于语句的优化

于 2008-06-10 10:08:00 发布
阅读量652 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 数据库 文章标签： 优化 report buffer sql filter iterator
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/henrybai/article/details/2529452
$ sqlplus / as sysdba                                                              
                                                                                   
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 4 02:20:54 2008               
                                                                                   
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.                            
                                                                                   
                                                                                   
Connected to:                                                                      
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production       
With the Partitioning, Real Application Clusters, OLAP and Data Mining options     
                                                                                   
SQL>    select index_name,degree,owner                                             
  2     from dba_indexes                                                           
   where index_name in ('PK_METRICDATA');                                          
  3                                                                                
INDEX_NAME                     DEGREE                                              
------------------------------ ----------------------------------------            
OWNER                                                                              
------------------------------                                                     
PK_METRICDATA                  DEFAULT                                             
SGREPORTS                                                                          
                                                                                   
                                                                                   
SQL> set long 2000                                                                 
SQL> SET PAGESIZE 9999                                                             
                                                                               
SQL>    select dbms_metadata.get_ddl('INDEX','PK_METRICDATA','SGREPORTS') from dual;
                                                                                   
DBMS_METADATA.GET_DDL('INDEX','PK_METRICDATA','SGREPORTS')                         
--------------------------------------------------------------------------------   
                                                                                   
  CREATE UNIQUE INDEX "SGREPORTS"."PK_METRICDATA" ON "SGREPORTS"."METRIC_DATA" (   
"DATETIME", "INVENTORY_ID", "FORMULA_ID", "PROPERTY_ID")                           
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(                                                                         
  BUFFER_POOL DEFAULT) LOCAL                                                       
 (PARTITION "METRIC_DATA_20080520"                                                 
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080521"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080522"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080523"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080524"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080525"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080526"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT                                                            
                                                                                   
                                                                                   
SQL> set long 2000000                                                              
SQL> /                                                                             
                                                                                   
DBMS_METADATA.GET_DDL('INDEX','PK_METRICDATA','SGREPORTS')                         
--------------------------------------------------------------------------------   
                                                                                   
  CREATE UNIQUE INDEX "SGREPORTS"."PK_METRICDATA" ON "SGREPORTS"."METRIC_DATA" (   
"DATETIME", "INVENTORY_ID", "FORMULA_ID", "PROPERTY_ID")                           
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(                                                                         
  BUFFER_POOL DEFAULT) LOCAL                                                       
 (PARTITION "METRIC_DATA_20080520"                                                 
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080521"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080522"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080523"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080524"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080525"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080526"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080527"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080528"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080529"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080530"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080531"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080601"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080602"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING                                    
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080603"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080604"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080605"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080606"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080607"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080608"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080609"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080610"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080611"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080612"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080613"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" ,                                                    
 PARTITION "METRIC_DATA_20080614"                                                  
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                               
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645            
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                 
  TABLESPACE "SG_REPORT_DATA" )                                                    
  PARALLEL   
 
  SQL> set autotrace trace exp
SQL>  SELECT  TO_CHAR(DATETIME at time zone '-04:00', 'MM/DD/YYYY HH24:MI') DATETIME, POLL_DATA                                                       
FROM METRIC_DATA                                                                                                                              
WHERE  (DATETIME >CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)               
AND  DATETIME<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)) 
And INVENTORY_ID = 83296  2    3    4   AND FORMULA_ID = 1 AND PROPERTY_ID = 118331                                                                        
ORDER BY DATETIME ASC          5    6 
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4229140614

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |               |     1 |    28 |   104   (4)| 00:00:02 |       |       |        |      |            |
|*  1 |  PX COORDINATOR                          |               |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)                     | :TQ10001      |     1 |    28 |   104   (4)| 00:00:02 |       |       |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                         |               |     1 |    28 |   104   (4)| 00:00:02 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                           |               |     1 |    28 |   103   (3)| 00:00:02 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE                       | :TQ10000      |     1 |    28 |   103   (3)| 00:00:02 |       |       |  Q1,00 | P->P | RANGE      |
|*  6 |       FILTER                             |               |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|   7 |        PX PARTITION RANGE ITERATOR       |               |     1 |    28 |   103   (3)| 00:00:02 |   KEY |   KEY |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| METRIC_DATA   |     1 |    28 |   103   (3)| 00:00:02 |   KEY |   KEY |  Q1,00 | PCWP |            |
|*  9 |          INDEX RANGE SCAN                | PK_METRICDATA |     1 |       |   103   (3)| 00:00:02 |   KEY |   KEY |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00','MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME
              ZONE)<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00','MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE))
   6 - filter(CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00','MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME
              ZONE)<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00','MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE))
   9 - access("DATETIME">CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00','MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE) AND
              "INVENTORY_ID"=83296 AND "FORMULA_ID"=1 AND "PROPERTY_ID"=118331 AND "DATETIME"<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00','MM/DD/YYYY
              HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE))
       filter("PROPERTY_ID"=118331 AND "INVENTORY_ID"=83296 AND "FORMULA_ID"=1)

SQL> set autotrace trace stat
SQL> /

258 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      82638  consistent gets
          0  physical reads
          0  redo size
       9561  bytes sent via SQL*Net to client
        679  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        258  rows processed                                                                     
                                                                                   
sqlplus / as sysdba
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 4 02:42:40 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP
and Data Mining options

SQL>  select index_name,degree,owner                                             
  2         from dba_indexes                                                           
   where index_name in ('PK_METRICDATA');   3 

INDEX_NAME                     DEGREE
------------------------------ ----------------------------------------
OWNER
------------------------------
PK_METRICDATA                  1
SGREPORTS


SQL> set long 200000  
SQL> set pages 12345
SQL>   select dbms_metadata.get_ddl('INDEX','PK_METRICDATA','SGREPORTS') from dual;

DBMS_METADATA.GET_DDL('INDEX','PK_METRICDATA','SGREPORTS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SGREPORTS"."PK_METRICDATA" ON "SGREPORTS"."METRIC_DATA" (
"DATETIME", "INVENTORY_ID", "PROPERTY_ID", "FORMULA_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
 (PARTITION "METRIC_DATA_20080520"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080521"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080522"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080523"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080524"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080525"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080526"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080527"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080528"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080529"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080530"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080531"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080601"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080602"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080603"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080604"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080605"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080606"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080607"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080608"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080609"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080610"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080611"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080612"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080613"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080614"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" )



SQL> set long 1234567
SQL> /

DBMS_METADATA.GET_DDL('INDEX','PK_METRICDATA','SGREPORTS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SGREPORTS"."PK_METRICDATA" ON "SGREPORTS"."METRIC_DATA" (
"DATETIME", "INVENTORY_ID", "PROPERTY_ID", "FORMULA_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT) LOCAL
 (PARTITION "METRIC_DATA_20080520"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080521"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080522"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080523"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080524"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080525"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080526"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080527"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080528"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080529"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080530"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080531"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080601"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080602"
  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080603"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080604"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080605"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080606"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080607"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080608"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080609"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080610"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080611"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080612"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080613"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" ,
 PARTITION "METRIC_DATA_20080614"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SG_REPORT_DATA" )



SQL> conn sgreports/sgreports
Connected.
SQL> set autotrace trace exp
SQL>  SELECT  TO_CHAR(DATETIME at time zone '-04:00', 'MM/DD/YYYY HH24:MI') DATETIME, POLL_DATA                                                       
FROM METRIC_DATA                                                                                                                              
WHERE  (DATETIME >CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)               
AND  DATETIME<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00', 'MM/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP WITH LOCAL TIME ZONE)) 
And INVENTORY_ID = 83296  2    3    4   AND FORMULA_ID = 1 AND PROPERTY_ID = 118331                                                                        
ORDER BY DATETIME ASC          5    6 
  7  /

Execution Plan
----------------------------------------------------------
Plan hash value: 938119123

--------------------------------------------------------------------------------
--------------------------------------

| Id  | Operation                            | Name          | Rows  | Bytes | C
ost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
--------------------------------------

|   0 | SELECT STATEMENT                     |               |     1 |    28 |
 100   (2)| 00:00:01 |       |       |

|   1 |  SORT ORDER BY                       |               |     1 |    28 |
 100   (2)| 00:00:01 |       |       |

|*  2 |   FILTER                             |               |       |       |
          |          |       |       |

|   3 |    PARTITION RANGE ITERATOR          |               |     1 |    28 |
  99   (2)| 00:00:01 |   KEY |   KEY |

|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| METRIC_DATA   |     1 |    28 |
  99   (2)| 00:00:01 |   KEY |   KEY |

|*  5 |      INDEX RANGE SCAN                | PK_METRICDATA |     1 |       |
  99   (3)| 00:00:01 |   KEY |   KEY |

--------------------------------------------------------------------------------
--------------------------------------


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

   2 - filter(CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00','MM/DD/YYYY HH24:MI
 tzh:tzm') AS TIMESTAMP WITH

              LOCAL TIME ZONE)<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00','M
M/DD/YYYY HH24:MI tzh:tzm') AS TIMESTAMP

              WITH LOCAL TIME ZONE))
   5 - access("DATETIME">CAST(TO_TIMESTAMP_TZ('05/30/2008 00:00 -04:00','MM/DD/Y
YYY HH24:MI tzh:tzm') AS

              TIMESTAMP WITH LOCAL TIME ZONE) AND "INVENTORY_ID"=83296 AND "PROP
ERTY_ID"=118331 AND "FORMULA_ID"=1 AND

              "DATETIME"<CAST(TO_TIMESTAMP_TZ('05/30/2008 23:59 -04:00','MM/DD/Y
YYY HH24:MI tzh:tzm') AS TIMESTAMP WITH

              LOCAL TIME ZONE))
       filter("PROPERTY_ID"=118331 AND "INVENTORY_ID"=83296 AND "FORMULA_ID"=1)

SQL> set autotrace trace stat
SQL> /














114 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     218344  consistent gets
      79065  physical reads
      74540  redo size
       4505  bytes sent via SQL*Net to client
        569  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        114  rows processed

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值