Home » SQL & PL/SQL » SQL & PL/SQL » Best Indexing Practices??..
| Best Indexing Practices??.. [message #19937] |
Fri, 19 April 2002 05:55  |
Kalpa
Messages: 21
Registered: February 2002
|
Junior Member |
|
|
I am not good with indexing :)
i have a query like this below, where
cisid is a FK, carrierkey is FK (so when i am querying using any of those columns the results are faster. but how they can query is using any combinations from CISID,CARRIERKEY,CLAIMTYPEDESCRIPTION,FORMTYPEDESCRIPTION,TRANSMITDESCRIPTION,SERVICESTATE. So when they are not selecting any of these FK's the query is awefully slow..
So what is the best way to index the columns, so that it is fast. Also what are the other factors that will positively effect performance?
I have tried
CISID A,
CARRIERKEY B,
CLAIMTYPEDESCRIPTION C,
FORMTYPEDESCRIPTION D,
TRANSMITDESCRIPTION E,
SERVICESTATE F.
ABC, BCD,CDE,DEF,ACD and so on..
i also tried, AB, BC,CD,EF and so on..
it improved the performance by a little bit but not a whole lot.
Any help is greatly appreciated..
My QUERY:
select to_char(b.deposittime,'DD-MON-YYYY') as dummiedate,
a.cisid as cid,
c.carrierkey as ckey,
d.claimtypedescription as clmtype,
e.formtypedescription as ftype,
f.transmitdescription as tdesc,
g.servicestate as state,
count(*) as clmcnt,
TO_CHAR(sum(b.totalamount/100),'L999G999G999G999D99MI') as clmamt
from tbl_providercisidref a, tbl_claimshistory b, tbl_payercarrierref c, tbl_claimtyperef d, tbl_formtyperef e, tbl_transmittyperef f, tbl_servicestateterritoryref g
where
b.deposittime >= to_timestamp(i_startdate,'DD-MON-YYYY')
and b.deposittime < to_timestamp(i_enddate,'DD-MON-YYYY') + 1
and b.cisid = a.cisid
and b.cisid = i_cisid
and b.carrierkey = c.carrierkey
and b.carrierkey = upper(i_ckey)
and b.claimtype = d.claimtype
and b.claimtype = i_lob
and b.formtype = e.formtype
and b.formtype = i_clmtype
and b.transmittype = f.transmittype
and b.transmittype = i_xmttype
and b.servicestate = g.servicestate
and b.servicestate = i_state
and b.billcode = '0'
group by to_char(b.deposittime,'DD-MON-YYYY'),a.cisid,c.carrierkey,d.claimtypedescription,e.formtypedescription,f.transmitdescription,g.servicestate
order by to_char(b.deposittime,'DD-MON-YYYY'), a.cisid,c.carrierkey,d.claimtypedescription,e.formtypedescription,f.transmitdescription,g.servicestate;
|
|
|
|
| Re: Best Indexing Practices??.. [message #19943 is a reply to message #19937] |
Fri, 19 April 2002 07:30  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Hi,
I got some questions:
Which is your Oracle version?
which is the datatype of i_startdate and i_enddate?
which is the goal of to_timestamp(i_enddate,'DD-MON-YYYY') + 1 ?
from which tables are the i_* columns?
Which is the size (how many rows) have the different tables?
Have you analyzed the tables once you created your indexes?
Mike
|
|
|
|
| Re: Best Indexing Practices??.. [message #19945 is a reply to message #19937] |
Fri, 19 April 2002 07:41 |
Kalpa
Messages: 21
Registered: February 2002
|
Junior Member |
|
|
Oracle Version: 9i;
the query is called from a webpage and all the i_* columns are the variable names which are passed from the webpage depending on the selections they make.
i_startdate is the variable name for the column deposittime. the column deposittime is TIMESTAMP but i_startdate is just a date.
Which is the size (how many rows) have the different tables? (I HOPE I AM UNDERSTANDING THIS CORRECTLY)
i am only trying to index the table tbl_claimshistory because basically after all the joins etc.. the data has to come from this table.
So the only table which i will be indexing is TBL_CLAIMSHISTORY..
Feel free to ask any other questions you have..
Thanks for your help..
|
|
|
|
| Re: Best Indexing Practices??.. [message #19950 is a reply to message #19937] |
Fri, 19 April 2002 15:41 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
hi again,
I guess that the values of ABCDE are not offten updated. If this is the case try it with an index over
ABCDE. You may even try it with an index over ABCDEF+billcode. Be carefull a big index may slow down inserts and updates, the best way to see if it is the case is to test it. Once you have created your index I recommand you to analyze it (ANALYZE INDEX index_name COMPUTE STATISTICS) also do it with your table if not yet done.
If you are still not statisfied you should have a look at the explain plan. You can do in SQLPLUS whit SET AUTORACE ON and then execute your query.
Hope that helps
Mike
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 02:46:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
