Home » RDBMS Server » Performance Tuning » how to increase the performance
how to increase the performance [message #283816] Wed, 28 November 2007 04:00 Go to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Hi,

I am having a table with more than 2 crore records(20,000,000) so, when i am trying to join this table with some other table it is becoming more time consuming job for me for that one i have used partitioning based on index field still it is slow is there any other alternative to increase the performance.

Thanks In Advance
Raj
Re: how to increase the performance [message #283822 is a reply to message #283816] Wed, 28 November 2007 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create the good indexes and good partioning.
Query only what is necessary.
Don't do more work than needed.

Regards
Michel
Re: how to increase the performance [message #283836 is a reply to message #283822] Wed, 28 November 2007 04:46 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Thank you for ur response..
i have created an index on one of the field and i have partitioned the table with range partitioning on the same field all these things i have done after inserting the records , so that indexing will work fine or not??

Thanks in advance
Rajesh
Re: how to increase the performance [message #283865 is a reply to message #283836] Wed, 28 November 2007 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

so that indexing will work fine or not??

Only appropriate indexes are fine.

Regards
Michel
Re: how to increase the performance [message #284121 is a reply to message #283865] Wed, 28 November 2007 23:48 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Thanks for ur response.

first of all i want to know can we have any number of indexes for a single table or if the number of indexes increases does it will decrease the performance and can we partition table by more than one column i mean to say if i have partitioned a table based on some field and now can i partition again based on other field.

Thanks In advance
Rajesh
Re: how to increase the performance [message #284123 is a reply to message #283816] Wed, 28 November 2007 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
> if the number of indexes increases does it will decrease the performance
As number of indexes increases, the DML performance decreases.

>can we partition table by more than one column i mean to say if i have partitioned a table based on some field and now can i partition again based on other field.

What did YOUR benchmark(s) show you?
Re: how to increase the performance [message #284128 is a reply to message #284123] Thu, 29 November 2007 00:04 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Thank you,

the tables what ever i am using my application will perform only select opeartion so can i proceed with creating another index and partitioning based on another field now.

Thanks in Advance
Rajesh
Re: how to increase the performance [message #284132 is a reply to message #284128] Thu, 29 November 2007 00:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
Yes.
Is it useful? Maybe yes, maybe no.

Regards
Michel
Re: how to increase the performance [message #284136 is a reply to message #284132] Thu, 29 November 2007 00:14 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
thank you i will proceed with indexing

this will work like two level indexing and if we create patition the table on the same field for which we have created index then retrieval will be fast i think so.

Thank you,
Rajesh.
Re: how to increase the performance [message #284165 is a reply to message #284136] Thu, 29 November 2007 01:30 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
when i am trying to create another index and partitioning it is throwing some exception

this the query i have used

CREATE INDEX MODEL_YEAR_INDEX_VD ON VEHICLE_DETAILS (MODEL_YEAR)
GLOBAL PARTITION BY RANGE (MODEL_YEAR_INDEX_VD)
(
PARTITION MODEL_YEAR_INDEX_VD_P1 VALUES LESS THAN (2005),
PARTITION MODEL_YEAR_INDEX_VD_P2 VALUES LESS THAN (2006),
PARTITION MODEL_YEAR_INDEX_VD_P3 VALUES LESS THAN (2007),
PARTITION MODEL_YEAR_INDEX_VD_P3 VALUES LESS THAN (MAXVALUE)
);

and the cause it is showing is

cause:user attempted to create a global non-prefixed index which is illegal

can u tell me why it is throwing this exception.

Thanks in advance
Rajesh.
Re: how to increase the performance [message #284178 is a reply to message #284165] Thu, 29 November 2007 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

can u tell me why it is throwing this exception.

No more that what is in the error message as you don't post any information about the table.

Regards
Michel
Re: how to increase the performance [message #284184 is a reply to message #284178] Thu, 29 November 2007 02:06 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member


I have already created an index on some field and partitions also based on the field.

Now i am trying to create another index on some other field and partitioning also based on that field so when i am trying to create like that i am getting this exception.
plz find the attached , snapshot of the exception.

Thanks
Rajesh
  • Attachment: s1.bmp
    (Size: 369.90KB, Downloaded 127 times)
Re: how to increase the performance [message #284193 is a reply to message #284184] Thu, 29 November 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer.

Regards
Michel
Re: how to increase the performance [message #284221 is a reply to message #284193] Thu, 29 November 2007 03:28 Go to previous message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Hi,

I have solved that one

Thanks for ur response,
Rajesh
Previous Topic: Issue updating a partitioned table
Next Topic: parameter setting error
Goto Forum:
  


Current Time: Tue Dec 06 10:44:41 CST 2016

Total time taken to generate the page: 0.12453 seconds