-
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
-
Use parallel query.
Index something else.
Use hash partitioning on multiple 0+1 devices.
Use bitmap indexes.
Redesign your table.
Jeff Hunter
-
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
-
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
-
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
-
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.
Jeff Hunter
-
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
-
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).
Jeff Hunter
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|