Home » RDBMS Server » Performance Tuning » best performance quries (g11 windows 2003 64bit)
best performance quries [message #383974] Sun, 01 February 2009 04:56 Go to next message
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 #383976 is a reply to message #383974] Sun, 01 February 2009 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Buy faster disks is one way.

Regards
Michel
Re: best performance quries [message #383977 is a reply to message #383976] Sun, 01 February 2009 05:54 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #383981 is a reply to message #383978] Sun, 01 February 2009 06:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run this:
SELECT COUNT(DISTINCT FIRSTNAME) 
FROM ICQUSER_TB


Ross Leishman
Re: best performance quries [message #383986 is a reply to message #383981] Sun, 01 February 2009 06:53 Go to previous messageGo to next message
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
Junior Member
what for?

Re: best performance quries [message #383989 is a reply to message #383974] Sun, 01 February 2009 07:06 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1.
Quote:
what for?


To know the selectivity of that index.

2. Do you really need VARCHAR2(128) for firstname column?

Michael
Re: best performance quries [message #383990 is a reply to message #383986] Sun, 01 February 2009 07:06 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #383993 is a reply to message #383989] Sun, 01 February 2009 07:23 Go to previous messageGo to next message
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
Junior Member
hi
its 80% unique names

sorry but i really need first_name varchar(128)
Smile



Re: best performance quries [message #383994 is a reply to message #383990] Sun, 01 February 2009 07:25 Go to previous messageGo to next message
oferoh
Messages: 6
Registered: February 2009
Location: ISRAEL
Junior Member

Im very sad R. REDDER LOSE

which information you need more?

ofer


Re: best performance quries [message #384007 is a reply to message #383974] Sun, 01 February 2009 13:50 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Quote:
"I would have thought a column like FIRST_NAME would have a lot of distinct values"


Depends which country you are in. I have noticed that certain countries have exceedingly common first names Smile

[Updated on: Sun, 01 February 2009 13:50]

Report message to a moderator

Re: best performance quries [message #384009 is a reply to message #383993] Sun, 01 February 2009 14:33 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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


Re: best performance quries [message #384116 is a reply to message #384086] Mon, 02 February 2009 03:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There should be no problem with a simple EQUALS query. If it is slower than the bitmap index, then it sounds like you are doing something wrong.

If you want to perform full text searches (LIKE %%), then you need to start reading the Oracle Text manuals.
http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/toc.htm
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/toc.htm

Ross Leishman
Previous Topic: what is "count stopkey"?
Next Topic: High optimizer cost ..Please help me out to tune the query. (merged 4)
Goto Forum:
  


Current Time: Wed Dec 07 20:04:14 CST 2016

Total time taken to generate the page: 0.12633 seconds