Hints In BO 6.5

[system]

Hi Everyone,

We are migrating the reports from 5.x to 6.5 and the 5.x reports are using HInts to optimize the queries.I am of the view that 6.5 doesnot support Hints can anyone pls confirm on that.Also I ran the migrated reports with hints and it doesnot work.When I removed the Hints from 6.5 reports it works perfectly fine.

Any help in this regard would be greatly appreciated.

Thanks!!
Chandra

---

BOConsul (BOB member since 2005-01-12)

[system]

what do you mean by “hints” ???

---

bernard timbal (BOB member since 2003-05-26)

[system]

Hints are select statements that are used to optimize the queries…basically to improve the performance.

PLs find the attached query which uses hints…

SELECT
/*+ INDEX ( GL_CODE_COMBINATIONS GL_CODE_COMBINATIONS_U1 ) / ‘’,
PO_VENDORS.VENDOR_NAME,
PO_VENDORS.WOMEN_OWNED_FLAG,
PO_VENDORS.MINORITY_GROUP_LOOKUP_CODE,
FLEX_SEGMENT3.FLEX_VALUE,
sum(nvl(AP.AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,0)),
substr(FLEX_SEGMENT5.FLEX_VALUE,1,1),
substr(FLEX_SEGMENT5.FLEX_VALUE,1,1)||‘ZZZZZ’,
substr(FLEX_SEGMENT5.FLEX_VALUE,1,2)||‘ZZZZ’,
substr(FLEX_SEGMENT5.FLEX_VALUE,1,2)
FROM
GL.GL_SETS_OF_BOOKS,
PO_VENDORS,
APPLSYS.FND_FLEX_VALUES FLEX_SEGMENT3,
APPLSYS.FND_FLEX_VALUE_SETS FND_FLEX_VALUE_SETS_Seg3,
AP.AP_INVOICE_DISTRIBUTIONS_ALL,
APPLSYS.FND_FLEX_VALUES FLEX_SEGMENT5,
APPLSYS.FND_FLEX_VALUE_SETS FND_FLEX_VALUE_SETS_Seg5,
AP.AP_INVOICE_PAYMENTS_ALL,
GL.GL_CODE_COMBINATIONS,
AP.AP_INVOICES_ALL
WHERE
( AP.AP_INVOICES_ALL.INVOICE_ID=AP.AP_INVOICE_PAYMENTS_ALL.INVOICE_ID )
AND ( AP.AP_INVOICES_ALL.INVOICE_ID=AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID(+) )
AND ( GL.GL_CODE_COMBINATIONS.SEGMENT3=FLEX_SEGMENT3.FLEX_VALUE )
AND ( AP.AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID = GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID )
AND ( GL.GL_CODE_COMBINATIONS.CHART_OF_ACCOUNTS_ID = 101 AND GL.GL_CODE_COMBINATIONS.TEMPLATE_ID IS NULL
)
AND ( GL.GL_CODE_COMBINATIONS.SEGMENT5=FLEX_SEGMENT5.FLEX_VALUE )
AND ( AP.AP_INVOICES_ALL.VENDOR_ID=PO_VENDORS.VENDOR_ID )
AND ( FLEX_SEGMENT3.FLEX_VALUE_SET_ID=FND_FLEX_VALUE_SETS_Seg3.FLEX_VALUE_SET_ID )
AND ( FLEX_SEGMENT5.FLEX_VALUE_SET_ID=FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_ID )
AND ( AP.AP_INVOICES_ALL.SET_OF_BOOKS_ID=GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID )
AND ( ( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg3.FLEX_VALUE_SET_NAME = ‘XXCUS_ACCOUNT’
)
AND ( ( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_NAME = ‘XXCUS_COST_CENTER’
)
AND ( ( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_NAME = ‘XXCUS_COST_CENTER’
)
AND ( ( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_NAME = ‘XXCUS_COST_CENTER’
)
AND ( ( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_NAME = ‘XXCUS_COST_CENTER’
)
AND (
(PO_VENDORS.WOMEN_OWNED_FLAG = ‘Y’
OR PO_VENDORS.MINORITY_GROUP_LOOKUP_CODE IN (‘AFRICAN/BLACK’, ‘NATIVE AMERICAN’, ‘HISPANIC AMERICAN’, ‘ASIAN/PACIFIC’, ‘ASIAN/INDIAN’))
AND AP.AP_INVOICE_PAYMENTS_ALL.CREATION_DATE BETWEEN @variable(‘1. Enter Payment Begin Date:’) AND @variable(‘2. Enter Payment End Date:’)
AND FLEX_SEGMENT5.FLEX_VALUE LIKE @variable(‘3. Cost Center Matches’) AND (( GL.GL_SETS_OF_BOOKS.NAME ) IN @Prompt(‘Sets of Books Name’,‘A’, ‘Invoices\Set of Books Name’, Multi, Free) and FND_FLEX_VALUE_SETS_Seg5.FLEX_VALUE_SET_NAME = ‘XXCUS_COST_CENTER’
)
)
GROUP BY
/+ INDEX ( GL_CODE_COMBINATIONS GL_CODE_COMBINATIONS_U1 ) */ ‘’,
PO_VENDORS.VENDOR_NAME,
PO_VENDORS.WOMEN_OWNED_FLAG,
PO_VENDORS.MINORITY_GROUP_LOOKUP_CODE,
FLEX_SEGMENT3.FLEX_VALUE,
substr(FLEX_SEGMENT5.FLEX_VALUE,1,1),
substr(FLEX_SEGMENT5.FLEX_VALUE,1,1)||‘ZZZZZ’,
substr(FLEX_SEGMENT5.FLEX_VALUE,1,2)||‘ZZZZ’,
substr(FLEX_SEGMENT5.FLEX_VALUE,1,2)

Thanks,
Chandra

---

BOConsul (BOB member since 2005-01-12)

[system]

@BOConsul

When I removed the Hints from 6.5 reports it works perfectly fine.

I would be surprised if oracle database hints don’t work in version 6. You didn’t say how they were working in v5 i.e. the first object is a hint or you frigged the sql or you added one at the connection level or…?

When it doesn’t work, do you mean that the query runs forever, or something else…

---

Nick Daniels (BOB member since 2002-08-15)

[system]

In Version 5 the first Object was a hint and in the 6.5 when i run the same report which was migrated it gives the error that the report cannot be refreshed.
I later removed the Hint object and it works fine…

Thanks,
Chandra

---

BOConsul (BOB member since 2005-01-12)

[system]

thank you, I haven’t never heard about “hints”, I will go to bed a bitt less stupid tonight

GL_CODE_COMBINATIONS / FND_FLEX_VALUES FLEX_SEGMENT3,

hey, you are working on Oracle Application isn’t it ?

---

bernard timbal (BOB member since 2003-05-26)

[system]

This previous post on hints seems to indicate that there maybe an issue in 6.5 although I haven’t used hints in 6.5 myself.

Adding hints to queries

[Edit: - Cindy - Moving this cool suggestion to the Designer forum] I know that you where only using the addition of Hints as an example of your point but for what it is worth you can add hints in your generated SQL. You will want to create a dimension variable in the universe something like this: /*+ the hint */ ‘’ the two ’ at the end are important. You then want to make sure that you use the created dimension object is the first object in your results objects selection within the query …

---

irish_stan (BOB member since 2003-05-13)

[system]

@irish_stan

This previous post on hints seems to indicate that there maybe an issue in 6.5

Interesting…BOConsul, it would be useful if you could post the exact message you get when trying to refresh in v6. All goes to show that if you can get away without using hints, then do!

---

Nick Daniels (BOB member since 2002-08-15)

[system]

@Nick Daniels

@irish_stan

This previous post on hints seems to indicate that there maybe an issue in 6.5

Interesting…BOConsul, it would be useful if you could post the exact message you get when trying to refresh in v6. All goes to show that if you can get away without using hints, then do!

Thanks all,

The following is the error message which i get when i refresh that .rep document in the infoview

Automation exception BusinessObjects server process raised an automation exception. (Error: BOMGR 0060) IDispatch error #109 The following data providers have not been successfully refreshed: FIN. (DMA0007): [] (CEGIN65-OMF-02V.Ceg.Corp.Net)
(returned by function Refreshing Document) )

The following is the error which i get when i refresh that report in the 3- tier business OBjects.

The following data providers have not been successfully refreshed: FIN(DMA0007)

Thanks,
CHandra

---

BOConsul (BOB member since 2005-01-12)

[system]

So PPl can I assume that Business OBjects 6.5 does not support Hints in the query…

Thanks
Chandra

---

BOConsul (BOB member since 2005-01-12)

[system]

we experienced the same problem and were supplied with a hotfix (837) from Business Objects which resolved the problem. Bug 1104062 was raised for the Index Awareness issue

---

mathib (BOB member since 2004-10-27)