OraFAQ Forum: SQL & PL/SQL » Table Design Consideration

Home » SQL & PL/SQL » SQL & PL/SQL » Table Design Consideration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table Design Consideration [message #616834]

Sat, 21 June 2014 05:20

VIP2013
Messages: 91
Registered: June 2013

Member

I am using version of oracle

I am having a requirement for which i have to design the tables structures which will best suits current design need and considering future data growth.. etc
We are planning to store possible performance metrics for application data, which will help in analysing the future trend and take necessary action, and we should be able to easily extract report out of this repository. data collection frequency will by once daily.

--We are planning all to store in a new schema(lets say perf_schema) in existing prod DB .
--Will create procedure which will basically 'INSERT INTO PERF_SCHEMA.TABLE .... SELECT FROM TRANSACTION_TABLE...WHERE..' for each of the metric.

--Performance metrics might be like 1)no Of user logins 2)No Of transactions done by ach user for that day 3)total accounts 4)total organisation.. etc ,currently we dont have all the metrics in place(some might be like , their value will not be in number datatype and we may store someother values) but we will keep on adding them as and when they will be established to be required metric, and expecting the design to be supportive.

-- So i am planning to create two table

Table "METRIC_DESC" which will store 1)metric_name VARCHAR type 2)its description VARCHAR type
Table "METRIC_DATA" which will have two columns 1)Create_date Timestamp 2)METRIC_NAME VARCHAR column 3) VALUE VARCHAR Type, it will be of VARCHAR type and can store any type of data(NUMBER/VARCHAR.. etc), but i am bit worried if during fetching values and putting 'like' clause on this column will impact the performance.
Table will be list partitioned on METRIC_NAME and Interval partitioned on Create_date. Global index on METRIC_NAME and Local Index on create_date.
Currently no such scenario, but i am thinking if ther will be any subcategorization of Metric_name reuired in future also those will be part of METRIC_DESC table, which will not be bulky.

I neeed expert advice, if it is fine, considering above requirement or is there some other better approach for this? I dont know if materialized view suits this scenario..

[Updated on: Sat, 21 June 2014 05:21]

Report message to a moderator

Re: Table Design Consideration [message #616847 is a reply to message #616834]

Sat, 21 June 2014 08:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Table "METRIC_DATA" which will have two columns 1)Create_date Timestamp 2)METRIC_NAME VARCHAR column 3) VALUE VARCHAR Type,
somebody should learn how to count before attempting to design RDBMS schemas.

data should be designed to Third Normal Form.

IMO, this task appears to have been conceived by somebody who has never actually tuned an Oracle database or application.


  1* select table_name from dba_tables where table_name like '%HIST%' order by 1
SQL> /

TABLE_NAME
------------------------------
APEX$_WS_HISTORY
APEX$_WS_WEBPG_SECTION_HISTORY
DIR$ALERT_HISTORY
EMPHISTORY
FINALHIST$
HISTGRM$
HISTORY
HIST_HEAD$
HS$_PARALLEL_HISTOGRAM_DATA
JOB_HISTORY
JOB_HISTORY
JOB_HISTORY
JOB_HISTORY
LOGSTDBY$HISTORY
MGMT_BLACKOUT_HISTORY
MGMT_ECM_HOSTPATCH_COMPL_HIST
MGMT_ECM_MD_HIST_TBLS
MGMT_HISTORY
MGMT_HISTORY_SQL
MGMT_JOB_HISTORY
MGMT_PROV_HISTORY
MGMT_SL_METRICS_HISTORY
MGMT_SL_RULES_HISTORY
MGMT_STRING_METRIC_HISTORY
OEHR_JOB_HISTORY
OLAPI_HISTORY
OLAPI_IFACE_OBJECT_HISTORY
OLAPI_IFACE_OP_HISTORY
OLAPI_MEMORY_HEAP_HISTORY
OLAPI_MEMORY_OP_HISTORY
OLAPI_SESSION_HISTORY
OPTSTAT_HIST_CONTROL$
REGISTRY$HISTORY
SCHEDULER$_FILEWATCHER_HISTORY
SDO_NETWORK_HISTORIES
USER_HISTORY$
WRH$_ACTIVE_SESSION_HISTORY
WRH$_ACTIVE_SESSION_HISTORY_BL
WRH$_EVENT_HISTOGRAM
WRH$_EVENT_HISTOGRAM_BL
WRH$_FILEMETRIC_HISTORY
WRH$_SESSMETRIC_HISTORY
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_SYSMETRIC_HISTORY
WRH$_WAITCLASSMETRIC_HISTORY
WRI$_ALERT_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_TAB_HISTORY
WWV_FLOW_FEATURE_HISTORY
WWV_FLOW_LOG_HISTORY
WWV_FLOW_PASSWORD_HISTORY

54 rows selected.

Report message to a moderator

Re: Table Design Consideration [message #616850 is a reply to message #616834]

Sat, 21 June 2014 10:37

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I see that you have also raised this question here, https://community.oracle.com/thread/3575316

My standard advice to clients on partitioning is: "Don't use partitioning because you can. Get it right, and it may be good; get it wrong, and it will be disastrous. You need a precise business problem, and you must prove mathematically that your chosen partitioning strategy will fix that problem, and not cause any others."
You do not appear to have identified any problems, and have given no reasons for selecting your chosen partitioning strategy. You do need to do this. I have seen awful situations caused by using partitioning inappropriately.

Report message to a moderator

Re: Table Design Consideration [message #616855 is a reply to message #616834]

Sat, 21 June 2014 11:37

VIP2013
Messages: 91
Registered: June 2013

Member

@Blackswan, Yes my mistake, its three columns for that table METRIC_DATA. Also i think you are referring to the AWR tables for fetching DB performance data,but i already have those getting stored for ~6months in my Prod DB itself(through retention period setup), so i dont need those to be exported to any other schema. What i want is to store application metric data, like DB users logon can not be directly related to the application users those are actual clients hitting our system.

@John, thanks, yes for the time being i wil not consider the partitioning starategy, i was thiking of a design which will also cater the future partitioning strategy if we require. Can you please help me to validate the design strategy for the above mentioned scenario? Is it ok, or some other approach wil be better?

[Updated on: Sat, 21 June 2014 11:38]

Report message to a moderator

Re: Table Design Consideration [message #616856 is a reply to message #616855]

Sat, 21 June 2014 11:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> What i want is to store application metric data
Then the application must be instrumented to collect & store the desired application details; since Oracle RDBMS engine knows nothing about your application.

Report message to a moderator

Re: Table Design Consideration [message #616858 is a reply to message #616856]

Sat, 21 June 2014 12:06

VIP2013
Messages: 91
Registered: June 2013

Member

I mean to say, we have the transaction tables currently holding the functional data, like one of the table store all the transaction along with their details in the functional schema. But as a performance team perspective, we need basic info like count of total transactions for a day(or some SLA metrics which actually matters to client), at each day end it should get flushed out to another table, so that we can plot a trend and can understand what is the growth trend and how to cater that in future(or if SLA is breaching). So that we dont hit the transaction table each time for the reporting purpose and can use the capacity repository with all the main metrics, which really matters to client. Also we will keep these Metric data for years even if the transactional details might get purged..

[Updated on: Sat, 21 June 2014 12:08]

Report message to a moderator

Re: Table Design Consideration [message #616859 is a reply to message #616858]

Sat, 21 June 2014 12:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

since we don't have your table, data or requirements, I am at a loss to make any meaningful comment about what should or should not be done.

post CREATE TABLE statements for the "transaction tables".
how do the transaction table get populated?

Report message to a moderator

Re: Table Design Consideration [message #616861 is a reply to message #616859]

Sat, 21 June 2014 12:37

VIP2013
Messages: 91
Registered: June 2013

Member

Thanks for your prompt response. Actually for the time being i am not worried about the source tables/transaction tables and their details as the functional guys will provide queries to fetch/pull the business metrics data ot of that. i only need to think of the target table/tables design, which will hold these metrics.

[Updated on: Sat, 21 June 2014 12:38]

Report message to a moderator

Re: Table Design Consideration [message #616862 is a reply to message #616861]

Sat, 21 June 2014 12:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>2)No Of transactions done by ach user for that day
how will above be contained in 3 column table METRIC_DATE; which does not include an "OWNER" column?

Report message to a moderator

Re: Table Design Consideration [message #616863 is a reply to message #616862]

Sat, 21 June 2014 12:58

VIP2013
Messages: 91
Registered: June 2013

Member

you are right, total metrics can be captured in daily basis through this but if it comes to user level, then perhaps i have to name the metrics that much time as much no of users. Like user1_transaction,user2_transaction.. etc and store the value against that.Just to fit into the design, or is there some other technique?

[Updated on: Sat, 21 June 2014 13:01]

Report message to a moderator

Re: Table Design Consideration [message #616864 is a reply to message #616863]

Sat, 21 June 2014 14:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>3) VALUE VARCHAR Type, it will be of VARCHAR type and can store any type of data(NUMBER/VARCHAR.. etc)
I predict that you will (soon?) regret using VARCHAR2 to contain metric value

Report message to a moderator

Re: Table Design Consideration [message #616866 is a reply to message #616864]

Sat, 21 June 2014 14:25

VIP2013
Messages: 91
Registered: June 2013

Member

First of all, thanks for 'questioning/bringing to notice' the weak points of the design. Just not quite getting , how this column will cause issue, can you please elaborate. Also if in that case i have to create multiple columns to store the metric VALUE like Number,Varchar.. etc? or any other solution to it?

Report message to a moderator

Re: Table Design Consideration [message #616868 is a reply to message #616866]

Sat, 21 June 2014 14:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post example of existing Oracle performance metric value that is non-numeric

SQL> select 'TRUE' from dual where '100' < '2';

'TRU
----
TRUE

SQL> select 'TRUE' from dual where 1 > 2;

no rows selected

SQL>

strings & numbers compare differently

Report message to a moderator

Re: Table Design Consideration [message #616871 is a reply to message #616868]

Sat, 21 June 2014 14:48

VIP2013
Messages: 91
Registered: June 2013

Member

Thanks. Noted.

So for a particular metric the VALUE column will be either NUMBER or VARCHAR or DATE.. etc. In that case i will make sure, to use to_number,to_date functions properly during using comparison operator and it will be metric specific.So perhaps single VALUE column will suffice.

[Updated on: Sat, 21 June 2014 14:49]

Report message to a moderator

Re: Table Design Consideration [message #616872 is a reply to message #616871]

Sat, 21 June 2014 14:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

VIP2013 wrote on Sat, 21 June 2014 12:48

Thanks. Noted.

So for a particular metric the VALUE column will be either NUMBER or VARCHAR or DATE.. etc. In that case i will make sure, to use to_number,to_date functions properly during using comparison operator and it will be metric specific.So perhaps single VALUE column will suffice.

HUH?
I don't understand.

we speak SQL. Do you?

post actual working example; just as I provided YOU!

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: Table Design Consideration [message #616873 is a reply to message #616872]

Sat, 21 June 2014 15:17

VIP2013
Messages: 91
Registered: June 2013

Member

lets say a metric is transaction count and i want to get all the records having transaction count>100 out of this repository, then below will be the query

select * from METRIC_DATA
where metric_name='TRANSACTION_COUNT'
and to_number(value)>100;

its like , below works..

SQL> select 'TRUE' from dual where to_number('100') < to_number('2');

no rows selected

Report message to a moderator

Re: Table Design Consideration [message #616874 is a reply to message #616873]

Sat, 21 June 2014 15:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>and to_number(value)>100;
query THROWS error as soon as VALUE contains any other non-numeric value!

Report message to a moderator

Re: Table Design Consideration [message #616875 is a reply to message #616874]

Sat, 21 June 2014 15:29

VIP2013
Messages: 91
Registered: June 2013

Member

Thats why i mentioned , will make sure such queries will be asociated with additional METRIC_NAME filter so this situation will not arise. Again the value column will be mostly in SELECT clause only , but not in WHERE clause. Date and metric_name will be in the WHERE clause only.

Report message to a moderator

Previous Topic:	create sequence within pl/sql execute immediate.
Next Topic:	Getting duplicate entry in audit table after triggered

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 03:45:23 CDT 2024