in spite of the odd responses, i'm still working on this if anyone has any suggestions. i simply cannot get it to use the index without full scanning it. ...when i take the order by part out and hint it to use the index it still full scans i broke the sql down: SQL> SELECT address.address_id 2 FROM clorox_prod.address 3 WHERE (address.company_id = 'SYS' 4 AND address.last_name = 'Plaugher'); ADDRESS_ID ---------- 1861557 2238779 2638869 2925218 3011411 4087853 964310 7 rows selected. Elapsed: 00:00:00.01 SQL> SQL> SELECT DISTINCT associated_address.case_id 2 FROM clorox_prod.associated_address, clorox_prod.address 3 WHERE (address.company_id = associated_address.company_id 4 AND address.address_id = associated_address.address_id 5 AND address.company_id = 'SYS' 6 AND address.last_name = 'Plaugher'); no rows selected Elapsed: 00:00:00.00 SQL> select count(*) from( 2 SELECT case_id.*, case_id.ROWID 3 FROM case_id 4 WHERE (case_id.company_id = 'SYS' 5 AND (case_id.address_id IN (1861557,2238779,2638869,2925218,3011411,964310) 6 OR case_id.case_id IN (NULL) 7 )) order by company_id, case_id); COUNT(*) ---------- 6 Elapsed: 00:00:00.02 i removed the postal_code predicat just to get some rows back. the last query uses th On 7/15/05, Walt Weaver <weaver1308@xxxxxxxxx> wrote: > How the hell did I get involved with this? > > It showed up in my Spam folder on Gmail. > > When I was flying a gunship in Vietnam we responded to "911 calls" > with massive firepower -- 20mm gatling guns was the preferred method, > usually worked well for TICs. > > My older daughter is currently in ICU on a ventilator. > > I don't give a shit about HP. > > Send me beer, Bluto. > > Thanks, > --Walt > > On 7/14/05, Mladen Gogala <gogala@xxxxxxxxxxxxx> wrote: > > > > On 07/15/2005 12:23:57 AM, Chris Stephens wrote: > > > This is a 9.2 database on HP 11i. > > > > > > There is some sql from a 3rd party app that is supporting one of our > > > call centers. > > > > Hopefully, you are not responding to 911 calls, are you? > > > > > It is taking approximately 3 minutes to complete. > > > > In geological terms, this is exceptionally quickly. You cannot even finish > > a can > > of Heineken in 3 minutes. What are you complaining about? That response time > > gives your service personnel the necessary time to self-reflect and > > meditate. > > As any person consecrated in fine spirits will tell you, it is a very good > > thing > > indeed. You need to discover your spiritual side. > > > > > They > > > are not using bind variables and we cannot change the sql. They also > > > have no integrity constraints defined. > > > > Symptoms of the truly portable application which "works with any database > > platform", > > probably written in Java. Congratulations! Your application will suck > > equally, regardless > > of what database you use. Be a pal and buy a mainframe from IBM, they still > > make them in the US, > > so it will help the situation with jobs. I believe that IBM mainframes are > > produced in the > > lovely state of Maine, thus the name. > > > > > > > > > > ...again, i can't change the sql. > > > > So tuning it will make whole lot of difference? I love pointless exercises > > in futility! > > > > > > > > i know where the execution time is being spent. i know i need to have > > > an index access to case_id. the query will never return more than > > > three or 4 rows. > > > > > > But you can't change anything or you'll void the warranty? Have you ever > > heard of gentleman > > by the name Joseph Heller? He wrote a book called Catch-22. It's an > > exquisite book, you'll > > enjoy reading it. You shouldn't alter it in any way, shape or form, though. > > > > > > > > even the explain plan shows that i am using the unique index on > > > case_id,address_id,company_Id but when i trace the session it is a > > > full table scan. > > > > Your table is just being thoroughly read, in a way supported by any > > database. Stop whining! > > Rewrite your application using this: > > http://www.gotw.ca/publications/mill03.htm > > > > > > > > > > > > > any suggestions? > > > > Let me bring up a quote from one of the most sacred classic movies, the > > National Lampoon's > > Animal House: > > > > Bluto: My advice to you is to start drinking heavily. > > Otter: Better listen to him, Flounder. He's pre-med. > > > > > > I am grateful to another esteemed member of this list for sending me this > > quote > > earlier today. It is clearly visible that I followed the advice from the > > sages. > > Hic! So should you. You can fine-tune your vendor by using the tools > > produced > > by this fine company: http://smithwesson.com. Go ahead, make my data. > > > > > > > > -- > > Mladen Gogala > > http://www.mgogala.com > > > > > > -- > > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l