Hi All,
Porting Oracle's NUMBER datatype to Postgres' numeric datatype might
seem very straight-forward. But the NUMERIC datatype is known to have a huge
performance impact on the query run-times.
So we have decided to convert the number columns to
SMALLINT/INTEGER/BIGINT wherever possible. In the last conference call, Jeff
opined that we first target those columns for migration which are NUMBERs
and are part of PRIMARY KEY, as this will help us in migrating the FOREIGN
KEY columns pointing to these PKey columns; and these two categories of
columns will cover most NUMBER columns that might affect the performance.
Please find attached the list of primary key columns of datatype NUMBER,
their table names, precision and scale (under the columns "P,S"), the names
of the PRIMARY KEY constraints.
What we are looking for from the developers is that they should comment
about the possible MAX_VAL for these columns. For example, Devan pointed out
the other day that in the application there are only around 50 action types,
which limits the number of values of "rhnActionType"."id" column to no more
than 50 values. Now equipped with this knowledge, this column can be easily
converted to SMALLINT; also, other columns referencing this column can also
be converted to SMALLINT.
So please come forward and comment on what would be the best data type
for the columns listed in the attached file. We need this.
Thanks and best regards,
--
gurjeet[[email protected]
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
TABLE_NAME COLUMN_NAME (P,S) CONSTRAINT_NAME
POSITION
------------------------------ ----------------- ----------
------------------------------ ----------
PXTSESSIONS ID default PXT_SESSIONS_PK
1
RHNACTION ID default RHN_ACTION_PK
1
RHNACTIONCONFIGREVISION ID default RHN_ACTIONCR_ID_PK
1
RHNACTIONKICKSTART ID default RHN_ACTIONKS_ID_PK
1
RHNACTIONKICKSTARTGUEST ID default
RHN_ACTIONKS_XENGUEST_ID_PK 1
RHNACTIONPACKAGE ID default RHN_ACT_P_ID_PK
1
RHNACTIONSCRIPT ID default RHN_ACTSCRIPT_ID_PK
1
RHNACTIONSTATUS ID default
RHN_ACTION_STATUS_PK 1
RHNACTIONTYPE ID default RHN_ACTION_TYPE_PK
1
RHNACTIONVIRTDESTROY ACTION_ID default RHN_AVD_AID_PK
1
RHNACTIONVIRTREBOOT ACTION_ID default RHN_AVREBOOT_AID_PK
1
RHNACTIONVIRTREFRESH ACTION_ID default
RHN_AVREFRESH_AID_PK 1
RHNACTIONVIRTRESUME ACTION_ID default RHN_AVRESUME_AID_PK
1
RHNACTIONVIRTSCHEDULEPOLLER ACTION_ID default RHN_AVSP_AID_PK
1
RHNACTIONVIRTSETMEMORY ACTION_ID default RHN_AVSM_AID_PK
1
RHNACTIONVIRTSHUTDOWN ACTION_ID default
RHN_AVSHUTDOWN_AID_PK 1
RHNACTIONVIRTSTART ACTION_ID default RHN_AVSTART_AID_PK
1
RHNACTIONVIRTSUSPEND ACTION_ID default
RHN_AVSUSPEND_AID_PK 1
RHNACTIONVIRTVCPU ACTION_ID default RHN_AVCPU_AID_PK
1
RHNAPPINSTALLINSTANCE ID default
RHN_APPINST_INSTANCE_ID_PK 1
RHNAPPINSTALLSESSION ID default
RHN_APPINST_SESSIOND_ID_PK 1
RHNAPPINSTALLSESSIONDATA ID default
RHN_APPINST_SDATA_ID_PK 1
RHNARCHTYPE ID default RHN_ARCHTYPE_ID_PK
1
RHNCHANNEL ID default RHN_CHANNEL_ID_PK
1
RHNCHANNELARCH ID default RHN_CARCH_ID_PK
1
RHNCHANNELCLONED ID default
RHN_CHANNELCLONE_ID_PK 1
RHNCHANNELCOMPS ID default
RHN_CHANNELCOMPS_ID_PK 1
RHNCHANNELFAMILY ID default
RHN_CHANNEL_FAMILY_ID_PK 1
RHNCHANNELPERMISSIONROLE ID default
RHN_CPERM_ROLE_ID_PK 1
RHNCHANNELPRODUCT ID default
RHN_CHANNELPROD_ID_PK 1
RHNCLIENTCAPABILITYNAME ID default
RHN_CLIENTCAPNAM_ID_PK 1
RHNCONFIGCHANNEL ID default RHN_CONFCHAN_ID_PK
1
RHNCONFIGCHANNELTYPE ID default
RHN_CONFCHANTYPE_ID_PK 1
RHNCONFIGCONTENT ID default
RHN_CONFCONTENT_ID_PK 1
RHNCONFIGFILE ID default RHN_CONFFILE_ID_PK
1
RHNCONFIGFILEFAILURE ID default
RHN_CONFFILE_FAIL_ID_PK 1
RHNCONFIGFILENAME ID default RHN_CFNAME_ID_PK
1
RHNCONFIGFILESTATE ID default RHN_CFSTATE_ID_PK
1
RHNCONFIGFILETYPE ID default
RHN_CONFFILETYPE_ID_PK 1
RHNCONFIGINFO ID default RHN_CONFINFO_ID_PK
1
RHNCONFIGREVISION ID default
RHN_CONFREVISION_ID_PK 1
RHNCPU ID default RHN_CPU_ID_PK
1
RHNCPUARCH ID default RHN_CPUARCH_ID_PK
1
RHNCRYPTOKEY ID default RHN_CRYPTOKEY_ID_PK
1
RHNCRYPTOKEYTYPE ID default
RHN_CRYPTOKEYTYPE_ID_PK 1
RHNCUSTOMDATAKEY ID default RHN_CDATAKEY_PK
1
RHNCVE ID default RHN_CVE_ID_PK
1
RHNDEVICE ID default RHN_DEVICE_ID_PK
1
RHNDOWNLOADS ID default RHN_DL_ID_PK
1
RHNDOWNLOADTYPE ID default
RHN_DOWNLOAD_TYPE_PK 1
RHNEMAILADDRESS ID default RHN_EADDRESS_ID_PK
1
RHNEMAILADDRESSSTATE ID default RHN_EASTATE_ID_PK
1
RHNERRATA ID default RHN_ERRATA_ID_PK
1
RHNERRATACLONED ID default
RHN_ERRATACLONE_ID_PK 1
RHNERRATACLONEDTMP ID default
RHN_ECLONEDTMP_ID_PK 1
RHNERRATAFILE ID default
RHN_ERRATAFILE_ID_PK 1
RHNERRATAFILETMP ID default
RHN_ERRATAFILETMP_ID_PK 1
RHNERRATAFILETYPE ID default
RHN_ERRATAFILE_TYPE_ID_PK 1
RHNERRATASEVERITY ID default
RHN_ERRATA_SEV_ID_PK 1
RHNERRATATMP ID default RHN_ERRATATMP_ID_PK
1
RHNEXCEPTION ID default RHN_EXC_ID_PK
1
RHNFAQ ID default RHN_FAQ_ID_PK
1
RHNFAQCLASS ID default RHN_FAQ_CLASS_ID_PK
1
RHNFEATURE ID default RHN_FEATURE_ID
1
RHNFILE ID default RHN_FILE_ID_PK
1
RHNFILELIST ID default RHN_FILELIST_ID_PK
1
RHNGRAILCOMPONENTS ID default RHN_GRAIL_COMP_PK
1
RHNINFOPANE ID default RHN_INFO_PANE_ID_PK
1
RHNKICKSTARTABLETREE ID default RHN_KSTREE_ID_PK
1
RHNKICKSTARTCOMMAND ID default RHN_KSCOMMAND_ID_PK
1
RHNKICKSTARTCOMMANDNAME ID default
RHN_KSCOMMANDNAME_ID_PK 1
RHNKICKSTARTSCRIPT ID default RHN_KSSCRIPT_ID_PK
1
RHNKICKSTARTSESSION ID default
RHN_KS_SESSION_ID_PK 1
RHNKICKSTARTSESSIONHISTORY ID default
RHN_KS_SESSIONHIST_ID_PK 1
RHNKICKSTARTSESSIONSTATE ID default
RHN_KS_SESSION_STATE_ID_PK 1
RHNKICKSTARTTIMEZONE ID default RHN_KS_TIMEZONE_PK
1
RHNKICKSTARTVIRTUALIZATIONTYPE ID default RHN_KVT_ID_PK
1
RHNKSDATA ID default RHN_KS_ID_PK
1
RHNKSINSTALLTYPE ID default
RHN_KSINSTALLTYPE_ID_PK 1
RHNKSTREETYPE ID default
RHN_KSTREETYPE_ID_PK 1
RHNMESSAGE ID default RHN_M_ID_PK
1
RHNMESSAGEPRIORITY ID default
RHN_M_PRIORITY_ID_PK 1
RHNMESSAGETYPE ID default RHN_M_TYPE_ID_PK
1
RHNMONITORGRANULARITY ID default
RHN_MONITORGRAN_ID_PK 1
RHNORGCHANNELSETTINGSTYPE ID default
RHN_OCSTNGS_TYPE_ID_PK 1
RHNORGENTITLEMENTTYPE ID default
RHN_ORG_ENT_TYPE_ID_PK 1
RHNPACKAGE ID default RHN_PACKAGE_ID_PK
1
RHNPACKAGEARCH ID default RHN_PARCH_ID_PK
1
RHNPACKAGECAPABILITY ID default
RHN_PKG_CAPABILITY_ID_PK 1
RHNPACKAGECHANGELOG ID default RHN_PKG_CL_ID_PK
1
RHNPACKAGEDELTA ID default
RHN_PACKAGEDELTA_ID_PK 1
RHNPACKAGEEVR ID default RHN_PE_ID_PK
1
RHNPACKAGEGROUP ID default
RHN_PACKAGE_GROUP_ID_PK 1
RHNPACKAGEKEY ID default RHN_PKEY_ID_PK
1
RHNPACKAGEKEYTYPE ID default
RHN_PKG_KEY_TYPE_ID_PK 1
RHNPACKAGENAME ID default RHN_PN_ID_PK
1
RHNPACKAGENEVRA ID default RHN_PKGNEVRA_ID_PK
1
RHNPACKAGEPROVIDER ID default
RHN_PKG_PROVIDER_ID_PK 1
RHNPACKAGESENSE ID default RHN_PKG_SENSE_ID_PK
1
RHNPACKAGESOURCE ID default RHN_PKGSRC_ID_PK
1
RHNPRODUCT ID default RHN_PRODUCT_ID_PK
1
RHNPRODUCTLINE ID default RHN_PROD_LINE_ID_PK
1
RHNPRODUCTNAME ID default
RHN_PRODUCTNAME_ID_PK 1
RHNPROVISIONSTATE ID default RHN_PROVSTATE_ID_PK
1
RHNPUSHCLIENT ID default RHN_PCLIENT_ID_PK
1
RHNPUSHCLIENTSTATE ID default
RHN_PCLIENT_STATE_ID_PK 1
RHNPUSHDISPATCHER ID default
RHN_PUSHDISPATCH_ID_PK 1
RHNRAM ID default RHN_RAM_ID_PK
1
RHNREGTOKEN ID default RHN_REG_TOKEN_PK
1
RHNREGTOKENPACKAGES ID default
RHN_REG_TOK_PKG_ID_PK 1
RHNRELATIONSHIPTYPE ID default RHN_RELTYPE_ID_PK
1
RHNSAVEDSEARCH ID default
RHN_SAVEDSEARCH_ID_PK 1
RHNSAVEDSEARCHTYPE ID default RHN_SSTYPE_ID_PK
1
RHNSERVER ID default RHN_SERVER_ID_PK
1
RHNSERVERARCH ID default RHN_SARCH_ID_PK
1
RHNSERVERDMI ID default RHN_SERVER_DMI_PK
1
RHNSERVEREVENT ID default RHN_SE_ID_PK
1
RHNSERVERGROUP ID default
RHN_SERVERGROUP_ID_PK 1
RHNSERVERGROUPNOTES ID default
RHN_SERVERGRP_NOTE_ID_PK 1
RHNSERVERGROUPTYPE ID default
RHN_SERVERGROUPTYPE_ID_PK 1
RHNSERVERHISTORY ID default
RHN_SERVERHISTORY_ID_PK 1
RHNSERVERINSTALLINFO ID default
RHN_SERVER_INSTALL_INFO_ID_PK 1
RHNSERVERLOCATION ID default
RHN_SERVERLOCATION_ID_PK 1
RHNSERVERNETWORK ID default
RHN_SERVERNETWORK_ID_PK 1
RHNSERVERNOTES ID default
RHN_SERVERNOTES_ID_PK 1
RHNSERVERPROFILE ID default
RHN_SERVER_PROFILE_ID_PK 1
RHNSERVERPROFILETYPE ID default RHN_SPROFTYPE_ID_PK
1
RHNSNAPSHOT ID default RHN_SNAPSHOT_ID_PK
1
RHNSNAPSHOTINVALIDREASON ID default RHN_SSINVALID_ID_PK
1
RHNSOLARISPACKAGE PACKAGE_ID default
RHN_SOLARIS_PKG_PID_PK 1
RHNSOLARISPATCH PACKAGE_ID default RHN_SOLARIS_P_PK
1
RHNSOLARISPATCHSET PACKAGE_ID default
RHN_SOLARIS_PS_PID_PK 1
RHNSOLARISPATCHTYPE ID default RHN_SOLARIS_PT_PK
1
RHNSOURCERPM ID default RHN_SOURCERPM_ID_PK
1
RHNTAG ID default RHN_TAG_ID_PK
1
RHNTAGNAME ID default RHN_TN_ID_PK
1
RHNTEMPLATECATEGORY ID default
RHN_TEMPLATE_CAT_ID_PK 1
RHNTEMPLATESTRING ID default
RHN_TEMPLATE_STR_ID_PK 1
RHNTIMEZONE ID default RHN_TIMEZONE_ID_PK
1
RHNTRANSACTION ID default RHN_TRANS_ID_PK
1
RHNTRANSACTIONOPERATION ID default RHN_TRANSOP_ID_PK
1
RHNTRANSACTIONPACKAGE ID default RHN_TRANSPACK_ID_PK
1
RHNUSERGROUP ID default RHN_USER_GROUP_PK
1
RHNUSERGROUPTYPE ID default
RHN_USERGROUPTYPE_ID_PK 1
RHNUSERMESSAGESTATUS ID default RHN_UM_STATUS_ID_PK
1
RHNUSERMESSAGETYPE ID default RHN_UM_TYPE_PK
1
RHNVIRTSUBLEVEL ID default RHN_VSL_ID_PK
1
RHNVIRTUALINSTANCE ID default RHN_VI_ID_PK
1
RHNVIRTUALINSTANCEEVENTLOG ID default RHN_VIEL_ID_PK
1
RHNVIRTUALINSTANCEEVENTTYPE ID default RHN_VIET_ID_PK
1
RHNVIRTUALINSTANCEINSTALLLOG ID default RHN_VIIL_ID_PK
1
RHNVIRTUALINSTANCESTATE ID default RHN_VIS_ID_PK
1
RHNVIRTUALINSTANCETYPE ID default RHN_VIT_ID_PK
1
RHNWEBCONTACTCHANGELOG ID default RHN_WCON_CL_ID_PK
1
RHNWEBCONTACTCHANGESTATE ID default
RHN_CONT_CHANGE_STATE_ID_PK 1
RHN_CHECK_PROBE PROBE_ID (12 ,0)
RHN_CHKPB_PROBE_ID_PK 1
RHN_CHECK_SUITES RECID (12 ,0) RHN_CKSUT_RECID_PK
1
RHN_CHECK_SUITE_PROBE PROBE_ID (12 ,0)
RHN_CKSPB_PROBE_ID_PK 1
RHN_COMMAND RECID (12 ,0) RHN_CMMND_RECID_PK
1
RHN_COMMAND_PARAMETER COMMAND_ID (12 ,0)
RHN_CPARM_ID_PARM_NAME_PK 1
RHN_COMMAND_PARAM_THRESHOLD COMMAND_ID (12 ,0)
RHN_COPTR_ID_P_NAME_P_TYPE_PK 1
RHN_COMMAND_QUEUE_COMMANDS RECID (12 ,0) RHN_CQCMD_RECID_PK
1
RHN_COMMAND_QUEUE_EXECS INSTANCE_ID (12 ,0)
RHN_CQEXE_INST_ID_NSAINT_PK 1
RHN_COMMAND_QUEUE_EXECS NETSAINT_ID (12 ,0)
RHN_CQEXE_INST_ID_NSAINT_PK 2
RHN_COMMAND_QUEUE_INSTANCES RECID (12 ,0) RHN_CQINS_RECID_PK
1
RHN_COMMAND_QUEUE_PARAMS INSTANCE_ID (12 ,0)
RHN_CQPRM_INSTANCE_ID_ORD_PK 1
RHN_COMMAND_QUEUE_PARAMS ORD (3 ,0)
RHN_CQPRM_INSTANCE_ID_ORD_PK 2
RHN_COMMAND_TARGET RECID (12 ,0)
RHN_CMDTG_RECID_TARGET_TYPE_PK 1
RHN_CONTACT_GROUPS RECID (12 ,0) RHN_CNTGP_RECID_PK
1
RHN_CONTACT_GROUP_MEMBERS CONTACT_GROUP_ID default
RHN_CNTGM_CGID_ORDER_PK 1
RHN_CONTACT_GROUP_MEMBERS ORDER_NUMBER default
RHN_CNTGM_CGID_ORDER_PK 2
RHN_CONTACT_METHODS RECID (12 ,0) RHN_CMETH_RECID_PK
1
RHN_CURRENT_ALERTS RECID (12 ,0) RHN_ALRTS_RECID_PK
1
RHN_CURRENT_STATE_SUMMARIES CUSTOMER_ID (12 ,0)
RHN_CURRENT_STATE_SUMMARIES_PK 1
RHN_DEPLOYED_PROBE RECID (12 ,0) RHN_DPROB_RECID_PK
1
RHN_HOST_CHECK_SUITES HOST_PROBE_ID (12 ,0)
RHN_HSTCK_SUITE_ID_PROBE_ID_PK 1
RHN_HOST_CHECK_SUITES SUITE_ID (12 ,0)
RHN_HSTCK_SUITE_ID_PROBE_ID_PK 2
RHN_HOST_PROBE PROBE_ID (12 ,0)
RHN_HSTPB_PROBE_ID_PK 1
RHN_INTERFACE_MONITORING SERVER_ID default RHN_MONIF_SERVER_PK
1
RHN_METHOD_TYPES RECID (12 ,0) RHN_MTHTP_RECID_PK
1
RHN_MULTI_SCOUT_THRESHOLD PROBE_ID (12 ,0)
RHN_MSTHR_PROBE_ID_PK 1
RHN_NOTIFICATION_FORMATS RECID (12 ,0) RHN_NTFMT_RECID_PK
1
RHN_NOTIFSERVERS RECID (12 ,0) RHN_NOTSV_RECID_PK
1
RHN_OS RECID (12 ,0) RHN_OS000_RECID_PK
1
RHN_OS_COMMANDS_XREF OS_ID (12 ,0)
RHN_OSCXR_OS_ID_COMMANDS_ID_PK 1
RHN_OS_COMMANDS_XREF COMMANDS_ID (12 ,0)
RHN_OSCXR_OS_ID_COMMANDS_ID_PK 2
RHN_PAGER_TYPES RECID (12 ,0) RHN_PGRTP_RECID_PK
1
RHN_PHYSICAL_LOCATION RECID (12 ,0) RHN_PHSLC_RECID_PK
1
RHN_PROBE RECID (12 ,0) RHN_PROBE_RECID_PK
1
RHN_PROBE_PARAM_VALUE PROBE_ID default
RHN_PPVAL_P_ID_CMD_ID_PARM_PK 1
RHN_PROBE_PARAM_VALUE COMMAND_ID default
RHN_PPVAL_P_ID_CMD_ID_PARM_PK 2
RHN_PROBE_STATE PROBE_ID (12 ,0)
PRBST_PROBE_ID_SCOUT_ID_PK 1
RHN_PROBE_STATE SCOUT_ID (12 ,0)
PRBST_PROBE_ID_SCOUT_ID_PK 2
RHN_REDIRECTS RECID (12 ,0) RHN_RDRCT_RECID_PK
1
RHN_REDIRECT_CRITERIA RECID (12 ,0) RHN_RDRCR_RECID_PK
1
RHN_REDIRECT_EMAIL_TARGETS REDIRECT_ID (12 ,0) RHN_RDRET_PK
1
RHN_REDIRECT_GROUP_TARGETS REDIRECT_ID (12 ,0) RHN_RDRGT_PK
1
RHN_REDIRECT_GROUP_TARGETS CONTACT_GROUP_ID (12 ,0) RHN_RDRGT_PK
2
RHN_REDIRECT_METHOD_TARGETS REDIRECT_ID (12 ,0) RHN_RDRME_PK
1
RHN_REDIRECT_METHOD_TARGETS CONTACT_METHOD_ID (12 ,0) RHN_RDRME_PK
2
RHN_SATELLITE_STATE SATELLITE_ID (12 ,0) RHN_SATST_SAT_ID_PK
1
RHN_SAT_CLUSTER RECID (12 ,0) RHN_SATCL_RECID_PK
1
RHN_SAT_CLUSTER_PROBE PROBE_ID (12 ,0)
RHN_SCLPB_PROBE_ID_PK 1
RHN_SAT_NODE RECID (12 ,0) RHN_SATND_RECID_PK
1
RHN_SAT_NODE_PROBE PROBE_ID (12 ,0)
RHN_SNDPB_PROBE_ID_PK 1
RHN_SCHEDULES RECID (12 ,0) RHN_SCHED_RECID_PK
1
RHN_SCHEDULE_DAYS RECID (12 ,0) RHN_SCHDY_RECID_PK
1
RHN_SCHEDULE_TYPES RECID (12 ,0) RHN_SCHTP_RECID_PK
1
RHN_SCHEDULE_WEEKS RECID (12 ,0) RHN_SCHWK_RECID_PK
1
RHN_SERVER_MONITORING_INFO RECID (12 ,0) RHN_HOST_RECID_PK
1
RHN_SNMP_ALERT RECID (12 ,0) RHN_SNMPA_RECID_PK
1
RHN_STRATEGIES RECID (12 ,0) RHN_STRAT_RECID_PK
1
RHN_URL_PROBE PROBE_ID (12 ,0)
RHN_URLPB_PROBE_ID_PK 1
RHN_URL_PROBE_STEP RECID (12 ,0) RHN_URLPS_RECID_PK
1
WEB_CONTACT ID default WEB_CONTACT_PK
1
WEB_CUSTOMER ID default WEB_CUSTOMER_ID_PK
1
WEB_CUSTOMER_NOTIFICATION ID default WEB_CUST_NOT_ID_PK
1
WEB_USER_SITE_INFO ID default WUSI_ID_PK
1
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
