Accessing data from table through Db link by view in oracle
Hi Experts
I am in bit confused state, as I am trying to get something out here from the below query, and it takes very long time then accepted to return the rows (which is in millions)
here table refrenced is on a diffrent server which it connects through db link,
This is oracle 11.1.7
SELECT DISTINCT SEC_SHORT_DESC FROM ds_bi.V_DIM_SECURITY;
View code:
CREATE OR REPLACE FORCE VIEW DS_BI.V_DIM_SECURITY
(
START_DTE,
END_DTE,
SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
SECURITYTYPECODE,
SECURITYTYPENAME,
SECURITY_GRP,
SECURITY_TYPE,
SECURITY_TYPE_DESC,
SECURITY_SUBTYPE_CDE,
SECURITY_SUBTYPE_NME,
SECURITY_SUBSUBTYPE_CDE,
SECURITY_SUBSUBTYPE_NME,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_NME,
MUNICIPAL_BOND_ISSUE_ST,
BOND_FACTOR_TYPE,
BOND_FACTOR_TYPE_NME,
SECURITY_CLASS,
SECURITY_CLASS_NME,
FUND_TYPE,
FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
)
AS
SELECT START_DTE,
END_DTE,
CAST (SEC_NO AS INT) AS SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
NVL (SEC_TYPE, '') || ' - ' || NEW_SEC_CODE AS SecurityTypeCode,
CASE
WHEN NEW_SEC_CODE = 'A' AND SEC_TYPE = 'C'
THEN
'Annuities'
WHEN NEW_SEC_CODE = 'C' AND SEC_TYPE = 'C'
THEN
'Common Stock'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'B'
THEN
'Debt (DB)'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'M'
THEN
'Debt (DM)'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'T'
THEN
'Debt (DT)'
WHEN NEW_SEC_CODE = 'F' AND SEC_TYPE IS NULL
THEN
'Futures'
WHEN NEW_SEC_CODE = 'G' AND SEC_TYPE = 'P'
THEN
'Escrow Receipt'
WHEN NEW_SEC_CODE = 'L' AND SEC_TYPE = 'C'
THEN
'Real Estate'
WHEN NEW_SEC_CODE = 'M' AND SEC_TYPE = 'C'
THEN
'Mutual Fund - Closed End'
WHEN NEW_SEC_CODE = 'M' AND SEC_TYPE = 'F'
THEN
'Mutual Fund - Open End'
WHEN NEW_SEC_CODE = 'MM' AND SEC_TYPE = 'F'
THEN
'Money Market Fund'
WHEN NEW_SEC_CODE = 'O' AND SEC_TYPE = 'P'
THEN
'Listed Option'
WHEN NEW_SEC_CODE = 'OT' AND SEC_TYPE = 'P'
THEN
'OTC Options'
WHEN NEW_SEC_CODE = 'P' AND SEC_TYPE = 'R'
THEN
'Preferred Stock'
WHEN NEW_SEC_CODE = 'U' AND SEC_TYPE = 'U'
THEN
'Units'
WHEN NEW_SEC_CODE = 'UT' AND SEC_TYPE = 'B'
THEN
'UIT (B)'
WHEN NEW_SEC_CODE = 'UT' AND SEC_TYPE = 'M'
THEN
'UIT (M)'
WHEN NEW_SEC_CODE = 'W' AND SEC_TYPE = 'G'
THEN
'Rights'
WHEN NEW_SEC_CODE = 'W' AND SEC_TYPE = 'W'
THEN
'Warrants'
WHEN NEW_SEC_CODE = 'X' AND SEC_TYPE = 'C'
THEN
'Currency'
ELSE
'Miscellaneous'
END
SecurityTypeName,
CASE
WHEN NEW_SEC_CODE = 'D' THEN 'Bonds'
WHEN NEW_SEC_CODE = 'M' THEN 'Funds'
ELSE 'Other'
END
AS SECURITY_GRP,
SECURITY_TYPE,
SECURITY_TYPE_DESC,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_TYPE_CODE
WHEN NEW_SEC_CODE = 'M' THEN MF_SHARE_CLASS_ID
ELSE SECURITY_TYPE
END
AS SECURITY_SUBTYPE_CDE,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_TYPE_NME
WHEN NEW_SEC_CODE = 'M' THEN MF_SHARE_CLASS_NME
ELSE SECURITY_TYPE_DESC
END
AS SECURITY_SUBTYPE_NME,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_FACTOR_TYPE
WHEN NEW_SEC_CODE = 'M' THEN FUND_TYPE
ELSE SECURITY_TYPE
END
AS SECURITY_SUBSUBTYPE_CDE,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_FACTOR_TYPE_NME
WHEN NEW_SEC_CODE = 'M' THEN FUND_TYPE_NME
ELSE SECURITY_TYPE_DESC
END
AS SECURITY_SUBSUBTYPE_NME,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_NME,
MUNICIPAL_BOND_ISSUE_ST,
BOND_FACTOR_TYPE,
BOND_FACTOR_TYPE_NME,
SECURITY_CLASS,
SECURITY_CLASS_NME,
FUND_TYPE,
FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
FROM (SELECT TRUNC (BATCH_DTE_CYMD, 'MONTH') AS START_DTE,
BATCH_DTE_CYMD AS END_DTE,
SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
ALPHA_KEY_5 AS SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
NEW_SEC_CODE || SEC_TYPE AS SECURITY_TYPE,
NEW_SEC_CODE || SEC_TYPE AS SECURITY_TYPE_DESC,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NO AS INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_CODE AS BOND_TYPE_NME,
STATE_CODE AS MUNICIPAL_BOND_ISSUE_ST,
BOND_SUB_CODE AS BOND_FACTOR_TYPE,
BOND_SUB_CODE AS BOND_FACTOR_TYPE_NME,
TYPE_CLASS_CODE AS SECURITY_CLASS,
TYPE_CLASS_CODE AS SECURITY_CLASS_NME,
FUND_CODE AS FUND_TYPE,
FUND_CODE AS FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_ID AS MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_IND AS PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
FROM SODS01.BETA_SEC_MTH@ODS01)
My concern is whether I am doing right thing using view and not the simple sql ? Or its CAST which is causing the dealy? What could be the remedy to get the faster result...
Do you want the table structure (SQL) from db on db link?
Please help...
Thanks a Ton.
I am in bit confused state, as I am trying to get something out here from the below query, and it takes very long time then accepted to return the rows (which is in millions)
here table refrenced is on a diffrent server which it connects through db link,
This is oracle 11.1.7
SELECT DISTINCT SEC_SHORT_DESC FROM ds_bi.V_DIM_SECURITY;
View code:
CREATE OR REPLACE FORCE VIEW DS_BI.V_DIM_SECURITY
(
START_DTE,
END_DTE,
SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
SECURITYTYPECODE,
SECURITYTYPENAME,
SECURITY_GRP,
SECURITY_TYPE,
SECURITY_TYPE_DESC,
SECURITY_SUBTYPE_CDE,
SECURITY_SUBTYPE_NME,
SECURITY_SUBSUBTYPE_CDE,
SECURITY_SUBSUBTYPE_NME,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_NME,
MUNICIPAL_BOND_ISSUE_ST,
BOND_FACTOR_TYPE,
BOND_FACTOR_TYPE_NME,
SECURITY_CLASS,
SECURITY_CLASS_NME,
FUND_TYPE,
FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
)
AS
SELECT START_DTE,
END_DTE,
CAST (SEC_NO AS INT) AS SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
NVL (SEC_TYPE, '') || ' - ' || NEW_SEC_CODE AS SecurityTypeCode,
CASE
WHEN NEW_SEC_CODE = 'A' AND SEC_TYPE = 'C'
THEN
'Annuities'
WHEN NEW_SEC_CODE = 'C' AND SEC_TYPE = 'C'
THEN
'Common Stock'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'B'
THEN
'Debt (DB)'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'M'
THEN
'Debt (DM)'
WHEN NEW_SEC_CODE = 'D' AND SEC_TYPE = 'T'
THEN
'Debt (DT)'
WHEN NEW_SEC_CODE = 'F' AND SEC_TYPE IS NULL
THEN
'Futures'
WHEN NEW_SEC_CODE = 'G' AND SEC_TYPE = 'P'
THEN
'Escrow Receipt'
WHEN NEW_SEC_CODE = 'L' AND SEC_TYPE = 'C'
THEN
'Real Estate'
WHEN NEW_SEC_CODE = 'M' AND SEC_TYPE = 'C'
THEN
'Mutual Fund - Closed End'
WHEN NEW_SEC_CODE = 'M' AND SEC_TYPE = 'F'
THEN
'Mutual Fund - Open End'
WHEN NEW_SEC_CODE = 'MM' AND SEC_TYPE = 'F'
THEN
'Money Market Fund'
WHEN NEW_SEC_CODE = 'O' AND SEC_TYPE = 'P'
THEN
'Listed Option'
WHEN NEW_SEC_CODE = 'OT' AND SEC_TYPE = 'P'
THEN
'OTC Options'
WHEN NEW_SEC_CODE = 'P' AND SEC_TYPE = 'R'
THEN
'Preferred Stock'
WHEN NEW_SEC_CODE = 'U' AND SEC_TYPE = 'U'
THEN
'Units'
WHEN NEW_SEC_CODE = 'UT' AND SEC_TYPE = 'B'
THEN
'UIT (B)'
WHEN NEW_SEC_CODE = 'UT' AND SEC_TYPE = 'M'
THEN
'UIT (M)'
WHEN NEW_SEC_CODE = 'W' AND SEC_TYPE = 'G'
THEN
'Rights'
WHEN NEW_SEC_CODE = 'W' AND SEC_TYPE = 'W'
THEN
'Warrants'
WHEN NEW_SEC_CODE = 'X' AND SEC_TYPE = 'C'
THEN
'Currency'
ELSE
'Miscellaneous'
END
SecurityTypeName,
CASE
WHEN NEW_SEC_CODE = 'D' THEN 'Bonds'
WHEN NEW_SEC_CODE = 'M' THEN 'Funds'
ELSE 'Other'
END
AS SECURITY_GRP,
SECURITY_TYPE,
SECURITY_TYPE_DESC,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_TYPE_CODE
WHEN NEW_SEC_CODE = 'M' THEN MF_SHARE_CLASS_ID
ELSE SECURITY_TYPE
END
AS SECURITY_SUBTYPE_CDE,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_TYPE_NME
WHEN NEW_SEC_CODE = 'M' THEN MF_SHARE_CLASS_NME
ELSE SECURITY_TYPE_DESC
END
AS SECURITY_SUBTYPE_NME,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_FACTOR_TYPE
WHEN NEW_SEC_CODE = 'M' THEN FUND_TYPE
ELSE SECURITY_TYPE
END
AS SECURITY_SUBSUBTYPE_CDE,
CASE
WHEN NEW_SEC_CODE = 'D' THEN BOND_FACTOR_TYPE_NME
WHEN NEW_SEC_CODE = 'M' THEN FUND_TYPE_NME
ELSE SECURITY_TYPE_DESC
END
AS SECURITY_SUBSUBTYPE_NME,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_NME,
MUNICIPAL_BOND_ISSUE_ST,
BOND_FACTOR_TYPE,
BOND_FACTOR_TYPE_NME,
SECURITY_CLASS,
SECURITY_CLASS_NME,
FUND_TYPE,
FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
FROM (SELECT TRUNC (BATCH_DTE_CYMD, 'MONTH') AS START_DTE,
BATCH_DTE_CYMD AS END_DTE,
SEC_NO,
ALPHA_KEY,
ALPHA_KEY_2,
ALPHA_KEY_5 AS SEC_SHORT_DESC,
DESC_TXT,
DESC_2,
NEW_SEC_CODE || SEC_TYPE AS SECURITY_TYPE,
NEW_SEC_CODE || SEC_TYPE AS SECURITY_TYPE_DESC,
SYMBOL,
CUSIP,
SEC_TYPE,
INDUSTRY_GRP_NO,
INDUSTRY_GRP_NO AS INDUSTRY_GRP_NME,
BOND_TYPE_CODE,
BOND_TYPE_CODE AS BOND_TYPE_NME,
STATE_CODE AS MUNICIPAL_BOND_ISSUE_ST,
BOND_SUB_CODE AS BOND_FACTOR_TYPE,
BOND_SUB_CODE AS BOND_FACTOR_TYPE_NME,
TYPE_CLASS_CODE AS SECURITY_CLASS,
TYPE_CLASS_CODE AS SECURITY_CLASS_NME,
FUND_CODE AS FUND_TYPE,
FUND_CODE AS FUND_TYPE_NME,
MF_SHARE_CLASS_ID,
MF_SHARE_CLASS_ID AS MF_SHARE_CLASS_NME,
NEW_SEC_CODE,
PUT_OR_CALL_IND,
PUT_OR_CALL_IND AS PUT_OR_CALL_NME,
LAST_CLOSE_PRX,
PRX_CHANGE_CYMD,
MONTH_END_PRX,
MONTH_END_PRX_CYMD,
YEAR_END_PRX,
YEAR_END_CYMD,
SANDP_CODE,
BID_PRX,
ASK_PRX
FROM SODS01.BETA_SEC_MTH@ODS01)
My concern is whether I am doing right thing using view and not the simple sql ? Or its CAST which is causing the dealy? What could be the remedy to get the faster result...
Do you want the table structure (SQL) from db on db link?
Please help...
Thanks a Ton.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
The cast has the potential to negate the use of an index on sec_no, but that would only apply if certain where clauses were involved. Same issue applies to TRUNC, btw.
You might consider joining to a code table for the security type descriptions (instead of the massive case statement), but that's more a matter of style and governance than performance.
I suggest running the query locally to determine how much of the problem is query/dbms structure related and how much is network related.
Also, might be helpful to see the actual query you are running. I assume it was something like "select * from DS_BI.V_DIM_SECURITY", but that's just conjecture.