| Advice for design tables [message #643372] |
Tue, 06 October 2015 07:21  |
 |
fabi88
Messages: 112
Registered: November 2011
|
Senior Member |
|
|
Hi, Could you introduce best solution for following issue please?
There are four table: "ProductsInformation","SearchProduct" ,"SearchResults" and "Users".
* Table "Users" uses for storing users information.
* Table "ProductsInformation" uses for storing all inforamtion about products.
* Table "SearchProducts" uses for storing inforamtion of product that a user wants to search it between all product in "ProductInformations".(UserID also save in this table)
* Table "SearchResults" uses for storing search result.(one product may be matched with n
product, so these will store in this table.)
There are multiple concurrent user ( for example 50) who want to search products and get search result about that.
The scenario is that when a user send a new request for search, all previous records in both table "SearchProducts" and "SearchResults" which are related to the user should be
removed(huge data shoud be removed and after that huge data should be inserted). Those data which are related to other users should be kept until they send a new request.
So there is a huge cocurrency in delete and insert command on two table ("SearchProducts" and "SearchResults").also delete operatin should be done in the least time.(old data are not important), for instance truncate table.(but we can not truncate table because data which are related to other users should be kept)
what is the best solution for resolving this issue?
|
|
|
|
|
|
|
|
| Re: Advice for design tables [message #643381 is a reply to message #643376] |
Tue, 06 October 2015 08:12  |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
This sounds like a SQL Server type of solution to a problem: using "temporary" tables. You don't usually do things that way in a Oracle database but if you have to, you should use Global Temporary Tables.
|
|
|
|
| Re: Advice for design tables [message #643382 is a reply to message #643381] |
Tue, 06 October 2015 08:17 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As John says this sounds like a SQL Server approach. Having a table (temporary or otherwise) for search results is almost never necessary.
Have you actually tried just getting the search results from a query when requested and not storing them?
|
|
|
|
| Re: Advice for design tables [message #643388 is a reply to message #643381] |
Tue, 06 October 2015 16:06 |
|
fabi88
Messages: 112
Registered: November 2011
|
Senior Member |
|
|
Thank you for your reply Mr.John Watson and cookiemonster,
Quote:This sounds like a SQL Server type of solution to a problem: using "temporary" tables. You don't usually do things that way in a Oracle database but if you have to, you should use Global Temporary Tables.
Yes, we have to use oracle databaseŁ I should try your proposed approach, thank you.(Although I'm not too familiar with it, but I think it has problems such as cannot see the temporary table rows for a specific user session and etc).
is there other solutions other than Global Temporary Tables? for instance using partitioning, for example we put record of each user in separate partitions.(I don't no that it is possible in oracle or not), and truncate or delete each partition individually?(or a solution like this)
Quote:Have you actually tried just getting the search results from a query when requested and not storing them?
Yes, because after completing whole search on server database(That is the oracle database), user can see all search result and then fetch a portion of total search results( by filtering) and export it to their local database.
If Oracle has other solutions for this issue, I am grateful for introducing it to me.
|
|
|
|
| Re: Advice for design tables [message #643401 is a reply to message #643388] |
Wed, 07 October 2015 01:25 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:Yes, because after completing whole search on server database(That is the oracle database), user can see all search result and then fetch a portion of total search results( by filtering) and export it to their local database.
If Oracle has other solutions for this issue, I am grateful for introducing it to me. Why do your users need "local" databases? This is so like SQL Server-type design. as I said, you don't do it that way with Oracle. Just run all the queries against the one database. Forget this "export" entirely.
|
|
|
|
|
|
| Re: Advice for design tables [message #643444 is a reply to message #643443] |
Wed, 07 October 2015 10:00 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Why can't you populate the access DB's with the result of a query against ProductsInformation?
Why do you need to store the results in a separate table before shunting it over?
|
|
|
|
|
|
| Re: Advice for design tables [message #643446 is a reply to message #643443] |
Wed, 07 October 2015 10:11 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The Oracle solution (which is superior in many ways to any other architecture) is to store the data in one place only, and have your users query it directly.
Your idea of creating copies of data is only necessary for cheaper, less capable, products.
|
|
|
|
|
|
|
|
| Re: Advice for design tables [message #643540 is a reply to message #643372] |
Sat, 10 October 2015 04:19 |
|
fabi88
Messages: 112
Registered: November 2011
|
Senior Member |
|
|
Hi,
I read many article about partition table which we can truncate it with drop storage:
Quote:ALTER TABLE tablename TRUNCATE PARTITION partitionname CASCADE UPDATE INDEXES;
So by putting each user data on a separate partition, then it can be truncate individually,also it should be use Partition By Reference on child tables for cascading truncate.I think that this is a good way for resolving the issue.
Thanks to all for your attention,track and guidance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
