Home » SQL & PL/SQL » SQL & PL/SQL » simple SQL.. but takes forever to return the result.
simple SQL.. but takes forever to return the result. [message #187268] Fri, 11 August 2006 09:49 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member


I have this simple SQL running to get a result. But it's running forever.

SELECT
count(DISTINCT T_IX.NUM_IX)
FROM
T_IX
WHERE
(
T_IX.DATE_PMT BETWEEN '01-JAN-2005' AND '31-DEC-2005'
);

1. T_IX is a partition table and defined as NOPARALLEL and has
huge number of records (over 10 million records).
2. DATE_PMT field is indexed.
3. NUM_IX field is defined as NULL ALLOWED and also indexed.
4. This table has over 100 fields in it.

It's been runing hours and hours and yet to get a result of it.
Any idea as to why it takes long time. Any help or tips is greatly appreciated.

Thanks
R.
Re: simple SQL.. but takes forever to return the result. [message #187270 is a reply to message #187268] Fri, 11 August 2006 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> T_IX.DATE_PMT BETWEEN '01-JAN-2005' AND '31-DEC-2005'
What is to the right of BETWEEN are NOT dates; they are strings.
Both should use the TO_DATE function so the index will be used.
Re: simple SQL.. but takes forever to return the result. [message #187276 is a reply to message #187268] Fri, 11 August 2006 10:57 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
1) what is the partition key of the table ?

2)
are the indexes local ?

3) what is the clustering factor for the date_pmt index ?
Perhaps reload the table to get a good clustering factor.

4) If you have datewise partitions , then what is the clustering factor for the index on num_ix ?

5) How many distinct date values are there ? If you have very few distinct values and if the table is relatively static and you dont have datewise partitions , then create a bitmap index on that column .
6)If none of the above is true , then create a composite btree index
on (date_pmt,num_ix)


And off course use correct data_types in your queries. !!
Re: simple SQL.. but takes forever to return the result. [message #187279 is a reply to message #187268] Fri, 11 August 2006 11:15 Go to previous message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thanks so much for the lot of tips.

There is no partition key defined on dt_pmt field. and I think that is one of the major factor as to why it takes long time.

Thanks for the help.


Previous Topic: Read only dropdown?
Next Topic: parse a stirng
Goto Forum:
  


Current Time: Wed Dec 07 16:33:44 CST 2016

Total time taken to generate the page: 0.24713 seconds