Performace Tables EBS

[DanielNN]

Hello,
Since when I started working with EBS I began to surprise the fact that Oracle does not like keys (primarias, foreign, etc.). She works with indexes.
I know that when you create a key, a single index is created.
1-But at the end of accounts Performace is the same (use only indices or keys) ??
2-In the question of information integrity, would not it be safer (and less laborious) Oracle working with keys? Or this lack of keys is to contemplate (being more dynamic) the various deployments worldwide ??

[stcoutinho]

Noctiferous,

I had exactly the same doubts as when I started working with EBS 11.5.9, about four years ago. How does a database manufacturer company have the courage to have an ERP that uses the Oracle database without PK, fks, etc? Would something like "do what I say, do not do what I do?"

has my colleagues who make jokes "malicious" about the lack of these constraints, saying - with this - Oracle errors in EBS are masked, avoiding the opening of a huge volume of calls In Metalink.

So ... I believe the main reason for Oracle does not create PKs and fks in your ERP database is to prevent people from knowing the EBS business model. It would not be exactly to avoid "performance problems."

In other words, they do not put pks, fks and CKS why they did not want a competitor (and even a client) discover the relationship between the EBS data model tables. With constraints, it would be possible for a competitor to discover exactly the relationship / dependencies between the tables and make an ERP data model similar to Oracle. For a user, it would be possible for him to make updates more easily in ERP, which could then generate errors in the application. It is logical that in this case the user will not reveal in the Metalink call that "put the hand" in the tables, which can make it practically impossible for Oracle to understand if the error is due to the product or an undue action of the user.

I believe until Other Oracle competitors adopt a strategy similar to Oracle. I do not know SAP ERP, but I have already told me that the names of the tables and columns seem "Greek" for a layman. In them, you as a layman, "battery the eye" and would not know how to serve the table or column. And I suspect that in the Base of SAP there is no PKs and FKs.

This same thing happens with Oracle's EBS Libraries / Packages. Many basic EBS (FND) packages feature your encrypted text to prevent people from knowing the bottom as they work. If they were decrypts, a competitor could make an ERP similar to EBS. Or a user could change their functionality for their needs, but committing EBS integrity.

Now, returning to your initial question, I find it essential a database to present PKs, fks, cks, etc. He has a lot of developer who "twist their nose" for this, saying "the application guarantees the integrity of the bank." I personally never believed in this bored conversation because a code can be developed in the wrong way, a maintenance can deploy a defective component and, a malicious user can make database horrors available to a password and a SQL * plus .

And the worst is that a failure can be detected only months after it has been created. And then imagine "who's left" try to clean the damage "done. Yes, it's for the DBA that about this cleaning! And sometimes the damage is so big that you can not hit it, and you get an integrity bank" Capenga. "And in these hours, someone will always appear with the criticism that" .... because the "fucking" of the DBA did not put it before this constraint on the base!?!? .... ". / CK / FK / Unique) is the "Last Trench / Resistance" in maintaining the integrity of the information stored in a database. In the bank, the rules (PK / FK, CK) become permanent (unless someone changes or drop the rule) and are proof of defective codes or poorly intentioned users.

Another point for the reason that developers do not like pks / fks and cks is that the bank responds at the time When a poorly elaborated code attempts to record incorrect data at the base. Without these constraints, the defective code "Wheel that is a marvel". Or the developers do not like the constraints why this makes it difficult for test mass loads.

But at the end, it is not one of the objectives of a system (application + bank) maintain the integrity of user information Ario? You can not wish the two worlds (total freedom x limited), right?

Maybe the other participants in this forum can give other answers to your questions, even opposite my point of view (maybe a little radical).

But this is a forum, and one of his goals is the exchange of ideas and experiences.

Hugs!

Sergio Coutinho

[DanielNN]

Opa Sérgio,
I liked its elaborate response.
I really had already cited to a friend that Oracle spends 10% of the time to develop one thing and the other 90% to mask and hamper access to information.
Now you Lenvantou the famous flag (DBA X developer) Heheheheheheh.
I am a developer, but I always worry about normalization and the business rule to get as much as possible.
Type here in the company they made a system that the user had to inform the date in a field and time in another, but the time field had no validation beyond a javascript malfect to put in format "00:00".
But I can insert letters or "99:99" for example.
This has said not to create constraints to facilitate the tests, found it funny. After all you should try to do tests with more possible information to validate the business. Finally, there are good developers and DBAs and the "not so good."
Type When I step some objects to be placed in production, it was supposed to be put and nothing.
I speak with the DBA, he "no, everything is ok. Test there again, must be cache ...". When I get back magically (done at that time) and I'd like it.
But this intrigue is for another post. : D.

[dr_gori]

Well, in my opinion, one of the reasons of not having constraints *** in some cases *** is the issue of Flex Fields.
As virtually all tables have Flex Fields, it is impossible to have constraints in the bank because the rule is defined in runtime.

Both descripting Flex Fields and Key Flex Fields make the use of constraints.

: - /

[stcoutinho]

Um ...


was consulting a forum today and I found that - although there are no PK / FK in EBS tables - the product has FND tables where these relationship between tables are described: [99]

FND_PRIMARY_KEY (Logical PKS interface)
FND_PRIMARY_KEY_COLUMNS (columns that make up each PK)

FND_FOREIGN_KEY (FKS Ratio " Logics ")
FND_FOREIGN_KEY_COLUMNS (columns that make up each FK)

for the FLEXFIELD fields, there are also some descriptive tables:

FND_DESCRIPTIVE_FLEXS
FND_DESCR_FLEX_COLUMN_USAGES

Be surprised to find that these tables already exist from EBS 11.5.9. I will try to search to see if I can extract scripts "DDL" from these constraints, or at least extract relationship information in a more user-friendly way.

Hugs,

Sergio Coutinho

[stcoutinho]

Just to leave registered,

I made some queries for PK / FK information from EBS tables:

Select all

-- Obtendo as PKs das tabelas 
SELECT TBO.TABLE_NAME, 
       TBO.DESCRIPTION, 
       PK.PRIMARY_KEY_NAME 
FROM        
(SELECT APPLICATION_ID, 
        TABLE_ID, 
        TABLE_NAME, 
        DESCRIPTION 
   FROM FND_TABLES) TBO, 
(SELECT APPLICATION_ID, 
        TABLE_ID, 
        PRIMARY_KEY_NAME 
   FROM FND_PRIMARY_KEYS) PK 
WHERE PK.TABLE_ID = TBO.TABLE_ID 
  AND PK.APPLICATION_ID = TBO.APPLICATION_ID 
ORDER BY TBO.TABLE_NAME 
 
-- Obtendo as FKs das tabelas 
SELECT TBO.TABLE_NAME, 
       TBO.DESCRIPTION, 
       FK.FOREIGN_KEY_NAME 
FROM        
(SELECT APPLICATION_ID, 
        TABLE_ID, 
        TABLE_NAME, 
        DESCRIPTION 
   FROM FND_TABLES) TBO, 
(SELECT APPLICATION_ID, 
        TABLE_ID, 
        FOREIGN_KEY_NAME 
   FROM FND_FOREIGN_KEYS) FK 
WHERE FK.TABLE_ID = TBO.TABLE_ID 
  AND FK.APPLICATION_ID = TBO.APPLICATION_ID 
ORDER BY TBO.TABLE_NAME, 
         FK.FOREIGN_KEY_NAME 
 
-- Obtendo as FKs/Pks das Tabelas 
SELECT TB_FK.TABLE_NAME_FK, 
       FK.FOREIGN_KEY_NAME, 
       PK.PRIMARY_KEY_NAME, 
       TB_PK.TABLE_NAME_PK        
FROM        
(SELECT APPLICATION_ID, 
        TABLE_ID AS TABLE_ID_PK, 
        PRIMARY_KEY_ID, 
        PRIMARY_KEY_NAME 
   FROM FND_PRIMARY_KEYS) PK, 
(SELECT PRIMARY_KEY_APPLICATION_ID, 
        TABLE_ID AS TABLE_ID_FK, 
        PRIMARY_KEY_ID, 
        FOREIGN_KEY_NAME 
   FROM FND_FOREIGN_KEYS) FK, 
(SELECT APPLICATION_ID, 
        TABLE_ID, 
        TABLE_NAME AS TABLE_NAME_PK, 
        DESCRIPTION 
   FROM FND_TABLES) TB_PK, 
(SELECT APPLICATION_ID, 
        TABLE_ID,  
        TABLE_NAME AS TABLE_NAME_FK, 
        DESCRIPTION 
   FROM FND_TABLES) TB_FK 
WHERE FK.PRIMARY_KEY_ID = PK.PRIMARY_KEY_ID 
  AND FK.PRIMARY_KEY_APPLICATION_ID = PK.APPLICATION_ID 
  AND PK.TABLE_ID_PK = TB_PK.TABLE_ID 
  AND FK.TABLE_ID_FK = TB_FK.TABLE_ID   
ORDER BY 3,2

You can do much more with the EBS FND tables. But stay here as an example of this possibility of extracting the EBS PK / FK. I do not know if the product uses these tables for some logical consistency of ERP information.

Hugs,

Sergio Coutinho

[DanielNN]

Sensational Sérgio,

Somehow the product uses this information.
I fucked a little here and added the columns involved in PK's.

Select all

SELECT TBO.TABLE_NAME, 
       TBO.DESCRIPTION, 
       PK.PRIMARY_KEY_NAME, 
       COL.COLUMN_NAME, 
       COL.DESCRIPTION COL_DESCRIPTION 
  FROM FND_TABLES              TBO, 
       FND_PRIMARY_KEYS        PK, 
       FND_PRIMARY_KEY_COLUMNS PK_COL, 
       FND_COLUMNS             COL 
 WHERE PK.TABLE_ID = TBO.TABLE_ID 
   AND PK.APPLICATION_ID = TBO.APPLICATION_ID 
   AND PK_COL.PRIMARY_KEY_ID = PK.PRIMARY_KEY_ID 
   AND PK_COL.COLUMN_ID      = COL.COLUMN_ID 
   AND TBO.TABLE_NAME         = 'PO_LINES_ALL' 
 ORDER BY 1,3,4;

Here also goes the structure of the table with the differential of the field description (which is rare to find in the table itself) and the use of the same in FlexFields:

Select all

SELECT TBO.TABLE_NAME, 
       TBO.DESCRIPTION, 
       COL.COLUMN_NAME, 
       COL.DESCRIPTION COL_DESCRIPTION, 
       COL.COLUMN_TYPE, 
       COL.NULL_ALLOWED_FLAG, 
       COL.WIDTH, 
       COL.FLEXFIELD_USAGE_CODE, 
       COL.FLEXFIELD_NAME 
  FROM FND_TABLES              TBO, 
       FND_COLUMNS             COL 
 WHERE TBO.TABLE_ID             = COL.TABLE_ID 
   AND TBO.APPLICATION_ID       = COL.APPLICATION_ID 
   AND TBO.TABLE_NAME           = 'PO_LINES_ALL' 
 ORDER BY 1,3;

[stcoutinho]

Oops noocifero!

very good your queries. They will help me in a survey I'm doing.

What surprises me is that the comments of the tables and columns are stored at FND_Tables or FND_COLUMNS, rather than the bank's data dictionary.

I am not right, but I suspect that in EBS must have a module in the system administrator, where you can consult this information about the tables. Maybe he looks for this information in these FND tables.

Hugs and all the best!

Sergio Coutinho

[dr_gori]

These queries go there for the topic "useful questions for Oracle Applications". heheheh
Done
Very good!

[dr_gori]

I joined the 2 query of the NOCTIFERO in 1 only ...
Below:

Select all

SELECT TBO.TABLE_NAME, 
       TBO.DESCRIPTION, 
       COL.COLUMN_NAME, 
       PK.PRIMARY_KEY_NAME,        
       COL.DESCRIPTION COL_DESCRIPTION, 
       COL.COLUMN_TYPE, 
       COL.NULL_ALLOWED_FLAG, 
       COL.WIDTH, 
       COL.FLEXFIELD_USAGE_CODE, 
       COL.FLEXFIELD_NAME 
  FROM FND_TABLES              TBO, 
       FND_COLUMNS             COL, 
       FND_PRIMARY_KEYS        PK, 
       FND_PRIMARY_KEY_COLUMNS PK_COL 
WHERE TBO.TABLE_ID             = COL.TABLE_ID 
   AND TBO.APPLICATION_ID       = COL.APPLICATION_ID 
   AND TBO.TABLE_NAME           = 'PO_LINES_ALL' 
   AND PK_COL.PRIMARY_KEY_ID = PK.PRIMARY_KEY_ID(+) 
   AND PK_COL.COLUMN_ID(+)       = COL.COLUMN_ID 
ORDER BY 1,column_sequence; 

: -O

[DanielNN]

Nice.
I saved this tb here for me. : D.