| 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: Tunning Timestamp field [message #33922 is a reply to message #33921] |
Thu, 11 November 2004 01:08  |
Fcerezo
Messages: 28
Registered: August 2004
|
Junior Member |
|
|
What are the querys that you do?
Select * from tab where timestamp = ...
Select * from tab where timestamp between ...
Select * from tab where trunc(timestamp) =
...
It's up the query you do, youŽd choose a different optimization.
|
|
|
|
| Re: Tunning Timestamp field [message #33925 is a reply to message #33921] |
Thu, 11 November 2004 02:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Don't split the column. Index it. If you have a NAME-column you don't split it either (1 column containing the first 3 letters, another containing the next 3), so WHY would you do it with a timestamp column ???
If you only use the time-part in your where-clause, you could either create an EXTRA column, containing only the time-part (or whatever you need), or create a function-based index on the to_date(to_char(timestamp_column, 'hh24:mi'), 'hh24:mi') part.
I would leave the timestamp column itself alone.
hth
|
|
|
|
| Re: Tunning Timestamp field [message #33928 is a reply to message #33922] |
Thu, 11 November 2004 04:26 |
Rosa Quevedo
Messages: 5
Registered: November 2004
|
Junior Member |
|
|
The second one:
"Select * from tab where timestamp between a and b"
and either a and b has the format 'dd/mm/yyyy hh24:mi'.
I wonder if I include the timestamp field in an index it'd reduce the response time.
|
|
|
|
| Re: Tunning Timestamp field [message #33929 is a reply to message #33925] |
Thu, 11 November 2004 04:32 |
Rosa Quevedo
Messages: 5
Registered: November 2004
|
Junior Member |
|
|
|
Ok, another possibility I'm thinking about, it's to convert the timestamp field into a number field so that I could do the querys between two dates, but if I include it in an index I suppose the index whould be very large and it will not reduce enough the response time
|
|
|
|
| Re: Tuning Timestamp field [message #33933 is a reply to message #33928] |
Thu, 11 November 2004 08:18 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> I wonder if I include the timestamp field in an index it'd reduce the response time.
If that is a typical query, yes!
> "Select * from tab where timestamp between a and b";
>
> and either a and b has the format 'dd/mm/yyyy hh24:mi'.
This worries me. a and b need to be the same datatype as timestamp, not some string with an arbitrary format. Why aren't they? This looks like the root of your tuning problem.
|
|
|
|
|
|
| Re: Tuning Timestamp field [message #33940 is a reply to message #33933] |
Thu, 11 November 2004 22:59 |
Rosa
Messages: 1
Registered: November 2004
|
Junior Member |
|
|
|
The idea of the query is to obtain statistics about the operations between two dates, the original data have the timestamp format but the results must have 'dd/mm/yyyy hh24:mi' format.
|
|
|
|
| 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)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
