| with out enforcing index i want use how to do this [message #212022] |
Wed, 03 January 2007 09:21  |
saiyshnav
Messages: 15
Registered: August 2006
Location: Bangalore
|
Junior Member |
|
|
Hi
this is the query which is coming to DB
SELECT UID,BUDESC,ORGDESC,ORGNAME,LOGIN,UUID,A.SDATE,STAT,TCOM,POINTS,SSTIME,SETIME,BRK FROM ORDER A,DETIAL D,BUIDU B,SCHEDU S WHERE A.UID in ('ONE','TWO') AND A.U1UID IS NOT NULL AND A.WUID=D.WUID AND A.UID=B.UID AND A.U1UID=S.SDATE AND A.SDATE>='2005-12-01'AND A.SDATE<='2005-12-31' ORDER BY UID,LOGIN,A.SDATE,STAT
this are index's which are on ORDER TABLE
PK_OUID OUID
ORDER_UID_IDX BUID
ORDER_U1UID_IDX U1UID
ORDER_STATU_IDX STATUS
ORDER_LCHANGED_IDX LCHANGED
ORDER_SDATE_IDX sDATE
ORDER_OID_IDX OID
if i run the query it is using only "ORDER_SDATE_IDX" i want use "ORDER_UID_IDX" for this i'm enforcing the query use this index in fact enforcing is not ANSI standard(because my application suppose to work other DB's also with out enforcing is thire any methode
please help me in this issue
thanks in advance
|
|
|
|
|
|
|
|
| Re: with out enforcing index i want use how to do this [message #212155 is a reply to message #212135] |
Thu, 04 January 2007 00:55  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
And in Oracle it's:
select /*+ index(Table index_name)*/ val1,val2
from Table
I can't make the SS version work on Oracle, but the Oracle version should work on SS - the hint should be treated as a comment and be ignored. SS may not use the right index, but at least it will run.
Ross Leishman
|
|
|
|
|
|
| Re: with out enforcing index i want use how to do this [message #212966 is a reply to message #212963] |
Mon, 08 January 2007 23:31 |
saiyshnav
Messages: 15
Registered: August 2006
Location: Bangalore
|
Junior Member |
|
|
Hi all
I d'd analyzation,but it no change
Here my dout is I can use Hint but,it works for Oracle only,if i got problem with SS then i have use HINT for SS so..
to aviod all this I'm asking ideal query which is best
at least i expecting what are the factory i have fallow while writing queris from frent end
|
|
|
|
| Re: with out enforcing index i want use how to do this [message #213023 is a reply to message #212022] |
Tue, 09 January 2007 03:11 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
Look at the query:
SELECT UID,BUDESC,ORGDESC,ORGNAME,LOGIN,UUID,A.SDATE,STAT,TCOM,POINTS,SSTIME,SETIME,BRK FROM ORDER A,DETIAL D,BUIDU B,SCHEDU S WHERE A.UID in ('ONE','TWO') AND A.U1UID IS NOT NULL AND A.WUID=D.WUID AND A.UID=B.UID AND A.U1UID=S.SDATE AND A.SDATE>='2005-12-01'AND A.SDATE<='2005-12-31' ORDER BY UID,LOGIN,A.SDATE,STAT
You have following selection limiting criteria ( all the others are join conditions ):
1. A.UID in ('ONE','TWO') - there is no index on UID column, so no index may be used at all
2. A.U1UID IS NOT NULL - it is NOT indexable condition.
You have an index on U1UID column named ORDER_U1UID_IDX, but in order to use it you have to change the condition to A.U1UID > ' ' ( or A.U1UID > 0 ).
3. So the only indexable predicate is A.SDATE>='2005-12-01'AND A.SDATE<='2005-12-31' and optimizer using it.
Why do you want to use ORDER_UID_IDX index (on BUID column) when you don't have any selection criteria for it?
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
