| best performance quries [message #383974] |
Sun, 01 February 2009 04:56  |
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
|
Junior Member |
|
|
hi to all
i have table with 25M AND 80 column on oracle g11
i have query on first name varchar(128) and i create bitmap index its take 0.1 - 0.9 sec , the query is seek
but i need to make it more faster .
some one can tell me how to handle with that or suggest me ways ?
ofer
|
|
|
|
|
|
| Re: best performance quries [message #383977 is a reply to message #383976] |
Sun, 01 February 2009 05:54  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I would have thought a column like FIRST_NAME would have a lot of distinct values. Why did you choose a BITMAP index? Why not a normal b-tree index - it would be faster.
Ross Leishman
|
|
|
|
| Re: best performance quries [message #383978 is a reply to message #383977] |
Sun, 01 February 2009 06:02 |
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
|
Junior Member |
|
|
hi
i build 3 indexes on difference columns like this
CREATE INDEX IX_UIN ON ICQUSER_TB(UIN) -- NUMBER COLUMN
CREATE INDEX BITMAP IX_FIRSTNAME ON ICQUSER_TB(FIRSTNAME) -- VARCHAR (128) COLUMN
CREATE INDEX IX_LASTNAME ON ICQUSER_TB(LASTNAME) -- VARCHAR (128) COLUMN
and i received the best performance on bitmap index ?
there is anther way to build index ?
ofer
|
|
|
|
|
|
|
|
|
|
| Re: best performance quries [message #383990 is a reply to message #383986] |
Sun, 01 February 2009 07:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Because I want to help you.
I can only help you if you give me some information - so far I have very little.
But you'd rather chat than help yourself, so you can chat to the others here for a while. The tennis is late into the 5th set on TV, so I'll be off to bed soon.
|
|
|
|
|
|
|
|
|
|
| Re: best performance quries [message #384009 is a reply to message #383993] |
Sun, 01 February 2009 14:33 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Unless the query you are trying to tune is a SELECT DISTINCT FIRSTNAME, I would drop the BITMAP index.
Since you claim that the bitmap index gives you the best performance, I guess that your query is not just:
SELECT *
FROM tab
WHERE FIRSTNAME = 'ROSS'
Either you are querying with a LIKE predicate, or you are querying with other columns.
Why not show us the query so we can help you tune it.
Ross Leishman
|
|
|
|
| Re: best performance quries [message #384086 is a reply to message #383974] |
Mon, 02 February 2009 02:51 |
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
|
Junior Member |
|
|
My query is simply like this
SELECT Uin
FROM tab
WHERE FIRSTNAME = 'ROSS'
I'm using bitmap index because the other index are very slowly
In the future i would like to use LIKE '%%'
my query need to return the user Ids that answer to the condition,
in my table i have 25M rows and 80 columns (properties on the user id)
finally in the table should have 400M rows.
so i need to think what is the best way to handle with that problem.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
