Home » Other » General » Advice for design tables (Oracle12c)
Advice for design tables [message #643372] Tue, 06 October 2015 07:21 Go to next message
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 #643375 is a reply to message #643372] Tue, 06 October 2015 07:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do the two search tables really need to exist?
What exact data do they hold?
Re: Advice for design tables [message #643376 is a reply to message #643375] Tue, 06 October 2015 07:31 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Yes, two table need to exist.
The most Type of data are varchar2 and Number and each product has a image,also the storage space after delete old data should be reclaim like purge command.

[Updated on: Tue, 06 October 2015 07:32]

Report message to a moderator

Re: Advice for design tables [message #643381 is a reply to message #643376] Tue, 06 October 2015 08:12 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #643443 is a reply to message #643401] Wed, 07 October 2015 09:46 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:
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.


Users have a light local database (for example Access Database) for their offline view, this is like reporting of products.
It isn't right that create another database (SQL Server) just for resolving this issue.
If you please, introduce the best solution in Oracle (even if it is not better than existing solutions in SQL Server) to solve this issue.
Re: Advice for design tables [message #643444 is a reply to message #643443] Wed, 07 October 2015 10:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #643445 is a reply to message #643443] Wed, 07 October 2015 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
fabi88 wrote on Wed, 07 October 2015 07:46
Quote:
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.


Users have a light local database (for example Access Database) for their offline view, this is like reporting of products.
It isn't right that create another database (SQL Server) just for resolving this issue.
If you please, introduce the best solution in Oracle (even if it is not better than existing solutions in SQL Server) to solve this issue.


It is not possible to offer any solution without knowing all the requirements.
How will we know when correct solution has been posted here?

>introduce the best solution in Oracle
which metric measures best?
Re: Advice for design tables [message #643446 is a reply to message #643443] Wed, 07 October 2015 10:11 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #643456 is a reply to message #643372] Wed, 07 October 2015 14:53 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Quote:

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?

Because users send their product information, then some statistical information (based on product information of users and those products which are stored in server database) should be calculated and stored in the table "SearchResults", after that users can see search results by paging on table "SearchResults".

Quote:
It is not possible to offer any solution without knowing all the requirements.
How will we know when correct solution has been posted here?

Yes, you are right, it would be difficult to advise without knowing all the requirements. I wanted to be aware of best solutions and I will provide feedback here based on results gained.


Quote:
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.

This issue is only part of the structure Oracle database, not all of it.
Re: Advice for design tables [message #643458 is a reply to message #643456] Wed, 07 October 2015 18:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I wanted to be aware of best solutions
which metric measures best?

If there was a single one size fits every requirement, then there would be no choices, options or alternatives offered by Oracle.

How will we know when correct solution has been posted here?
Re: Advice for design tables [message #643540 is a reply to message #643372] Sat, 10 October 2015 04:19 Go to previous message
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.

Previous Topic: Oracle DBA - Daily Activities
Next Topic: unable to use data access control (oradc.ocx) with VB6
Goto Forum:
  


Current Time: Thu Mar 28 13:34:25 CDT 2024