Hi,
I have created a GTT table(ON COMMIT PRESERVE ROWS) and mapped to Appian CDT, but the database session seems expired in couple of seconds. how to prevent the database session?
Thanks
Vinay
Discussion posts and replies are publicly visible
Hi Vinay, Having a Permanent table is a better solution. what I can suggest is have some kind of Identifier that marks the old records as invalid , so that you can avoid deleting them. As you are dealing with huge data set, the disabling can be achieved using stored Procedures. This approach is better from Audit perspective as well. Thanks & Regards Roopesh Rambhatla
Hello Vinay
Thanks for sharing your use case, Now I have some more questions.
- I guess is a table where you read and write? Does the already written values are updated later on?
- the table is huge but has some date, price or some numeric field on it?
- How does the user query the information?
- Is this an aggregation? Or is this just filtering ?
- The normal filtering includes a date range ? Can you offer the option to have day -1 ? Hour -1 ? Minute -5?
I could think of some options here. It just depends on the nature of the table the information and how the user query the same and the answer or the questions above but here I go:
- first thing are the indexes this is always the first thing to do. which is the normal way to improve, the most important thing to do is select the right index (maybe you already have this covered).
- Remove as much calculations and joins as possible for example have another mirror of that table/view precalculating them over night, update just the new delta values, maybe a trigger after insert/update or a query happening every x minutes/hours/days (OLAP vs OLTP) or just the materialized view ?
- Have you trace where the time is lost? Use something like
exec dbms_stats.gather_table_stats
Depending on the version or oracle you are using you might have different tools but analyzing the query is always a good option.
- I have seen to partition the table, by a date (the first partition the current month, the next partition the past month, and the last partition has the bunch more) if you query using dates this could help a lot. You can partition using any numeric value(including dates) this is used normally in billing systems, where you need the lastra information faster than historical but you still to query into the same table.
- And last but not least, others mentioned the option of copying the information to another table and expire it might help as well.
- Finally sometimes when managing big amount of data maybe an ETL can help instead of a stored procedure. And a note (I have seen) the smart service have a timeout around 20-30 minutes. After that it might continue executing the procedure but it doesn’t give response back to the node.
As suggested for others a materialized view or a permanent table would be my choice just having into consideration the answer of the questions above
I hope this helps
Jose