关于语句的优化

$ 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>
阅读更多

没有更多推荐了,返回首页