You Asked
We have table as follow :
CREATE TABLE PRICE_DATA
( "ID" VARCHAR2(10 BYTE),
"TYPE" VARCHAR2(1 BYTE),
"P_DATE" DATE,
"VALUE" NUMBER(18,6),
"IS_CORRECTED" VARCHAR2(1 BYTE) DEFAULT 'N',
PRIMARY KEY ("ID", "TYPE", "P_DATE") ENABLE
)
with sample data as :
INSERT INTO PRICE_DATA VALUES ('GS','H',to_date('01-jan-2004'),100,'N');
INSERT INTO PRICE_DATA VALUES ('GS','L',to_date('01-jan-2004'),80,'N');
INSERT INTO PRICE_DATA VALUES ('GS','C',to_date('01-jan-2004'),90,'N');
INSERT INTO PRICE_DATA VALUES ('C','H',to_date('01-jan-2004'),200,'N');
INSERT INTO PRICE_DATA VALUES ('C','L',to_date('01-jan-2004'),180,'N');
INSERT INTO PRICE_DATA VALUES ('C','C',to_date('01-jan-2004'),190,'N');
COMMIT
This table currently stores more than 300M records and we add about 350K records daily into this table. This table store pricing data which is like a time series data for each day.
Currently, we use the following code to retrieve a data in XML format to send it to the upstream application.
SELECT DBMS_XMLGEN.getxml (
DBMS_XMLGEN.newcontext (
'SELECT ID,
TYPE,
TO_CHAR (P_DATE, ''YYYY-MM-DD HH24:MI:SS'') P_DATE,
VALUE,
IS_CORRECTED
FROM PRICE_DATA
WHERE ID in (''GS'',''C'')
AND ( ( P_DATE >=
TO_DATE (''20030101 00:00:00'', ''YYYYMMDD HH24:MI:SS'')
AND P_DATE <=
TO_DATE (''20040101 23:59:59'', ''YYYYMMDD HH24:MI:SS''))
)')) from dual
Currently, this code is taking about 2 to 3 seconds to generate this result. Is there anything we can do to improve the performance to send respond in milliseconds?
Is there any other way we can retrieve data faster than generating response XML?
We need to send a response either in XML or JSON so we are currently generating XML at DB layer. We are currently using Oracle 11g DB
and Chris said...
The first thing to do is find out how long it takes to process the underlying query and if you can get that to run any faster.
If the query takes 2-3 on it's own then changing how you generate your XML isn't going to make any difference!
So as a first step, get the execution plan for:
select id, type, to_char ( p_date, 'YYYY-MM-DD HH24:MI:SS' ) p_date,
value, is_corrected
from price_data
where id in ( 'GS','C' )
and ( ( p_date >= to_date ( '20030101 00:00:00', 'YYYYMMDD HH24:MI:SS' )
and p_date <= to_date ( '20040101 23:59:59', 'YYYYMMDD HH24:MI:SS' ) ) )
You can find instructions on how to do this at:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution Once you have this come back to us with your findings and we'll see what we can do.
If you're posting the plan make sure:
- You place it in < code > tags to preserve the formatting
- It is an
execution plan that includes stats like
E(stimated) rows, A(ctual) rows, buffers, time etc.