Search results for query: *

Thanks Uparna for your advice. I will look into point#3 in detail. Indexes are already there and objects are also analyzed.

Thanks Sem for this link. Ya I'm updating my knowledge on optimizer hints.

Thanks Sem for your inputs. It seems using alias names in indexes indeed forcing the optimizer to use indexes. But this requires testing on daily basis as I'm not sure about the execution path taken by optimizer at different points of time. As the table size is quite big, the response time is...

Ok... I'll try that too. But I've observed one thing. My query is running fine in the test database which have almost same number of records in these tables and exact replica of the production (created using the cold backup of production) That means Oracle is accepting those hints in test...

Tried... no improvement still.....

Thanks for your advice. I'll look into it and see what best can be done. Thanks everybody for your valuable inputs. Sandeep.

Ok.... just now tried this... sorry its not working... its worse than what it was earlier. Now the optimizer is using index for date range of only 2 days and goes for full table scan otherwise. I need some solution as I've to write this query for MIS reports and nobody is going to sit and wait...

Thanks... For some reasons I can't make partitions now. I've tried with analyzing tables and updating statistics but that didn't work. Any other idea??

I tried that too but it didn't work....

But both the tables have records in millions and a date range of 10 days only brings up 20 or 30 hundred records. I think CBO go for full table scan if it find records more than 5%. Correct?? Sandeep.

I've specified optimizer hint in the following query to use the indexes. SELECT /*+ first_rows ordered use_nl(B C) index(CUSTORDER_IDX03) index(PK_ORDERINFO)*/ UPPER(C.CITY) PUCITY, SUM(DECODE(B.USERID, NULL, 0, 1)) WEBCOUNT, COUNT(*) TOTALCOUNT FROM CUSTORDER B...

Thanks Karluk. I got it. Sandeep.

Thanks... but if I don't create a separate index on the foreign key, does it not lock the parent table for every update in this child table?? I'm not sure how Oracle handles updation of child table for some key value in parent table. Sandeep.

I have a table with columns A and B as Primary Key and about 2 million records. There is one child table with columns A,B and C as primary key and A,B as a foreign key to above table. This child table got 4 million records. Do I need to create a index on columns A and B in child table for...

Carp, I think moving tables to different tablespace is a nice idea. (Ya, I'm using 8i) I'll try that. Thanks Sandeep.

Thanks ThomVF for your suggestions. Carp, I will go ahead with your idea. Tell me whether dropping all objects (after export ofcourse) affect the index tablespace?? Will importing back everything also creates all indexes which I created explicitly on non-unique columns. Thanks Sandeep.

I got a tablespace of size 1.5 GB and I'm only using 16 MB. My goal is to reduce the size of this tablespace to 1 GB. First I gave the following command: alter tablespace <tablespace name> coalesce; Then I issued the following command: alter database datafile <filename with path> resize...