Oracle is known to be a very scalable database able to support tables with millions of rows.

Partitioning and compression are the 2 major features Oracle provides to handle really large tables.

Just curious...what is the size (no. of rows/MB) of the largest table out there? At some point, doesnt the darn thing just become too unmanageable?

Well, a table is not a segment only for partitioned tables.

In your 50B partitioned table example, unless all operations are done strictly on a per-partition basis (in which case its simply N tables with a UNION ALL view on them), there would still be global indexes, would there not?

Any kind of DDL/DML that would need to update the global index would take hours!

Also, any kind of FTS or hash join involving the full table would take hours.

By managable, I am referring more to the use of the table by apps, not so much the DBA aspect of it

Thanks

Tom, I'm concerning about item d) above in your first answer. Could you elaborate it a little bit in how to gtt comes to play into that fashion?

Thanks and regards,

can you please elaborate the use of b)
Create a view T as select<all
but the company_id> from t_table where company_id = sys_context( 'my-ctx',
'company-id');

thanks ,

Would not be better to use the FGAC instead of view approach in this case?

Thanks Tom for your valuable advice.

Regards,
Albert Nelson A.

I suggest you read both
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 http://asktom.oracle.com/~tkyte/article2/index.html <code>

And a little sample of playing with view and context.

ops$mportes@FCCUAT9I> create table t as
2 select user_id, username, account_status
3 from dba_users
4 /

Table created.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> create or replace context ctx using p;

Context created.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> create or replace procedure p( p_name in varchar2, p_value in varchar2 )
2 is
3 begin
4 dbms_session.set_context('ctx', p_name, p_value);
5 end;
6 /

Procedure created.

ops$mportes@FCCUAT9I> show error
No errors.
ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> create or replace view t_view
2 as
3 select username, account_status
4 from t
5 where account_status = sys_context('CTX', 'ACCOUNT_STATUS')
6 /

View created.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> select account_status, count(*)
2 from t
3 group by account_status
4 /

ACCOUNT_STATUS COUNT(*)
-------------------------------- -------------
EXPIRED 2
EXPIRED(GRACE) 5
OPEN 58

3 rows selected.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> select * from t_view;

no rows selected

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> exec p('account_status','EXPIRED')

PL/SQL procedure successfully completed.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> select * from t_view;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
OPUAT_READ EXPIRED
OPUAT_INDER EXPIRED

2 rows selected.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> exec p('account_status','EXPIRED(GRACE)')

PL/SQL procedure successfully completed.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> select * from t_view;

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
AKUMAR_DBA EXPIRED(GRACE)
BOCHC_READ EXPIRED(GRACE)
PYCHC_READ EXPIRED(GRACE)
OPCHCONTROL EXPIRED(GRACE)
PYCHCONTROL EXPIRED(GRACE)

5 rows selected.

rgds,
Marcio

Hi Tom,

If the requirment is to filter/restrict the data of a company with in its users than FGAC would be the Only Option. Am i correct ?


Thanks,

Vikas Sharma

Hi Tom

Refering to the first part of the question and its answer I would like to get a clarification. SInce we are adding company_id to all the tables in the application we natually have to add this key to the primary key as well. In this case is it advisable to have a surogate key and have unique constraint on the company_id and atual primary key?

Rgds
Anil

Tom ,

<<
totally depends on the nature of the primary
>>

yes tom it is depended on the primary key, but experiance shows that in most of the cases if you have a business code primary key it has to add with company id otherwise it will have conflict when other company uses the same key in their data. I have many cases in the application especially in the masters tables(base data) where we have to add company id to the primary key there by having composite PK. Since it is masters and not very tranasctional I think I can afford an extra index that would be created for the surrogate key!!

Also keeping company id on all the indexes at the leading edge will have any implication on the index access??. Will it be same as accessing the index without the leading edge column?


Rgds
Anil

What about the case when a query doesn't hit an index?

With all the data from 50 companies in one table these queries would seem to take much longer.

This might be compounded by the inability to customize indexes for a particular customer's favorite strange query.

The company_id issue reminds me the scenario in our work place. Currently we have data mart in its own LINUX box for a particular client. We extract 3 years worth of data from a data warehouse for that client and store it in the data mart. The client would run ad-hoc queries on the data mart for their reporting purposes. The size of the data mart is 1 TB.

The data warehouse is a single instance that stores data for 8 different clients on its own LINUX box.

Two more clients are signing up with our company to have a similar kind of data mart. The data is quite similar across differnt clients.

The debate is, do we need LINUX box for each data mart OR put all the data in one database and allow the client to access their respective information using client ID?

There are three things to be considerd, PERFORMANCE, HARDWARE EXPENSES and MAINTENANCE.

What is your thought on this?

Thanks

Well, I have experience putting all the information in a single instance which is a warehouse that does not get clogged due to SQL queries. However datamart does.

I agree your point that client A can use more CPU when client B is inactive. However, if both are active at the same time, then I don't know.

I think you kind of prefer to have all the client info in the same table (partitioned) and within the same database.

What if the size of each client is 5TB and the number of clients is 8?

Thanks,

We had the same problem in deciding between schemas and partitions, we tried the schema for each company and it was a total nightmare. Never even try that. We stuck to partitions and VPD since we had other security concerns.

The reason we went to the schema approach is because of some great so called gurus who recommended the management about the approach who wouldnt listen to us. After burning their fingers they had settle for the nicer approach.

We did it in 8.1.7, and I didnt know much about Tom then, and wasted 4 months trying some nut's ideas.

Regarding the question raised by sns from autin.tx …

Assuming one would go for the partitioned-by-client data mart on one single box … would you not change the mechanism of the views?

Rather than one set of views like “where company_id = sys_context()” … have n set of views like “where company_id = <literal>”.

Different login schemas for the n customers on the box, each with their own relevant set of views … bit more management of the objects but no issues with the binds. For OLTP the sys_context() being a bind is perfect, as you mentioned, smaller shared pool needs, less hard parses, etc. … for the DM those are not as much of an issue … the potential (though remote) problem I see with sys_context() is bind peeking for customer A who has substantially less [or more] data than customer B, etc. …

How remote of a possibility? Since they rent/host the DM data they may decide to rent/host an analytical app on top of it as well (talk about competitive advantage). Maybe … do you think is it worth avoiding the sys_context() for DM?

Thanks.

Thanks.

Actually, the bind peeking caveat would be an issue to consider even for multi-customer OLTP apps.

Typically I see issues related to bind peeking for Search functionality (frequently invoked but going against large data sets). The other two categories of functionality:
a. Discrete Selects (frequently used but getting discrete data sets)
b. Operational Reports (un-frequently used but shifting through large data sets)
are manageable: definitely binds for a) … easier to make a case-by-case choice between binds or literals for b)

A multi-customer OLTP app with disproportionate customer data volumes would exacerbate the bind peeking issue. So for the original case presented in the thread (existing app with 100 tables, some of them with millions of records added per day) I hope nobody would just follow the steps you listed, maybe increase the buffer cache, etc., deploy and just add another brand new customer hoping everything will work just the same. The technique is definitely sound but thorough scalability testing would be paramount.

I’m starting to have some misgivings about incrementally adding customers especially when the application was built just for one. Even when built from the ground up as multi-customer, the disproportionate data volume would be something the seriously consider. I don’t like the separate schema for each customer within the same database solution … budget permitting, I would rather have a separate box for each customer (those costs would typically be passed onto the customer anyway) and only put few customers on the same box when safe to do so.

Hope it makes sense.

Thanks Gabe and Tom for your inputs.
I was just not sure how oracle handles large volume of data in the data mart scenario. Views, SYS_CONTEXT are facilities to capture the data when we have more than one client within the same table.

I haven't seen Oracle database bigger than 10TB so far. I am quite sure the way oracle handles multiple queries in a pretty big DB.

I have heard in a company they replaced oracle by Teradata since their size is now up to 25TB. Lot of end users query the database and they felt oracle is not quite capable for such a large database.

I still don't believe them but I cannot argue with them too.

Anyway, oracle is great.

<quote>
OLTP is characterized by queries returning predicable, small sets of data -- I would hope they would follow what I said above.
</quote>

Yes … absolutely. As I said the technique you presented is definitely sound and it would be my first choice to consider. Hope my comments were not interpreted otherwise … only wanted to flesh out some of the potential issues/risks:
1. bind peeking with disproportionate customer volumes
2. thorough scalability testing paramount
3. risks associated with having too many eggs in the same basket

I realize those are “duh” kind of things … still deemed they were worth mentioning.

Cheers.

Tom - back to the question about a schema originally produced for a single company being enhanced to cater for multiple companies.
What if the companies have their own business rules that need to be reflected in the database? Would your recommendation then be to move to separate schemas?

Tom - let's say you have a housing landlord management system that has many different tables - people, tenancies, transactions (rent raised weekly, payment received, adjustments), property details etc. The scenario would be that you want to split this portfolio of properties into separate groups and allow these to be mangaged on your behalf by different organisations whilst you still retain ownership of the properties. Each of these organisations may have different policies to deal with ,say, people falling into arrears with their rent payments. The separate organisations would also need to be able to 'see' only their data, whilst you would need to be able to look at the complete database.

Tom - I'll keep plugging away... There will still be a requirement to report on performance indicators like tenancy turnover, arrears figures, number and value of repairs etc. across the whole portfolio. As legislation and management requirements change so will the performance indicators.

I am thinking a VPD is what that guy wants, just restrict the data they can see via a code or something.

Then he can look at all the data in one query

What about the business rules? I think your reader wanted to implement different business rules in the schema related to the different organizations.

I think I'm missing something.

Let's say you have an application with 10 tables. You add a column to each, eg company id. your context is set:

procedure set_company(p_company_id in number)
as
l_ctx varchar2(255) default 'my_Ctx';
begin
dbms_session.set_context(l_ctx, 'company_id', p_company_id);
end;


and your predicate is (simplified):

function policy_function
(p_schema in varchar2,
p_object in varchar2)
return varchar2
as
l_app_ctx varchar2(30) default 'my_Ctx';
l_predicate varchar2(1024) default NULL;

begin

l_predicate := 'company_id = sys_context('''||l_app_ctx||''',''company_id'')';

return l_predicate;

end;

You add a this same policy for each table, for inserts updates, selects and deletes.

What's the best way to handle inserts? Do all of the inserts need to go back and be changed to include sys_context...etc?

The app is already in-place and I've been ask to "make it work" for many other companies...it's in HMLDB, if that matters.

Am I doing this all wrong?

Thanks,

Not sure what I was doing incorrectly before. I had tried the trigger, but was still getting the policy violation error. Things are working well now. Thanks

Tom,

Thanks a lot for this one. It is very helpful. However our team has the following issues

1. What are the extras for FGAC that you refer here ?
2. We have a need in our research database (not a huge database where some information need to be separated by the research study). Only about 10 tables need to be restricted, others can be kept open. In this case, would you recommend FGAC or this approach with one view per those tables.
Given the following
- We don't have to deal with terabytes of data
- No need for partitions

Since the team could not figure out the costs by identifying the extras and also more programmer work for this approach, FGAC seems a faster reliable way to them. Your commments please.

The number of studies could grow to say a 10 in the next couple of years. The predicate is simple like in this case here.
Thanks

Tom,

I was refering to one of your answers. I have pasted it below. Could you help us understand better what are the costs of doing FGAC. An earlier question and answer below.

"Reviewer: Dushan from Czech rep.
Would not be better to use the FGAC instead of view approach in this case?

Followup:
not for something "so simple", the predicate is constant here
where company_id = sys_context()

if the predicate is different frequently, it would be FGAC. But for something
so simple, you don't need the extra's that FGAC has. " What are these extra's being referred here?
Thanks

Hello Tom.

Here's another variant: We're in a purchased-COTS situation where each client has an exact copy of the same complete database structure. Client-A has an instance, Client-B has an instance, Client-C etc. So there's dozens of cookie-cutted instances, and most of them are small, < one or two gig. Conceptually easy, but the maintenance overhead is NOT. Oh, and the COTS is one of those that has the table relationships dynamically embedded within the application - we can't create our own tables, can't modify the tables (like add a Client_ID column), indexes, etc.

So... my Cunning Plan (semi-formed) is to explore combining these clients - one at a time - into a single instance, as different schema owners, with separate LMTs. Do you think it's feasible? This scenario has been discussed above, but what's "better" (yeah, I know I shouldn't use that term): Mutiple schemas or multiple instances? Your thoughts and insights would be appreciated.

Thanks and Regards.

Would you say the advise given to the original poster is still valid if his system serviced 1000s or 10,000s companies?

Dear Tom,

How to do if all companies are in one single schema and I want to create a copy of one of them ?

Is there a possibility to do that if all PKs are referenced by FKs without disabling constraints during this process or writing a very very long script ?

Thanks a lot

Dear Tom,

My question is pretty much the same as the one asked right at the beginning of this thread - just want to check if the answer is any different 9 years later :-)


Your advice would be much appreciated for the following:

There are about 260 tables in a schema presently.
All tables are refreshed on a weekly basis.
Some tables are huge tables with about 50 million rows inserted per week.
The application which was developed to cater for one company is now being enhanced to support more than one company (might go upto 45 companies).

To achieve this we have three choices:
Approach 1: Include additional column called company_id in all the tables. Potentially list partition on company_id to be able to selectively truncate/load data for a

specific company
- Pros: Maintenance is easy; if I need to change an attribute, I will have to change it at only once place
- Cons: the performance can go out of the window; especially on large tables - especially if I have some BI reports running on top of large tables
- Cons: my ETL codes will be a little complex

Approach 2: Create separate schemas for each company with the same table structures.
- Pros: ETL jobs will be easy; schema creation is easy
- Cons: Maintenance will be a nightmare (to say the least)


Approach 3: Does the multitenancy feature in 12c has something new to offer? I have read about CDBs and PDBs but I dont see them as a better alternative to 'Approach 1'. Would love to stand corrected. Does 12c have a silver bullet to solve this flavor of "multi-tenancy" problem?

Thank you very much in advance, Tom.

Warm regards,
Apurva