Home » SQL & PL/SQL » SQL & PL/SQL » Tunning Timestamp field
Tunning Timestamp field [message #33921] |
Wed, 10 November 2004 23:28 |
Rosa Quevedo
Messages: 5 Registered: November 2004
|
Junior Member |
|
|
Hello,
My problem is that I'm using a large amount of data, 60 million records, I'm accessing the data through a timestamp column, and I get a full access to the table so the response time is very high, and I need to reduce it.
I suggest that we separate the timestamp fiel into three different columns: date(dd/mm/yyyy), hour (hh24:mi:ss) and miliseconds and include each column in an index. I would appreciate any suggestions.
Thanks.
�
�
|
|
|
|
|
|
|
|
|
|
Re: Tuning Timestamp field [message #33943 is a reply to message #33940] |
Fri, 12 November 2004 00:16 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
Your original question was how to tune a query involving TIMESTAMP columns. These can be indexed, as shown below:
SQL*Plus: Release 9.2.0.1.0 - Developer's Release on Fri Nov 12 09:12:35 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> CREATE TABLE test_timestamp
2 ( t1 TIMESTAMP
3 , t2 TIMESTAMP
4 , othercol VARCHAR2(30) );
Table created.
SQL> INSERT INTO test_timestamp
2 SELECT SYSTIMESTAMP + NUMTODSINTERVAL(DBMS_RANDOM.VALUE(1,100), 'DAY')
3 , SYSTIMESTAMP + NUMTODSINTERVAL(DBMS_RANDOM.VALUE(1,100), 'DAY')
4 , object_name
5 FROM all_objects;
5957 rows created.
SQL> CREATE INDEX test_timestamp_ix1 ON test_timestamp (t1,t2);
Index created.
col t1 format a29
col t2 like t1
SQL> SELECT * FROM test_timestamp SAMPLE(0.1)
T1 T2 OTHERCOL
----------------------------- ----------------------------- ------------------------------
20-DEC-04 02.42.29.836148 AM 05-DEC-04 03.50.29.959139 AM EXU8VDPT
14-NOV-04 06.02.30.233150 AM 16-NOV-04 01.14.43.608106 PM I_STREAMS_CAPTURE_PROCESS2
16-DEC-04 12.53.00.198799 PM 15-FEB-05 04.16.57.021867 AM KU$_IOT_PARTOBJ_VIEW
30-JAN-05 05.30.09.243178 AM 14-JAN-05 09.04.18.484666 AM USER_EXTERNAL_TABLES
13-JAN-05 09.48.03.799501 AM 12-JAN-05 08.59.09.787997 AM USER_USTATS
5 rows selected.
SQL> SELECT COUNT(*)
2 FROM test_timestamp
3* WHERE TO_TIMESTAMP('08-DEC-2004','DD-MON-YYYY') BETWEEN t1 AND t2
SQL> /
COUNT(*)
----------
1124
1 row selected.
SQL> @<a href=http://www.williamrobertson.pwp.blueyonder.co.uk/code/xplan.sql.txt target=_blank>xplan</a>
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | | |
|* 2 | INDEX RANGE SCAN | TEST_TIMESTAMP_IX1 | | |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST_TIMESTAMP"."T2">=TO_TIMESTAMP('08-DEC-2004',:B1) AND
"TEST_TIMESTAMP"."T1"<=TO_TIMESTAMP('08-DEC-2004',:B2)
filter("TEST_TIMESTAMP"."T1"<=TO_TIMESTAMP('08-DEC-2004',:B1) AND
"TEST_TIMESTAMP"."T2">=TO_TIMESTAMP('08-DEC-2004',:B2))
Note: rule based optimization
You can use TO_CHAR() in the SELECT list to format things:
SQL> SELECT TO_CHAR(t1,'dd/mm/yyyy hh24:mi') AS t1
2 , TO_CHAR(t2,'dd/mm/yyyy hh24:mi') AS t2
3 FROM test_timestamp
4 WHERE t1 BETWEEN TO_TIMESTAMP('08-DEC-2004','DD-MON-YYYY')
5* AND TO_TIMESTAMP('08-DEC-2004 01','DD-MON-YYYY HH24')
T1 T2
----------------------------- -----------------------------
08/12/2004 00:05 24/12/2004 12:22
08/12/2004 00:14 24/01/2005 14:00
2 rows selected.
SQL> @xplan
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
|* 1 | INDEX RANGE SCAN | TEST_TIMESTAMP_IX1 | | |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST_TIMESTAMP"."T1">=TO_TIMESTAMP('08-DEC-2004',:B1) AND
"TEST_TIMESTAMP"."T1"<=TO_TIMESTAMP('08-DEC-2004 01',:B2)
|
|
|
|
Goto Forum:
Current Time: Sun Jan 12 02:20:48 CST 2025
|