Hi,
can you apply Oracle hints to an Object in the actual Object definiton or does it have to be applied at the join level ?
Basically even though I have defined the joins as the keys, when I create big reports and check the sql in show plan I get a number of full table scans, I want to avoid these at all cost ?
Rich 
(BOB member since 2002-10-04)
Ok Im feeling lonely now 
Rich (BOB member since 2002-10-04)
Sometimes we donât answer 'cause the answer can be found by doing a search 
. There were 21 posts with âhint and objectâ in the Designer forum.
Cindy Clayton 
(BOB member since 2002-06-11)
Hi Rich
You are not alone, please do a search,
there are already posts talking about
about using hints at the object level
in the universe
âOracle Hintsâ
I will also look around if I find i will let you know
gowthaman (BOB member since 2002-08-15)
Not âat all costsâ surely. Whatâs the problem with full table scans?
slimdave 
(BOB member since 2002-09-10)
I assumed the Full Table scans were the problem. I will be honest Im pretty much on my own here, the level of DBA support I have is nil.
The showplan Ive got for a query that wont run is as follows, in you opionion does this look exccesive ??
SELECT STATEMENT Optimizer=CHOOSE (Cost=2894 Card=1 Bytes=388)
FILTER
NESTED LOOPS (Cost=2894 Card=1 Bytes=388)
NESTED LOOPS (OUTER) (Cost=2893 Card=1 Bytes=368)
NESTED LOOPS (OUTER) (Cost=2892 Card=1 Bytes=344)
NESTED LOOPS (OUTER) (Cost=2891 Card=1 Bytes=319)
NESTED LOOPS (OUTER) (Cost=2890 Card=1 Bytes=298)
NESTED LOOPS (Cost=2889 Card=1 Bytes=273)
NESTED LOOPS (Cost=2888 Card=1 Bytes=219)
NESTED LOOPS (Cost=2829 Card=1 Bytes=171)
NESTED LOOPS (Cost=2828 Card=1 Bytes=111)
NESTED LOOPS (Cost=2827 Card=1 Bytes=83)
TABLE ACCESS (BY INDEX ROWID) OF PSOPRDEFN (Cost=1 Card=1 Bytes=16)
INDEX (UNIQUE SCAN) OF PS_PSOPRDEFN (UNIQUE) (Cost=1 Card=1)
TABLE ACCESS (FULL) OF PS_JOB (Cost=2826 Card=1 Bytes=67)
INDEX (FULL SCAN) OF PS0LOCATION_TBL (NON-UNIQUE) (Cost=1 Card=84 Bytes=2352)
SORT (AGGREGATE)
TABLE ACCESS (FULL) OF PS_LOCATION_TBL (Cost=2 Card=1 Bytes=17)
TABLE ACCESS (BY INDEX ROWID) OF PS_DEPT_TBL (Cost=1 Card=192 Bytes=11520)
INDEX (RANGE SCAN) OF PS_DEPT_TBL (UNIQUE) (Cost=1 Card=192)
SORT (AGGREGATE)
TABLE ACCESS (BY INDEX ROWID) OF PS_DEPT_TBL (Cost=2 Card=1 Bytes=23)
INDEX (RANGE SCAN) OF PS_DEPT_TBL (UNIQUE) (Cost=2 Card=1)
TABLE ACCESS (FULL) OF PS_ING_STRUCT_DNRM (Cost=59 Card=556 Bytes=26688)
TABLE ACCESS (BY INDEX ROWID) OF PS_PERSONAL_DATA (Cost=1 Card=31900 Bytes=1722600)
INDEX (UNIQUE SCAN) OF PS_PERSONAL_DATA (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF PS_PERS_NID (Cost=1 Card=2244 Bytes=56100)
INDEX (RANGE SCAN) OF PS_PERS_NID (UNIQUE) (Cost=1 Card=2244)
TABLE ACCESS (BY INDEX ROWID) OF PS_PERS_NID (Cost=1 Card=2244 Bytes=47124)
INDEX (RANGE SCAN) OF PS_PERS_NID (UNIQUE) (Cost=1 Card=2244)
TABLE ACCESS (BY INDEX ROWID) OF PS_PERSONAL_DATA (Cost=1 Card=31900 Bytes=797500)
INDEX (UNIQUE SCAN) OF PS_PERSONAL_DATA (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF PS_EMPLOYMENT (Cost=1 Card=32280 Bytes=774720)
INDEX (RANGE SCAN) OF PS_EMPLOYMENT (UNIQUE) (Cost=1 Card=32280)
INDEX (UNIQUE SCAN) OF PS_FAST_SCRTY_2 (UNIQUE)
SORT (AGGREGATE)
INDEX (FAST FULL SCAN) OF PS_ING_STRUCT_DNRM (UNIQUE) (Cost=12 Card=2 Bytes=44)
SORT (AGGREGATE)
FIRST ROW (Cost=3 Card=6 Bytes=114)
INDEX (RANGE SCAN (MIN/MAX)) OF PSAJOB (NON-UNIQUE) (Cost=3 Card=6)
SORT (AGGREGATE)
FIRST ROW (Cost=3 Card=1 Bytes=22)
INDEX (RANGE SCAN (MIN/MAX)) OF PSAJOB (NON-UNIQUE) (Cost=3 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF PS_JOB (Cost=4 Card=1 Bytes=13)
INDEX (RANGE SCAN) OF PSAJOB (NON-UNIQUE) (Cost=3 Card=1)
FILTER
TABLE ACCESS (BY INDEX ROWID) OF PS_JOB (Cost=2 Card=1 Bytes=21)
INDEX (RANGE SCAN) OF PSAJOB (NON-UNIQUE) (Cost=3 Card=1)
FILTER
TABLE ACCESS (BY INDEX ROWID) OF PS_JOB (Cost=2 Card=1 Bytes=24)
INDEX (RANGE SCAN) OF PSAJOB (NON-UNIQUE) (Cost=3 Card=1)
Rich (BOB member since 2002-10-04)
Please find out when the statistics were last updated. You can get a rough idea looking at all_indexes view. If the statistics are not updated the Cost optmizer will have to scan the table to generate the associated costâŚ
As dave pointed out, scanning itself is not necessarily evil and is useful with smallish tables as accessing indexes has its own costâŚ
avaksi 
(BOB member since 2002-08-22)
Create a dimension object with select
/*+ HINT */ ââ
This wonât parse in Designer but should work on the database side. When in query panel, always add the hint objects first so it will be the first line in your select clause.
Hope this helps.
-Pasi
Pasi Tervo 
(BOB member since 2002-09-03)
Actually there is another thing (I havenât tested this). In your universe if you go to your connection properties there is a hint parameter (custom tab). By applying a hint here probably means all your queries based on this universe use this parameter.
This might be useful if for instance your Oracle instance has a rule based optimizer mode and some of the tables have statistics analyzed - in this case you might want to have a hint CHOOSE to force Oracle to use cost based optimizer for these tables.
Cheers, Pasi
Pasi Tervo 
(BOB member since 2002-09-03)
Thanks for that, I got the hint working ealier, funny thing is though it
did Parse successfully in designer.
Fortantly for me in 99% reports the first object selected is EMPID and this is where I applied the Hint. The problem I have though now the smaller reports now run slower due to the hint (bit of pain).
The options I can think of at the moment is to have two EMPID objects and work out what the best situation is to use either one is.
I was going to try and apply the index at the join level, what is the syntax
for this, my current join is
PRD.PS_JOB.EMPLID=PRD.PS_ING_IFACE_TBL.EMPLID
I want to force it to use the index PSOJOB on PRD.PS_JOB table how would I code this in the join.
Rich (BOB member since 2002-10-04)
Are you working in a Data warehouse environment or OLTP system?
In a DW you should hardly ever need to use hints, because the DW should be configured properly.
Andreas 
(BOB member since 2002-06-20)
Nop not a datawarehouse, just a standard psoft database.
I agree with you that I shouldnt need to use the hints, I have told the dbas here that Im having to use a hint to force a use of index, more worry though is the fact that the index Im forcing is the Daddy index
on applications Daddy table, so it indicates a problem.
Im using Oracle 8.1.7 Cost based.
Time to wip dba butt 
Rich (BOB member since 2002-10-04)
I found this topic and created a hint object as described earlier in the thread using this syntax: /*+ HINT */ ââ
It worked great during initial tests! Queries where many times faster. I called it the Magic Object ('cuz users couldnât see what it did) and the users testing the change loved it.
But when it was moved to the production server (most of our users use zabo) it DID NOT WORK. I looked bad. Odd thing is it worked fine on other WEBI servers.
Turns out this syntax works in NT but not in our AIX environment. Adding a space between the two single quotes fixed it so the hint works in both NT and Unix: /*+ HINT */ â â
Tom Thompson (BOB member since 2003-06-04)