关于语句的优化

最新推荐文章于 2024-09-14 19:55:29 发布
最新推荐文章于 2024-09-14 19:55:29 发布
阅读量123 收藏
点赞数
文章标签： 数据库
$ 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>
iteye_4537
关注
SQL语句优化
李澎昆的博客
02-20 535
SQL语句优化 　　怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。 　　建立索引不是建的越多越好，原则是： 　　第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。 　　第二...
sql语句优化
lempoo的博客
06-23 171
### part1 : sql语句优化 (1) mysql 执行流程 客户端: 发送连接请求,然后发送增删改查sql语句进行执行 服务端: 1.连接层:提供和客户端连接的服务,在tcp协议下, 提供多线程并发的技术,让多个用户登录到mysql中. show processlist; 查看所有登录到mysql的用户进程 2.服务器: 提供了各种接口(增删改查..)分析器组件会解析用户的sql语句 如果发现sql语句执行效率较低,会提交给优化器组件进行优化,然后在执行 (查询缓存:把上次搜过的数据,或者提
oracle多表关联修改
qq_43077857的博客
04-27 2502
首先 建表 表一 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 主键约束复制,Oracle主键及约束
weixin_34581040的博客
04-11 497
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分区之三：索引分区
Watson的专栏
01-17 5810
分区索引分为本地(local index)索引和全局索引(global index)。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE
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...
关于统计信息过期的性能落差
congbao6525的博客
05-14 148
今天客户反馈某一个应用部署补丁的时候，执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_01 (select * from em1_r...
update 语句优化update 语句优化update 语句优化
03-08
### Update语句优化 在数据库操作中，`UPDATE`语句是用于修改现有记录的关键工具之一。对于大型数据库系统来说，优化`UPDATE`语句能够显著提升数据处理速度和效率，减少资源消耗，提高整体性能。本文将通过分析提供...
SQL语句优化提高数据库性能
09-10
以下是一些关于SQL语句优化的重要策略： 1. **减少表连接（JOIN）的数量**：避免过度的表连接是优化SQL语句的基本原则。当查询涉及多个表时，尽量限制连接数量在5个以内，以降低查询的复杂度。 2. **使用临时表或...
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
09-14
SQL语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句 SQL语句优化是数据库性能优化的重要方面之一。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS...
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) ...
数据库数据导出建表SQL解析
xxydzyr的博客
01-20 3082
数据库数据导出建表SQL解析 概述： ​ 今天遇到一段神奇的SQL，表示没有遇到过。所以学习了一下，解析解析。 SQL： set serveroutput on size 10000; set timing on; declare c_ddl clob; begin c_ddl := dbms_metadata.get_ddl('TABLE','DEPT','SCOTT'); d...
数据表创建参数介绍（一）
ciqu9915的博客
02-26 142
创建数据表create table是我们对数据库进行的常见操作。我们一般使用create table之后，指定了数据列信息和主键等约束信息，其他就交给Oracle使用默认值了。今天我们一起来看看这些默认值。说明：本片只关注...
oracle19c的创建index的并发度与服务器cpu数目相同，创建速度最快的结论验证
viviliving的专栏
10-18 171
最近贵阳数转，遇到dba创建index使用了1000并发，感觉这样玩，cpu应该在同一个任务上都要切换30次左右把，这样会快么，为了驳斥该观点，进行了同库测试。测试使用了一个4000万和2亿的表进行创建索引测试。先看一下我的测试结论。
Oracle 19c VLDB and Partitioning Guide 第8章：Using Parallel Execution 读书笔记
In-Memory Computing Technology
10-19 1072
Oracle 19c VLDB and Partitioning Guide 第8章：Using Parallel Execution 读书笔记
Oracle Lob类型存储浅析
ciqu9915的博客
09-26 156
在Oracle中，为数据表字段column和PL/SQL语言，分别提供了多种数据类型，以应对实际开发中的多种类型。Lob类型是Oracle推出一种保存大对象的数据类型。当我们考虑将信息文件（十进制、二进制）、图像甚至音频信...
Oracle 9i轻松取得建表和索引的DDL语句
我思故我在，有我更精彩
02-09 1008
我们都知道在9i之前，要想获得建表和索引的语句是一件很麻烦的事。我们通常的做法都是通过export with rows=no来得到，但它的输出因为格式的问题并不能直接拿来用。而另一种方法就是写复杂的脚本来查询数据字典，但这对于一稍微复杂的对象，如IOT和嵌套表等，还是无法查到。　　从数据字典中获得DDL语句是经常要用的，特别是在系统升级/重建的时候。在Oracle 9i中，我们可以直接通过执行db
4.网络编程
最新发布
weixin_45476535的博客
09-14 379
程序注册端口：1024-49151。单个协议下，端口号不能冲突。公有端口0-1023。
SQL语句优化技巧
SQL语句优化是数据库性能调优的关键环节，尤其对于大型系统，高效的SQL查询可以显著提升数据处理速度。以下是对标题和描述中所述知识点的详细解释： 1. **建立索引**：索引是提升查询速度的有效手段，应在经常出现...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值