Toad World® Forums

The Index Expert did not find any index


#1

Hi all,

on Sql Optimizer 7.2.0.642

when running Generate Virtual Indexes I receive the following error message :

The Index Expert did not find any index or index set with a unique execution plan using the Custom Intelligence level setting

Please see the picture :

http://auteur-blogeman.blogspot.fr/.

Then I change some options like Intelligence level and …but never works.

I do not know what to choose as value.

Thank for help.


#2

There is a case when all tables in your SQL are properly indexed, our engine may not find any alternative indexes recommendations for you. Please give us your SQL and tables definition, if you want further analysis.


#3

ok, thank you.

here is the SQL :

SELECT PV_REQ_NAME, BUSINESS_UNIT, REQ_ID, REQ_STATUS, PV_REQ_STATUS, HOLD_STATUS, TO_CHAR(REQ_DT,‘YYYY-MM-DD’), ORIGIN, REQUESTOR_ID, OPRID_ENTERED_BY,

OPRID_MODIFIED_BY, OPRID_APPROVED_BY, TO_CHAR(ENTERED_DT,‘YYYY-MM-DD’), TO_CHAR(APPROVAL_DT,‘YYYY-MM-DD’), PO_POST_STATUS, APPL_JRNL_ID_ENC, POST_DOC,

DST_CNTRL_ID, TO_CHAR(ACCOUNTING_DT,‘YYYY-MM-DD’), PROCESS_INSTANCE, IN_PROCESS_FLG, TO_CHAR(ACTIVITY_DATE,‘YYYY-MM-DD’), NEXT_MOD_SEQ_NBR, CURRENCY_CD,

RT_TYPE, RATE_DIV, RATE_MULT, CURRENCY_CD_BASE, BUDGET_HDR_STATUS, KK_AMOUNT_TYPE, KK_TRAN_OVER_FLAG, KK_TRAN_OVER_OPRID,

TO_CHAR(KK_TRAN_OVER_DTTM,‘YYYY-MM-DD-HH24.MI.SS.“000000”’), TO_CHAR(LAST_DTTM_UPDATE,‘YYYY-MM-DD-HH24.MI.SS.“000000”’),

BUSINESS_UNIT_PO, PO_ID, PO_STATUS, RECV_STATUS, CHNG_ORD_BATCH FROM PS_PV_REQ_SRCH_VW WHERE REQ_DT >= TO_DATE(‘2014-06-01’,‘YYYY-MM-DD’)

AND REQ_DT <= TO_DATE(‘2014-07-01’,‘YYYY-MM-DD’) AND (UPPER(PV_REQ_NAME) LIKE ‘%277884%’ OR UPPER(REQ_ID) LIKE ‘%277884%’)

and exists (select ‘X’ from PS_SP_BU_PM_OPRVW RF where RF.BUSINESS_UNIT = PS_PV_REQ_SRCH_VW.BUSINESS_UNIT and RF.OPRID = ‘xxx’)

ORDER BY BUSINESS_UNIT, REQ_ID DESC;


#4

describe command output is not sufficient. You really need to use Toad or some other tool and send the create table commands with their indexes, constraints, etc. Otherwise you’re asking team to guess.


#5

Ok, let’s do that.


#6

How can I attach a file ?

Thank you.


#7

CREATE TABLE MYSYSTEM.PS_REQ_HDR

(

BUSINESS_UNIT VARCHAR2(5 BYTE) NOT NULL,

REQ_ID VARCHAR2(10 BYTE) NOT NULL,

REQ_STATUS VARCHAR2(4 BYTE) NOT NULL,

HOLD_STATUS VARCHAR2(1 BYTE) NOT NULL,

REQ_DT DATE NOT NULL,

ORIGIN VARCHAR2(3 BYTE) NOT NULL,

REQUESTOR_ID VARCHAR2(30 BYTE) NOT NULL,

OPRID_ENTERED_BY VARCHAR2(30 BYTE) NOT NULL,

OPRID_MODIFIED_BY VARCHAR2(30 BYTE) NOT NULL,

OPRID_APPROVED_BY VARCHAR2(30 BYTE) NOT NULL,

ENTERED_DT DATE,

APPROVAL_DT DATE,

PO_POST_STATUS VARCHAR2(1 BYTE) NOT NULL,

APPL_JRNL_ID_ENC VARCHAR2(10 BYTE) NOT NULL,

POST_DOC VARCHAR2(1 BYTE) NOT NULL,

DST_CNTRL_ID VARCHAR2(10 BYTE) NOT NULL,

ACCOUNTING_DT DATE,

PROCESS_INSTANCE NUMBER(10) NOT NULL,

IN_PROCESS_FLG VARCHAR2(1 BYTE) NOT NULL,

ACTIVITY_DATE DATE,

NEXT_MOD_SEQ_NBR INTEGER NOT NULL,

CURRENCY_CD VARCHAR2(3 BYTE) NOT NULL,

RT_TYPE VARCHAR2(5 BYTE) NOT NULL,

RATE_DIV NUMBER(15,8) NOT NULL,

RATE_MULT NUMBER(15,8) NOT NULL,

CURRENCY_CD_BASE VARCHAR2(3 BYTE) NOT NULL,

BUDGET_HDR_STATUS VARCHAR2(1 BYTE) NOT NULL,

KK_AMOUNT_TYPE VARCHAR2(1 BYTE) NOT NULL,

KK_TRAN_OVER_FLAG VARCHAR2(1 BYTE) NOT NULL,

KK_TRAN_OVER_OPRID VARCHAR2(30 BYTE) NOT NULL,

KK_TRAN_OVER_DTTM DATE,

LAST_DTTM_UPDATE DATE,

USER_HDR_CHAR1 VARCHAR2(1 BYTE) NOT NULL,

BUDGET_CHECK VARCHAR2(1 BYTE) NOT NULL,

CHNG_ORD_BATCH INTEGER NOT NULL,

CHANGE_STATUS VARCHAR2(1 BYTE) NOT NULL,

CHNG_ORD_SEQ INTEGER NOT NULL,

INCREMENT_FLG VARCHAR2(1 BYTE) NOT NULL

)

TABLESPACE POAPP

PCTUSED 80

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 13736K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

CREATE UNIQUE INDEX MYSYSTEM.PS_REQ_HDR ON MYSYSTEM.PS_REQ_HDR

(BUSINESS_UNIT, REQ_ID)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE INDEX MYSYSTEM.PS0REQ_HDR ON MYSYSTEM.PS_REQ_HDR

(REQUESTOR_ID, BUSINESS_UNIT, REQ_ID)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE INDEX MYSYSTEM.PSAREQ_HDR ON MYSYSTEM.PS_REQ_HDR

(REQ_STATUS, BUSINESS_UNIT, REQ_ID)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE TABLE MYSYSTEM.PS_PV_REQ_HDR_DTL

(

BUSINESS_UNIT VARCHAR2(5 BYTE) NOT NULL,

REQ_ID VARCHAR2(10 BYTE) NOT NULL,

PV_REQ_NAME VARCHAR2(30 BYTE) NOT NULL,

PV_REQ_TEMPLATE_FL VARCHAR2(1 BYTE) NOT NULL,

CREDIT_CARD_VENDOR VARCHAR2(5 BYTE) NOT NULL,

CRDMEM_ACCT_NBR VARCHAR2(20 BYTE) NOT NULL,

EXPIRATION_DT DATE,

PV_GHOST_CARD VARCHAR2(3 BYTE) NOT NULL,

PV_UPDATE_FLAG VARCHAR2(1 BYTE) NOT NULL,

PV_ADD_FLAG VARCHAR2(1 BYTE) NOT NULL,

PV_PRIORITY_FLG VARCHAR2(1 BYTE) NOT NULL

)

TABLESPACE PVAPP

PCTUSED 80

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 4360K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

CREATE UNIQUE INDEX MYSYSTEM.PS_PV_REQ_HDR_DTL ON MYSYSTEM.PS_PV_REQ_HDR_DTL

(BUSINESS_UNIT, REQ_ID)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE TABLE MYSYSTEM.PS_PO_LINE_DISTRIB

(

BUSINESS_UNIT VARCHAR2(5 BYTE) NOT NULL,

PO_ID VARCHAR2(10 BYTE) NOT NULL,

LINE_NBR INTEGER NOT NULL,

SCHED_NBR INTEGER NOT NULL,

DST_ACCT_TYPE VARCHAR2(4 BYTE) NOT NULL,

DISTRIB_LINE_NUM INTEGER NOT NULL,

QTY_PO NUMBER(15,4) NOT NULL,

CURRENCY_CD VARCHAR2(3 BYTE) NOT NULL,

MERCHANDISE_AMT NUMBER(26,3) NOT NULL,

CURRENCY_CD_BASE VARCHAR2(3 BYTE) NOT NULL,

MERCH_AMT_BSE NUMBER(26,3) NOT NULL,

LOCATION VARCHAR2(10 BYTE) NOT NULL,

ACCOUNT VARCHAR2(10 BYTE) NOT NULL,

ALTACCT VARCHAR2(10 BYTE) NOT NULL,

DEPTID VARCHAR2(10 BYTE) NOT NULL,

OPERATING_UNIT VARCHAR2(8 BYTE) NOT NULL,

PRODUCT VARCHAR2(6 BYTE) NOT NULL,

FUND_CODE VARCHAR2(5 BYTE) NOT NULL,

CLASS_FLD VARCHAR2(5 BYTE) NOT NULL,

PROGRAM_CODE VARCHAR2(5 BYTE) NOT NULL,

BUDGET_REF VARCHAR2(8 BYTE) NOT NULL,

AFFILIATE VARCHAR2(5 BYTE) NOT NULL,

AFFILIATE_INTRA1 VARCHAR2(10 BYTE) NOT NULL,

AFFILIATE_INTRA2 VARCHAR2(10 BYTE) NOT NULL,

CHARTFIELD1 VARCHAR2(10 BYTE) NOT NULL,

CHARTFIELD2 VARCHAR2(10 BYTE) NOT NULL,

CHARTFIELD3 VARCHAR2(10 BYTE) NOT NULL,

PROJECT_ID VARCHAR2(15 BYTE) NOT NULL,

STATISTICS_CODE VARCHAR2(3 BYTE) NOT NULL,

STATISTIC_AMOUNT NUMBER(15,2) NOT NULL,

CHARTFIELD_STATUS VARCHAR2(1 BYTE) NOT NULL,

BUSINESS_UNIT_GL VARCHAR2(5 BYTE) NOT NULL,

DISTRIB_LN_STATUS VARCHAR2(1 BYTE) NOT NULL,

DIST_PROCESSED_FLG VARCHAR2(1 BYTE) NOT NULL,

SYSTEM_SOURCE VARCHAR2(3 BYTE) NOT NULL,

BUSINESS_UNIT_REQ VARCHAR2(5 BYTE) NOT NULL,

BUSINESS_UNIT_PC VARCHAR2(5 BYTE) NOT NULL,

ACTIVITY_ID VARCHAR2(15 BYTE) NOT NULL,

ANALYSIS_TYPE VARCHAR2(3 BYTE) NOT NULL,

RESOURCE_TYPE VARCHAR2(5 BYTE) NOT NULL,

RESOURCE_CATEGORY VARCHAR2(5 BYTE) NOT NULL,

RESOURCE_SUB_CAT VARCHAR2(5 BYTE) NOT NULL,

PROCESS_INSTANCE NUMBER(10) NOT NULL,

PROCESS_MAN_CLOSE VARCHAR2(1 BYTE) NOT NULL,

REQ_ID VARCHAR2(10 BYTE) NOT NULL,

REQ_LINE_NBR INTEGER NOT NULL,

REQ_SCHED_NBR INTEGER NOT NULL,

REQ_DISTRIB_NBR INTEGER NOT NULL,

PO_POST_AMT NUMBER(26,3) NOT NULL,

PO_POST_AMT_BSE NUMBER(26,3) NOT NULL,

PC_DISTRIB_STATUS VARCHAR2(1 BYTE) NOT NULL,

PC_DISTRIB_AMT NUMBER(26,3) NOT NULL,

PC_DISTRIB_AMT_BSE NUMBER(26,3) NOT NULL,

PROFILE_ID VARCHAR2(10 BYTE) NOT NULL,

TAG_NUMBER VARCHAR2(12 BYTE) NOT NULL,

CAP_NUM VARCHAR2(15 BYTE) NOT NULL,

CAP_SEQUENCE INTEGER NOT NULL,

EMPLID VARCHAR2(11 BYTE) NOT NULL,

BUSINESS_UNIT_AM VARCHAR2(5 BYTE) NOT NULL,

BUSINESS_UNIT_IN VARCHAR2(5 BYTE) NOT NULL,

PO_POST_STATUS VARCHAR2(1 BYTE) NOT NULL,

CLOSE_AMOUNT NUMBER(26,3) NOT NULL,

CLOSE_AMOUNT_BSE NUMBER(26,3) NOT NULL,

QTY_REQ NUMBER(15,4) NOT NULL,

DISTRIB_TYPE VARCHAR2(10 BYTE) NOT NULL,

DISTRIB_PCT NUMBER(7,4) NOT NULL,

INVOICE_CLOSE_IND VARCHAR2(1 BYTE) NOT NULL,

FINANCIAL_ASSET_SW VARCHAR2(1 BYTE) NOT NULL,

COST_TYPE VARCHAR2(1 BYTE) NOT NULL,

TAX_CD_SUT VARCHAR2(8 BYTE) NOT NULL,

TAX_CD_SUT_PCT NUMBER(5,2) NOT NULL,

SALETX_AMT NUMBER(26,3) NOT NULL,

SALETX_AMT_BSE NUMBER(26,3) NOT NULL,

USETAX_AMT NUMBER(26,3) NOT NULL,

USETAX_AMT_BSE NUMBER(26,3) NOT NULL,

SUT_APPLICABILITY VARCHAR2(1 BYTE) NOT NULL,

VAT_TXN_TYPE_CD VARCHAR2(4 BYTE) NOT NULL,

VAT_APPLICABILITY VARCHAR2(1 BYTE) NOT NULL,

TAX_CD_VAT VARCHAR2(8 BYTE) NOT NULL,

TAX_CD_VAT_PCT NUMBER(7,4) NOT NULL,

VAT_AMT NUMBER(26,3) NOT NULL,

VAT_AMT_BASE NUMBER(26,3) NOT NULL,

VAT_BASIS_AMT NUMBER(26,3) NOT NULL,

VAT_BASIS_AMT_BSE NUMBER(26,3) NOT NULL,

VAT_RECOVERY_PCT NUMBER(5,2) NOT NULL,

VAT_RCVRY_AMT NUMBER(26,3) NOT NULL,

VAT_RCVRY_AMT_BSE NUMBER(26,3) NOT NULL,

VAT_REBATE_PCT NUMBER(5,2) NOT NULL,

VAT_REBATE_AMT NUMBER(26,3) NOT NULL,

VAT_REBATE_AMT_BSE NUMBER(26,3) NOT NULL,

VAT_USE_ID VARCHAR2(6 BYTE) NOT NULL,

RT_TYPE VARCHAR2(5 BYTE) NOT NULL,

RATE_MULT NUMBER(15,8) NOT NULL,

RATE_DIV NUMBER(15,8) NOT NULL,

FREIGHT_AMT NUMBER(26,3) NOT NULL,

FREIGHT_AMT_BSE NUMBER(26,3) NOT NULL,

FREIGHT_AMT_NP NUMBER(26,3) NOT NULL,

FREIGHT_AMT_NP_BSE NUMBER(26,3) NOT NULL,

MISC_AMT NUMBER(26,3) NOT NULL,

MISC_AMT_BSE NUMBER(26,3) NOT NULL,

MISC_AMT_NP NUMBER(26,3) NOT NULL,

MISC_AMT_NP_BSE NUMBER(26,3) NOT NULL,

MONETARY_AMOUNT NUMBER(26,3) NOT NULL,

MONETARY_AMT_BSE NUMBER(26,3) NOT NULL,

BUDGET_DT DATE,

BUDGET_LINE_STATUS VARCHAR2(1 BYTE) NOT NULL,

KK_CLOSE_FLAG VARCHAR2(1 BYTE) NOT NULL,

KK_PROCESS_PRIOR VARCHAR2(1 BYTE) NOT NULL,

VAT_CALC_TYPE VARCHAR2(1 BYTE) NOT NULL,

SALETX_PRORATE_FLG VARCHAR2(1 BYTE) NOT NULL,

USETAX_PRORATE_FLG VARCHAR2(1 BYTE) NOT NULL,

VAT_NRCVR_PRO_FLG VARCHAR2(1 BYTE) NOT NULL,

VAT_PRORATE_FLG VARCHAR2(1 BYTE) NOT NULL,

CONSIGNED_FLAG VARCHAR2(1 BYTE) NOT NULL,

VAT_RCVRY_PCT_SRC VARCHAR2(1 BYTE) NOT NULL,

VAT_REBATE_PCT_SRC VARCHAR2(1 BYTE) NOT NULL,

VAT_TRANS_AMT NUMBER(26,3) NOT NULL,

VAT_TRANS_AMT_BSE NUMBER(26,3) NOT NULL,

PUBLISHED_IBU VARCHAR2(5 BYTE) NOT NULL,

VAT_APORT_CNTRL VARCHAR2(1 BYTE) NOT NULL,

KK_CLOSE_PRIOR VARCHAR2(1 BYTE) NOT NULL,

DOC_TOL_LN_STATUS VARCHAR2(1 BYTE) NOT NULL,

ROLL_STAT_R VARCHAR2(1 BYTE) NOT NULL,

USER_DIST_CHAR1 VARCHAR2(1 BYTE) NOT NULL,

ENTRY_EVENT VARCHAR2(10 BYTE) NOT NULL

)

TABLESPACE POLARGE

PCTUSED 80

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 57992K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

CREATE UNIQUE INDEX MYSYSTEM.PS_PO_LINE_DISTRIB ON MYSYSTEM.PS_PO_LINE_DISTRIB

(BUSINESS_UNIT, PO_ID, LINE_NBR, SCHED_NBR, DST_ACCT_TYPE,

DISTRIB_LINE_NUM)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE INDEX MYSYSTEM.PSAPO_LINE_DISTRIB ON MYSYSTEM.PS_PO_LINE_DISTRIB

(REQ_ID, BUSINESS_UNIT)

LOGGING

TABLESPACE PSINDEX_M

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 512K

NEXT 512K

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

CREATE TABLE MYSYSTEM.PS_PO_HDR
(
BUSINESS_UNIT VARCHAR2(5 BYTE) NOT NULL,
PO_ID VARCHAR2(10 BYTE) NOT NULL,
CHNG_ORD_BATCH INTEGER NOT NULL,
PO_TYPE VARCHAR2(4 BYTE) NOT NULL,
PO_STATUS VARCHAR2(2 BYTE) NOT NULL,
HOLD_STATUS VARCHAR2(1 BYTE) NOT NULL,
RECV_STATUS VARCHAR2(1 BYTE) NOT NULL,
DISP_ACTION VARCHAR2(1 BYTE) NOT NULL,
DISP_METHOD VARCHAR2(3 BYTE) NOT NULL,
CHANGE_STATUS VARCHAR2(1 BYTE) NOT NULL,
PO_DT DATE NOT NULL,
PO_REF VARCHAR2(30 BYTE) NOT NULL,
VENDOR_SETID VARCHAR2(5 BYTE) NOT NULL,
VENDOR_ID VARCHAR2(10 BYTE) NOT NULL,
VNDR_LOC VARCHAR2(10 BYTE) NOT NULL,
PRICE_SETID VARCHAR2(5 BYTE) NOT NULL,
PRICE_VENDOR VARCHAR2(10 BYTE) NOT NULL,
PRICE_LOC VARCHAR2(10 BYTE) NOT NULL,
PYMNT_TERMS_CD VARCHAR2(5 BYTE) NOT NULL,
BUYER_ID VARCHAR2(30 BYTE) NOT NULL,
ORIGIN VARCHAR2(3 BYTE) NOT NULL,
CHNG_ORD_SEQ INTEGER NOT NULL,
ADDRESS_SEQ_NUM INTEGER NOT NULL,
CNTCT_SEQ_NUM INTEGER &nbsp


#8

The object PS_PV_REQ_SRCH_VW is undefined, I think it is view on top of those tables, please provide the create view SQL.


#9

Thank you.

Here it is :

SELECT DISTINCT A.PV_REQ_NAME , A.BUSINESS_UNIT , A.REQ_ID , A.REQ_STATUS , A.REQ_STATUS , A.HOLD_STATUS , A.REQ_DT ,

A.ORIGIN , A.REQUESTOR_ID , A.OPRID_ENTERED_BY , A.OPRID_MODIFIED_BY , A.OPRID_APPROVED_BY , A.ENTERED_DT , A.APPROVAL_DT

, A.PO_POST_STATUS , A.APPL_JRNL_ID_ENC , A.POST_DOC , A.DST_CNTRL_ID , A.ACCOUNTING_DT , A.PROCESS_INSTANCE , A.IN_PROCESS_FLG ,

A.ACTIVITY_DATE , A.NEXT_MOD_SEQ_NBR , A.CURRENCY_CD , A.RT_TYPE , A.RATE_DIV , A.RATE_MULT , A.CURRENCY_CD_BASE ,

A.BUDGET_HDR_STATUS , A.KK_AMOUNT_TYPE , A.KK_TRAN_OVER_FLAG , A.KK_TRAN_OVER_OPRID , A.KK_TRAN_OVER_DTTM , A.LAST_DTTM_UPDATE ,

B.BUSINESS_UNIT , B.PO_ID

, C.PO_STATUS , C.RECV_STATUS , C.CHNG_ORD_BATCH FROM PS_PV_REQ_SRCH_VW2 A , PS_PO_LINE_DISTRIB B , PS_PO_HDR C

WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PO_ID = C.PO_ID AND

C.PO_STATUS <> ‘C’ AND B.REQ_ID <> ’ ’ UNION SELECT DISTINCT A.PV_REQ_NAME , A.BUSINESS_UNIT , A.REQ_ID , A.REQ_STATUS , A.REQ_STATUS , A.HOLD_STATUS , A.REQ_DT, A.ORIGIN , A.REQUESTOR_ID , A.OPRID_ENTERED_BY , A.OPRID_MODIFIED_BY , A.OPRID_APPROVED_BY , A.ENTERED_DT , A.APPROVAL_DT ,

A.PO_POST_STATUS , A.APPL_JRNL_ID_ENC , A.POST_DOC , A.DST_CNTRL_ID , A.ACCOUNTING_DT , A.PROCESS_INSTANCE , A.IN_PROCESS_FLG , A.ACTIVITY_DATE , A.NEXT_MOD_SEQ_NBR , A.CURRENCY_CD , A.RT_TYPE

, A.RATE_DIV , A.RATE_MULT , A.CURRENCY_CD_BASE , A.BUDGET_HDR_STATUS , A.KK_AMOUNT_TYPE , A.KK_TRAN_OVER_FLAG , A.KK_TRAN_OVER_OPRID ,

A.KK_TRAN_OVER_DTTM , A.LAST_DTTM_UPDATE , CAST(NULL AS CHAR(5)) , CAST(NULL AS CHAR(10)) , CAST(NULL AS

CHAR(1) ) , CAST(NULL AS CHAR(1) ) , 0 FROM PS_PV_REQ_SRCH_VW2 A WHERE NOT EXISTS( SELECT ‘X’ FROM PS_PO_LINE_DISTRIB B , PS_PO_HDR C WHERE

A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND

B.PO_ID = C.PO_ID AND C.PO_STATUS <> ‘C’ AND B.REQ_ID <> ’ ') ;


#10

How about this view PS_PV_REQ_SRCH_VW2 ?

We need the create view SQL statements!


#11

Thanks. Here it is :

CREATE OR REPLACE FORCE VIEW MYSYSTEM.ps_pv_req_srch_vw2 (pv_req_name,
business_unit,
req_id,
req_status,
pv_req_status,
hold_status,
req_dt,
origin,
requestor_id,
oprid_entered_by,
oprid_modified_by,
oprid_approved_by,
entered_dt,
approval_dt,
po_post_status,
appl_jrnl_id_enc,
post_doc,
dst_cntrl_id,
accounting_dt,
process_instance,
in_process_flg,
activity_date,
next_mod_seq_nbr,
currency_cd,
rt_type,
rate_div,
rate_mult,
currency_cd_base,
budget_hdr_status,
kk_amount_type,
kk_tran_over_flag,
kk_tran_over_oprid,
kk_tran_over_dttm,
last_dttm_update
)
AS
SELECT pv_req_name, a.business_unit, a.req_id, a.req_status, a.req_status,
a.hold_status, a.req_dt, a.origin, a.requestor_id,
a.oprid_entered_by, a.oprid_modified_by, a.oprid_approved_by,
a.entered_dt, a.approval_dt, a.po_post_status, a.appl_jrnl_id_enc,
a.post_doc, a.dst_cntrl_id, a.accounting_dt, a.process_instance,
a.in_process_flg, a.activity_date, a.next_mod_seq_nbr,
a.currency_cd, a.rt_type, a.rate_div, a.rate_mult,
a.currency_cd_base, a.budget_hdr_status, a.kk_amount_type,
a.kk_tran_over_flag, a.kk_tran_over_oprid, a.kk_tran_over_dttm,
a.last_dttm_update
FROM ps_req_hdr a, ps_pv_req_hdr_dtl b
WHERE a.req_id = b.req_id(+) AND a.business_unit = b.business_unit(+);

GRANT SELECT ON MYSYSTEM.PS_PV_REQ_SRCH_VW2 TO CCC;


#12

Although I still cannot execute your original SQL(view PS_SP_BU_PM_OPRVW is still missing), I think your problem is not missing index. I did some show plans on your view’s SQL and rewrite your SQL with our engine, it has generated a lot of alternative SQL(I am using DELL SQL Optimizer version 8.9, you better upgrade you product),

I recommend you to start optimize your view’s SQL first especially for those complex views’ SQL. If you want to optimize the original SQL only and you don’t want touch the underlining views, you must increase the Intelligent Level and let our engine to try merging views SQL into your original SQL. It may help database internal optimizer to make better plan.


#13

Thank you.

Intelligent Level has been already 10.

How to let engine to try merging views SQL into original SQL ?


#14

You can increase intelligent level to 4 or higher, it will automatically enable.

ScreenHunter_86 Jul. 04 14.12.jpeg

Or use Custome Settings to manual select “Transform view to inline view”, if you want to transform nested views, you can control how deep you want by setting the transform level.

ScreenHunter_86 Jul. 04 14.09.jpeg


#15

Thanks.