OraFAQ Forum: Performance Tuning » how to increase the performance

Home » RDBMS Server » Performance Tuning » how to increase the performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to increase the performance [message #283816]

Wed, 28 November 2007 04:00

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

Report message to a moderator

Re: how to increase the performance [message #283822 is a reply to message #283816]

Wed, 28 November 2007 04:17

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to increase the performance [message #283836 is a reply to message #283822]

Wed, 28 November 2007 04:46

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

Report message to a moderator

Re: how to increase the performance [message #283865 is a reply to message #283836]

Wed, 28 November 2007 05:41

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

so that indexing will work fine or not??

Only appropriate indexes are fine.

Regards
Michel

Report message to a moderator

Re: how to increase the performance [message #284121 is a reply to message #283865]

Wed, 28 November 2007 23:48

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

Report message to a moderator

Re: how to increase the performance [message #284123 is a reply to message #283816]

Wed, 28 November 2007 23:53

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: how to increase the performance [message #284128 is a reply to message #284123]

Thu, 29 November 2007 00:04

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

Report message to a moderator

Re: how to increase the performance [message #284132 is a reply to message #284128]

Thu, 29 November 2007 00:08

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.
Yes.
Is it useful? Maybe yes, maybe no.

Regards
Michel

Report message to a moderator

Re: how to increase the performance [message #284136 is a reply to message #284132]

Thu, 29 November 2007 00:14

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.

Report message to a moderator

Re: how to increase the performance [message #284165 is a reply to message #284136]

Thu, 29 November 2007 01:30

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.

Report message to a moderator

Re: how to increase the performance [message #284178 is a reply to message #284165]

Thu, 29 November 2007 01:43

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to increase the performance [message #284184 is a reply to message #284178]

Thu, 29 November 2007 02:06

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