Home » RDBMS Server » Performance Tuning » with out enforcing index i want use how to do this
with out enforcing index i want use how to do this [message #212022] Wed, 03 January 2007 09:21 Go to next message
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 #212124 is a reply to message #212022] Wed, 03 January 2007 19:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why can't you use a hint? Since it is enclosed in comment marks, it will be ignored by other DBs.

Ross Leishman
Re: with out enforcing index i want use how to do this [message #212135 is a reply to message #212124] Wed, 03 January 2007 23:17 Go to previous messageGo to next message
saiyshnav
Messages: 15
Registered: August 2006
Location: Bangalore
Junior Member
Hi

I case of MS SQLSERVER

it is doing like this

select val1,val2 from Table with(index(index name))


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 Go to previous messageGo to next message
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 #212963 is a reply to message #212155] Mon, 08 January 2007 23:19 Go to previous messageGo to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

Hi


Are you analyzing table or schemas



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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?

Re: with out enforcing index i want use how to do this [message #213056 is a reply to message #213023] Tue, 09 January 2007 04:40 Go to previous messageGo to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

Hi michael


order table have index on buid col that is '"ORDER_UID_IDX" BUID'

and selectin criteria is A.UID in ('ONE','TWO')

" as he mentied in query"


[Updated on: Tue, 09 January 2007 04:42]

Report message to a moderator

Re: with out enforcing index i want use how to do this [message #213082 is a reply to message #213056] Tue, 09 January 2007 07:22 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
The index is on BUID column but the condition uses A.UID ( without B).

Michael
Previous Topic: Help Tune My query
Next Topic: Query running slow in Forms
Goto Forum:
  


Current Time: Tue Dec 03 04:57:57 CST 2024