Wrong cardinality estimates
John, November 06, 2020 - 2:23 am UTC
Hi Chris,
SEARCH_FI is a view.
Here is the view definition .
CREATE OR REPLACE FORCE EDITIONABLE VIEW "BT"."SEARCH_FI" ("INSTRUMENT_ID", "COUPON_RATE", "MATURITY_DATE", "ORG_NAME", "INST_NAME", "BOND_TICKER", "ASSET_TYPE", "DEAD_DATE",
"ORGANIZATION_ID") AS
SELECT IM.INSTRUMENT_ID, FI.CURRENT_COUPON_RATE, FI.MATURITY_DATE, OM.PRIMARY_NAME, IM.PRIMARY_NAME, OM.BOND_TICKER, IM.ASSET_TYPE, IM.DEAD_DATE, OM.ORGANIZATION_ID
FROM
BT.I_MASTER_CUR IM
INNER JOIN BT.ORG_MASTER_CUR OM ON (IM.ORGANIZATION_ID = OM.ORGANIZATION_ID)
LEFT OUTER JOIN BT.F_INCOME_CUR FI ON (IM.INSTRUMENT_ID = FI.INSTRUMENT_ID)
;
CREATE OR REPLACE FORCE EDITIONABLE VIEW "BT"."I_MASTER_CUR" ("ASSET_TYPE", "CHILD_ISSUE_IND", "COMMENTS", "COUNTRY_OF_RISK", "CREATION_DATE", "DEAD_DATE", "DELIVERY_DATE", "DTC_ELIGIBILITY",
"EVAL_QUOTATION_BASIS", "HYBRID_TYPE", "IDC_ASSET_TYPE", "INITIAL_AUCTION_DATE", "INSTRUMENT_ID", "INSTRUMENT_SETUP_STATUS", "INSTRUMENT_SETUP_STATUS_DATE", "INSTRUMENT_STATUS",
"INSTRUMENT_STAT
US_REASON", "INSTRUMENT_TYPE", "ISSUE_DATE", "ISSUE_PRICE", "ISSUE_PRICE_FIXED_IND", "ISSUE_PRICE_PERCENT", "ISSUE_PRICE_TYPE", "LOADED_BY", "ORGANIZATION_ID", "OUTSTANDING_AMOUNT",
"OUTSTANDING_IND", "OVERALLOTMENT_IND", "PRIMARY_CURRENCY_CODE", "PRIMARY_EXCHANGE", "PRIMARY_MARKET_CODE", "PRIMARY_NAME", "REGISTRATION_DAYS", "REGISTRATION_TYPE", "RIGHTS_IND", "SECTOR",
"SEC_REGISTRATION_FLAG", "SE
TTLEMENT_DATE", "SOURCE_CODE", "TRADING_RESTRICTIONS_TYPE", "TRADING_STATUS", "UNIT_IND") AS
SELECT
ASSET_TYPE,
CHILD_ISSUE_IND,
COMMENTS,
COUNTRY_OF_RISK,
CREATION_DATE,
DEAD_DATE,
DELIVERY_DATE,
DTC_ELIGIBILITY,
EVAL_QUOTATION_BASIS,
HYBRID_TYPE,
IDC_ASSET_TYPE,
INITIAL_AUCTION_DATE,
INSTRUMENT_ID,
INSTRUMENT_SETUP_STATUS,
INSTRUMENT_SETUP_STATUS_DATE,
INSTRUMENT_STATUS,
INSTRUMENT_STATUS_REASON,
INSTRUMENT_TYPE,
ISSUE_DATE,
ISSUE_PRICE,
ISSUE_PRICE_FIXED_IND,
ISSUE_PRICE_PERCENT,
ISSUE_PRICE_TYPE,
LOADED_BY,
ORGANIZATION_ID,
OUTSTANDING_AMOUNT,
OUTSTANDING_IND,
OVERALLOTMENT_IND,
PRIMARY_CURRENCY_CODE,
PRIMARY_EXCHANGE,
PRIMARY_MARKET_CODE,
PRIMARY_NAME,
REGISTRATION_DAYS,
REGISTRATION_TYPE,
RIGHTS_IND,
SECTOR,
SEC_REGISTRATION_FLAG,
SETTLEMENT_DATE,
SOURCE_CODE,
TRADING_RESTRICTIONS_TYPE,
TRADING_STATUS,
UNIT_IND
FROM IC.I_MASTER
;
CREATE OR REPLACE FORCE EDITIONABLE VIEW "BT"."ORG_MASTER_CUR" ("BOND_TICKER", "INCORPORATED_STATE_CODE", "LOADED_BY", "ORGANIZATION_COUNTRY_CODE", "ORGANIZATION_ID", "ORGANIZATION_STATUS",
"ORGANIZATION_TYPE", "PRIMARY_NAME", "PARENT", "ULT_PARENT", "IS_LEGAL_ENTITY_ROOT") AS
SELECT
BOND_TICKER,
INCORPORATED_STATE_CODE,
LOADED_BY,
ORGANIZATION_COUNTRY_CODE,
ORGANIZATION_ID,
ORGANIZATION_STATUS,
ORGANIZATION_TYPE,
PRIMARY_NAME,
PARENT,
ult_parent,
IS_LEGAL_ENTITY_ROOT
FROM IC.ORG_MASTER
;
CREATE OR REPLACE FORCE EDITIONABLE VIEW "BT"."F_INCOME_CUR" ("ACCRETING_IND", "ACCRETION_BASIS", "ACCRETION_COMPOUNDING_METHOD", "ACCRETION_START_DATE", "ACCRUAL_COMPOUNDING_METHOD",
"ACCRUAL_DATE", "ACCRUAL_DAYS_PRIOR", "ACCRUED_INTEREST_IND", "ACCRUED_ROLL_CONVENTION", "AGENCY_DISC_NOTE_IND", "AMOUNT_OUTSTANDING_RATIO", "AMOUNT_OUTSTANDING_RATIO_DATE", "AVERAGE_LIFE",
"BOND_FORM", "BON
D_INSURER", "BUSINESS_DAY_CONVENTION_CODE", "CALL_IND", "CAPITALIZED_FLAG", "CHILD_IND", "CHILD_ISSUE_INSURANCE_TYPE", "CHILD_ISSUE_INSURED_DATE", "CLASS", "COMPOUND_INTEREST_IND",
"CONTINGENT_INTEREST_IND", "CONVERTIBLE_IND", "COUPON_SUMMARY", "COUPON_TYPE", "COUPON_TYPE_METHOD", "CREDIT_ENHANCEMENT_TYPE", "CREDIT_SENSITIVE_IND", "CREDIT_SENSITIVE_RATE_ADJ_TYPE",
"CURRENT_COUPON_DETERM_DATE", "CU
RRENT_COUPON_NEXT_RESET_DATE", "CURRENT_COUPON_RATE", "CURRENT_COUPON_RESET_DATE", "CURRENT_CPN_NEXT_DETERM_DATE", "DATED_DATE", "DAYCOUNT_BASIS_TYPE", "DEBT_RANK_TYPE", "DEBT_TYPE",
"DEFAULT_IND", "DEFAULT_PERIOD_END_DATE", "DEFAULT_PERIOD_START_DATE", "DEFEASANCE_IND", "DEFERRABLE_IND", "DEFERRED_INTEREST_PERIOD", "DEFERRED_INTEREST_PERIOD_UNITS",
"DEFERRED_INTEREST_TYPE", "DELAYED_FIRST_PAYMENT
_AMOUNT", "DEPOSITORY_TYPE", "DETERM_DATE_OFFSET", "DTC_IND", "EQUITY_LINKED_NOTE_IND", "EUSD_TYPE", "FINAL_COUPON_PAYMENT_DATE", "FIRST_COUPON_DATE", "FIRST_END_ACCRUAL_DATE",
"FIRST_PAYMENT_DATE", "FITCH_DEBT_LEVEL", "GRACE_PERIOD", "GRACE_PERIOD_UNITS", "GREEN_BOND_IND", "GREEN_BOND_THIRD_PARTY", "GROSS_UP_IND", "GUARANTEED_TYPE", "INDEX_LINKED_FIXING_DATE",
"INFLATION_PROTECTED_INDEX", "INFLAT
ION_PROTECTED_INDEX_BASE", "INSTRUMENT_ID", "INTEREST_CALCULATION_METHOD", "INTEREST_PAYMENT_DATE_CODE", "INTEREST_PAYMENT_FREQ", "ISSUE_AMOUNT", "IS_CONVERT_IND", "LAST_ACCRUAL_DATE",
"LAST_COUPON_PERIOD_TYPE", "LAST_DAY_OF_MONTH_PAYMENT_IND", "LOADED_BY", "MATURITY_DATE", "MATURITY_REDEMPTION_PCT", "MATURITY_YEARS", "MOODY_SENIORITY_TYPE", "MORTGAGE_TYPE",
"NEXT_COUPON_CHANGE_DATE", "NEXT_COUPON
_PAYMENT_DATE", "NOMINAL_VALUE", "NO_UNDERWRITER_TYPE", "ODD_FIRST_COUPON_DATE", "OFFERING_TYPE", "OID_IND", "ORIGINAL_MATURITY_DATE", "ORIG_AVERAGE_LIFE_DATE", "ORIG_COUPON_RATE",
"ORIG_COUPON_RATE_IND", "ORIG_INTEREST_PAYMENT_FREQ", "ORIG_PRINCIPAL_AMOUNT", "ORIG_YIELD", "OTHER_ACCRUAL_DATE", "OTHER_TAX_EXEMPT_IND", "OUTSTANDING_AMOUNT", "PAYMENT_MONTHDAY",
"PAYMENT_ROLL_CONVENTION", "PERMANENT_
INTEREST_IND", "PFANDBRIEF_TYPE", "PIK_COUPON_RATE", "PIK_DESC_TYPE", "PIK_END_DATE", "PIK_IND", "PIK_NOTICE", "PIK_NOTICE_UNITS", "PIK_START_DATE", "PRE_REDENOMINATION_AMOUNT",
"PROGRAM_TYPE", "PROSPECTUS_DATE", "PUT_IND", "RANGE_BOND_IND", "RECORD_DATE_FORMULA", "RECORD_DATE_HOLIDAY_RULES", "RECORD_DATE_OFFSET", "RECORD_DATE_RULES", "REMARKETED_COUPON_RATE",
"REMARKETED_IND", "REMARKETED_INTERES
T_ACCRUAL_ST", "SALE_TYPE", "SERIES", "SETTLEMENT_TYPE", "SINK_IND", "SNP_DEBT_TYPE", "SNP_DERIVED_DEBT_TYPE", "SPECIAL_FIRST_PAYMENT", "STATED_FIRST_COUPON_DATE", "STILL_CALLABLE_IND",
"STRIPPABLE_IND", "STRIP_TYPE", "SUBORDINATION_TYPE", "SYNTHETIC_IND", "TAXABLE_IND", "TENDER_EXCHANGE_OFFER_IND", "TIPS_IND", "TRACE_LAST_TRADE_SIZE", "TRACE_LAST_TRADE_TIME", "TRANCHE_IND",
"USTREAS_OTR_TERM", "U
STREAS_OTR_TYPE", "US_TREASURY_TYPE", "VARIABLE_DELAY_RULE", "WARRANTS_IND", "WARRANT_EXPIRATION_DATE") AS
SELECT
ACCRETING_IND,
ACCRETION_BASIS,
ACCRETION_COMPOUNDING_METHOD,
ACCRETION_START_DATE,
ACCRUAL_COMPOUNDING_METHOD,
ACCRUAL_DATE,
ACCRUAL_DAYS_PRIOR,
ACCRUED_INTEREST_IND,
ACCRUED_ROLL_CONVENTION,
AGENCY_DISC_NOTE_IND,
AMOUNT_OUTSTANDING_RATIO,
AMOUNT_OUTSTANDING_RATIO_DATE,
AVERAGE_LIFE,
BOND_FORM,
BOND_INSURER,
BUSINESS_DAY_CONVENTION_CODE,
CALL_IND,
CAPITALIZED_FLAG,
CHILD_IND,
CHILD_ISSUE_INSURANCE_TYPE,
CHILD_ISSUE_INSURED_DATE,
CLASS,
COMPOUND_INTEREST_IND,
CONTINGENT_INTEREST_IND,
CONVERTIBLE_IND,
COUPON_SUMMARY,
COUPON_TYPE,
COUPON_TYPE_METHOD,
CREDIT_ENHANCEMENT_TYPE,
CREDIT_SENSITIVE_IND,
CREDIT_SENSITIVE_RATE_ADJ_TYPE,
CURRENT_COUPON_DETERM_DATE,
CURRENT_COUPON_NEXT_RESET_DATE,
CURRENT_COUPON_RATE,
CURRENT_COUPON_RESET_DATE,
CURRENT_CPN_NEXT_DETERM_DATE,
DATED_DATE,
DAYCOUNT_BASIS_TYPE,
DEBT_RANK_TYPE,
DEBT_TYPE,
DEFAULT_IND,
DEFAULT_PERIOD_END_DATE,
DEFAULT_PERIOD_START_DATE,
DEFEASANCE_IND,
DEFERRABLE_IND,
DEFERRED_INTEREST_PERIOD,
DEFERRED_INTEREST_PERIOD_UNITS,
DEFERRED_INTEREST_TYPE,
DELAYED_FIRST_PAYMENT_AMOUNT,
DEPOSITORY_TYPE,
DETERM_DATE_OFFSET,
DTC_IND,
EQUITY_LINKED_NOTE_IND,
EUSD_TYPE,
FINAL_COUPON_PAYMENT_DATE,
FIRST_COUPON_DATE,
FIRST_END_ACCRUAL_DATE,
FIRST_PAYMENT_DATE,
FITCH_DEBT_LEVEL,
GRACE_PERIOD,
GRACE_PERIOD_UNITS,
GREEN_BOND_IND,
GREEN_BOND_THIRD_PARTY,
GROSS_UP_IND,
GUARANTEED_TYPE,
INDEX_LINKED_FIXING_DATE,
INFLATION_PROTECTED_INDEX,
INFLATION_PROTECTED_INDEX_BASE,
INSTRUMENT_ID,
INTEREST_CALCULATION_METHOD,
INTEREST_PAYMENT_DATE_CODE,
INTEREST_PAYMENT_FREQ,
ISSUE_AMOUNT,
IS_CONVERT_IND,
LAST_ACCRUAL_DATE,
LAST_COUPON_PERIOD_TYPE,
LAST_DAY_OF_MONTH_PAYMENT_IND,
LOADED_BY,
MATURITY_DATE,
MATURITY_REDEMPTION_PCT,
MATURITY_YEARS,
MOODY_SENIORITY_TYPE,
MORTGAGE_TYPE,
NEXT_COUPON_CHANGE_DATE,
NEXT_COUPON_PAYMENT_DATE,
NOMINAL_VALUE,
NO_UNDERWRITER_TYPE,
ODD_FIRST_COUPON_DATE,
OFFERING_TYPE,
OID_IND,
ORIGINAL_MATURITY_DATE,
ORIG_AVERAGE_LIFE_DATE,
ORIG_COUPON_RATE,
ORIG_COUPON_RATE_IND,
ORIG_INTEREST_PAYMENT_FREQ,
ORIG_PRINCIPAL_AMOUNT,
ORIG_YIELD,
OTHER_ACCRUAL_DATE,
OTHER_TAX_EXEMPT_IND,
OUTSTANDING_AMOUNT,
PAYMENT_MONTHDAY,
PAYMENT_ROLL_CONVENTION,
PERMANENT_INTEREST_IND,
PFANDBRIEF_TYPE,
PIK_COUPON_RATE,
PIK_DESC_TYPE,
PIK_END_DATE,
PIK_IND,
PIK_NOTICE,
PIK_NOTICE_UNITS,
PIK_START_DATE,
PRE_REDENOMINATION_AMOUNT,
PROGRAM_TYPE,
PROSPECTUS_DATE,
PUT_IND,
RANGE_BOND_IND,
RECORD_DATE_FORMULA,
RECORD_DATE_HOLIDAY_RULES,
RECORD_DATE_OFFSET,
RECORD_DATE_RULES,
REMARKETED_COUPON_RATE,
REMARKETED_IND,
REMARKETED_INTEREST_ACCRUAL_ST,
SALE_TYPE,
SERIES,
SETTLEMENT_TYPE,
SINK_IND,
SNP_DEBT_TYPE,
SNP_DERIVED_DEBT_TYPE,
SPECIAL_FIRST_PAYMENT,
STATED_FIRST_COUPON_DATE,
STILL_CALLABLE_IND,
STRIPPABLE_IND,
STRIP_TYPE,
SUBORDINATION_TYPE,
SYNTHETIC_IND,
TAXABLE_IND,
TENDER_EXCHANGE_OFFER_IND,
TIPS_IND,
TRACE_LAST_TRADE_SIZE,
TRACE_LAST_TRADE_TIME,
TRANCHE_IND,
USTREAS_OTR_TERM,
USTREAS_OTR_TYPE,
US_TREASURY_TYPE,
VARIABLE_DELAY_RULE,
WARRANTS_IND,
WARRANT_EXPIRATION_DATE
FROM IC.F_INCOME
;
CREATE OR REPLACE FORCE EDITIONABLE VIEW "BT"."I_XREF_CUR" ("EFFECTIVE_DATE_START", "IDENTIFIER", "IDENTIFIER_TYPE", "INSTRUMENT_ID", "LOADED_BY") AS
SELECT
EFFECTIVE_DATE_START,
IDENTIFIER,
IDENTIFIER_TYPE,
INSTRUMENT_ID,
LOADED_BY
FROM BT.I_XREF
WHERE sys_extract_utc(systimestamp) between RECORDED_DATE_START and RECORDED_DATE_END
AND sys_extract_utc(systimestamp) between EFFECTIVE_DATE_START and EFFECTIVE_DATE_END
;
November 06, 2020 - 9:16 am UTC
Sadly I think this may be a case of "you're reading lots of data, it's going to take a while"
The only real filtering is bond_ticker = upper (:tkr), everything else is joins. In both plans the query is returning over 6 million rows at the step before the top-N filtering.
I doubt this will make much difference, but it's worth checking that all the outer joins do need to be outer. Switching to inner may help a little.
Can you add any other filters to the query?