关于语句的优化

最新推荐文章于 2024-07-25 17:46:08 发布
原创 最新推荐文章于 2024-07-25 17:46:08 发布 · 652 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#优化 #report #buffer #sql #filter #iterator

$ 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>
oracle数据库表清单,Oracle 数据库表（常见的表）
weixin_35991051的博客
04-07 1403
数据库表(常见的表)堆组织表:普通表索引组织表：iot嵌套表临时表外部表1 表一个表最多1000列，oracle会把列大于254的行存储在多个单独的行段中，表中的行是无限的，术语：段：表：表段保存一个数据库表的数据表分区：这种段类型用于分区索引：这种段类型保存索引结构索引分区：类似于表分区嵌套表：为嵌套表指定段的类型回滚段：undo数据就存在这里段空间管理Hwm高水位线2 堆组织表全表扫描时，会按...
关于性能测试：数据库的 SQL 性能优化实战
朱雀随云记的博客
11-14 1390
索引是提高SQL查询性能的关键。同时，要注意索引的维护成本，避免创建过多的索引导致插入、更新和删除操作的性能下降。做过性能测试的同学都应该知道，性能测试核心就是结果分析和性能瓶颈调优，然后性能的瓶颈70%-80%的问题都是来自于数据库。所以，在做性能测试的时候，如果发现数据库占的CPU比较高，或者响应时间比较长，都可以去检查一下 数据库是否存在上述的SQL问题，从而来优化项目的数据库的性能。数据库SQL性能优化是提升数据库运行效率的关键环节，以下是一些具体的注意点，并结合案例进行演示和说明，帮助大家理解。
数据表创建参数介绍（一）
ciqu9915的博客
02-26 197
创建数据表create table是我们对数据库进行的常见操作。我们一般使用create table之后，指定了数据列信息和主键等约束信息，其他就交给Oracle使用默认值了。今天我们一起来看看这些默认值。说明：本片只关注...
Oracle 建表语句的关键字详解
热门推荐
帝上无天的博客
05-08 1万+
目录 一、建表语句 二、关键字详解 1. ENABLE 和 DISABLE 2. 创建主键约束 3. USING INDEX 4. PCTFREE 5. PCTUSED 6. INITRANS 和 MAXTRANS 1）INITRANS 2）MAXTRANS 7. COMPUTE STATISTICS 8. STORAGE 1）表空间管理机制 2）STORAGE 内部参数 3）FREELISTS 和FREELIST GROUPS 4）BUFFER_POOL 5) SE...
数据表创建参数介绍
欧辰的专栏
06-17 1826
创建数据表create table是我们对数据库进行的常见操作。我们一般使用create table之后，指定了数据列信息和主键等约束信息，其他就交给Oracle使用默认值了。今天我们一起来看看这些默认值。说明：本片只关注一般数据表，临时表、聚簇、IOT等特殊类型暂时不考虑。   提取完整的DDL   首先我们需要提取出创建数据表的完整DDL语句，才能将Oracle提供的默认值们正确抽取出
常用的三种数据库创建存储过程并解析json
qq_43123348的博客
09-10 3808
MySQL、oracle、SqlServer建立存储过程并解析json
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
09-14
SQL语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句 SQL语句优化是数据库性能优化的重要方面之一。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS...
SQLServer语句优化中文最新版本
最新发布
11-22
本文档将会对SQL Server中的语句优化进行详细讲解，内容覆盖了语句优化的基本理论、实际操作技巧以及优化过程中的常见误区，旨在为数据库管理员或开发者提供系统的优化指导。 SQL Server语句优化是一项专业技术...
SQL语句优化提高数据库性能
09-10
以下是一些关于SQL语句优化的重要策略： 1. **减少表连接（JOIN）的数量**：避免过度的表连接是优化SQL语句的基本原则。当查询涉及多个表时，尽量限制连接数量在5个以内，以降低查询的复杂度。 2. **使用临时表或...
SQL SERVER 的SQL语句优化方式小结
09-11
以下是一些关于SQL SERVER 2005中SQL语句优化的重要知识点： 1. **SQL Server Profiler**：这是一个强大的工具，用于跟踪和分析数据库的活动。它可以记录所有的SQL语句、存储过程调用以及各种事件，帮助识别性能...
javascript教程:关于if简写语句优化的方法
12-04
下面我们将探讨几种通过UglifyJS或直接在编写代码时采用的if语句优化技巧。 1. **使用三元操作符** 三元操作符是JavaScript中的一种简洁表达方式，它可以替代简单的if-else结构。例如： ```javascript if (foo) ...
[每日一题] OCP1z0-047 :2013-08-28 DELETE..........................................................160
长期深耕于云计算、大数据、数据库领域！
08-28 3103
[每日一题] OCP1z0-047 :2013-08-28 DELETE
oracle数据库优化-ORACLE分区技术
oradbm的博客
10-16 590
分区技术分而治之，对于hash分区能够有效缓解热点块问题(提高indexrange成本） ORACLE 分区技术 分区技术可以将大表、大索引分解为更小更易管理的块，这些块被称为分区，通过分区技术可以有效的解决大表、大索引带来的问题,在VLDB环境中，分区表有助于提高数据库的可用性和可维护性。将一个分区表划分成几个易于管理的称为’分区’的部分，分区可以进一步化成分子分区。分区表上的索引可能是...
oracle 主键约束复制,Oracle主键及约束
weixin_34581040的博客
04-11 569
Oracle主键Primary Key包含非空约束及唯一约束。添加主键的语句alter table table_nameadd constraint cons_name primary key(col_name);查看主键是否被创建成功select dbms_metadata.get_ddl(‘OBJECT_TYPE‘,‘NAME‘,‘SCHEMA‘) from dual;OBJECT_TYPE ...
Oracle-在线迁移表到新表空间
sinat_36757755的博客
05-30 2834
​在Oracle线上系统中，我们经常会需要迁移表到新表空间或者通过move tablespace的方式去清理高水位等类似迁移场景，但通过move操作会导致锁表的发生，这在线上系统是不可接受的，在Oracle11g提供了在线重定义的方式，通过dbms_redefinition进行在线表迁移，避免了长时间锁表的发生、影响业务的连续性。
Oracle 12c 新特性 --- Rolling Back Redefinition
leo__1990的博客
05-07 341
1 概念 There is a new ROLLBACK parameter for the FINISH_REDEF_TABLE procedure that tracks DML on a newly redefined table so that changes can be easily synchronized with the original table using the SY...
删除表空间出现ORA-22868错误（三）
ckawt40802的专栏
07-26 308
今天删除一个不在使用的表空间时，碰到了ORA-22868错误。 这篇描述解决错误的过程。 删除表空间出现ORA-22868错误（一）：http://yangtingkun.itpub.net/post/468/488288 ...
oracle多表关联修改
qq_43077857的博客
04-27 2578
首先 建表 表一 CREATE TABLE “SCOTT”.“TF_DORMITORY_TASK_INFO” ( “ID” VARCHAR2(50) NOT NULL ENABLE, “STAFF_ID” VARCHAR2(50), “PARENT_ID” VARCHAR2(50), “TYPE” VARCHAR2(8), “DESCPTION” VARCHAR2(500), “AUDIT_STA...
oracle 大字段进行导出导入后空间占用大不同，原来是字段压缩惹的祸！
老苏畅谈运维
07-25 783
生产上有一张大字段的表，大小300G左右，将其通过expdp命令导出，并通过impdp导入一个测试库后，发现其占用空间竟然比生产库大了很多，导入后的空间占了700G左右。这是怎么回事，按理说，导入后空间不应该大这么多。由于涉及敏感信息，这里就自己创建一个测试表，复现一下整个过程。
Oracle SQL语句优化实战：hawk分析与优化技巧
资源摘要信息:"Oracle SQL语句优化是数据库管理中的一个重要环节，它涉及到对数据库查询语句的编写和调整，以达到减少资源消耗、提升查询效率的目的。本资源包主要围绕Oracle数据库环境下的SQL语句优化实践展开，...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值