INDEX and PERFORMANCE

Printable View

07-10-2002, 01:38 PM
sanjay kan

I have large tables having more than 600000 to 1000000 rows.
We have a select query....
In where clause we are using columns which have very few
distinct values .
I mean to say from 600000 rows ,only 15 or 20 rows have distinct values.
Performance is a issue.
What is the best way to improve performance.

oracle version is 8i.
Any help will be appreciated.

Thanks.

Sanjay
07-10-2002, 01:41 PM
marist89

Use parallel query.
Index something else.
Use hash partitioning on multiple 0+1 devices.
Use bitmap indexes.
Redesign your table.
07-10-2002, 01:50 PM
sanjay kan

Thanks for support.
I know hash partition but could not understand term "on multiple 0+1 devices".
Can you please explain ?
Justt now ,I tried to create Bitmap index but gives error
error "Table too fragmented to ............

Any idea on this ?

Thanks

Sanjay
07-10-2002, 01:50 PM
sack

to your query - performance

Friend,

Do one thing in your initorcl.ora file in the database directory insert one parameter

optimizer_mode=FIRST_ROWS

This will definately improve the performance greatly.

also

Try increasing the
shared_pool_size, db_buffers parameter value in the same file

This will give you good uplifted queries

Do let me know your comments ok - sure

Sack
07-10-2002, 01:54 PM
sanjay kan

Thanks for support.
In spite of changing INIT.ora ,if i use first_rows as hint
then will not be work ?

We use optimizer_mode =Choose in init.ora

Sanjay
07-10-2002, 02:01 PM
marist89

Quote:

Originally posted by sanjay kan
I know hash partition but could not understand term "on multiple 0+1 devices".

A 0+1 device is a RAID device where multiple physical disks are grouped together as one logical volume.
07-10-2002, 02:05 PM
sack

U could user bitmap indexes then. This is the situation they are ment for.

I have never used it but you could try it .

Do you know anything bout recovery i have just posted a question may you could help with it

sack
07-10-2002, 02:12 PM
marist89

Quote:

Originally posted by sack

Do you know anything bout recovery i have just posted a question may you could help with it

sack

Whoa, whoa, whoa. Considering you're a newbie I'll cut you some slack this time. If you have something to contribute to the thread, do it. Don't post something to try to get somebody to look at your question. There are many qualified people on this forum that can answer your questions. Taking the current thread off topic is not an acceptable way to get your question answered. (Neither is continuously posting "Can anybody help me" as a reply to your own post to keep "bumping" it up).
07-10-2002, 02:56 PM
pando

are your queries predicate use only that column or combined with other columns?
you can probably try composite indexing if several columns are always used , bitmap index are good for low cardinality columns but you have to do some reaserach before using it because it's performance is poor in OLTP database (when DML is going on)

FIRST_ROWS doesnt mean good performance, it only decrease the response time for the first rows