Help with an Index [message #401818] |
Wed, 06 May 2009 09:44  |
cajohn
Messages: 10 Registered: July 2006
|
Junior Member |
|
|
I needed to create an index for an application that we use and I am not sure what type to create.
Here is my problem:
Users run reports on Countries and they are slow.
There are 2 tables involved.
1st - Event table - This is where the data exist (This table gets about 20 Million new rows a day).
2nd - Geo table - This table seldom changes and contain about 15,000 rows and one column is Country. There are roughly 200 distinct counties in this table.
The Event table joins to the Geo table by the GeoCode column.
I am thinking about a bitmap index, but worried that the massive amount of updates on the Event table will cause issue.
Any ideas?
Thanks.
|
|
|
Re: Help with an Index [message #401825 is a reply to message #401818] |
Wed, 06 May 2009 10:12   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
How about giving us describes of both tables along with the query you're trying to speed up.
bitmap indexes on a table that is updated a lot is a non starter.
|
|
|
Re: Help with an Index [message #401838 is a reply to message #401818] |
Wed, 06 May 2009 11:09   |
cajohn
Messages: 10 Registered: July 2006
|
Junior Member |
|
|
Here is a little more detail:
desc event
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(20)
TIME NOT NULL TIMESTAMP(3)
COUNT NUMBER(7)
DADDRESS NUMBER(22)
D_GEO_ID NUMBER(16)
NAME VARCHAR2(512)
E_TYPE NUMBER(2)
GENERATOR NUMBER(20)
PRIORITY NUMBER(5)
SADDRESS NUMBER(22)
S_GEO_ID NUMBER(16)
MESSAGE VARCHAR2(1023)
ROTOCOL VARCHAR2(31)
(80 more columns)
desc GEO
Name Null? Type
----------------------------------------- -------- ----------------------------
GEO_ID NOT NULL NUMBER(16)
GEO_LATITUDE NUMBER(13)
GEO_LONGITUDE NUMBER(13)
GEO_POSTAL_CODE VARCHAR2(1023)
GEO_COUNTRY_CODE VARCHAR2(1023)
GEO_REGION_CODE VARCHAR2(1023)
GEO_LOCATION_INFO VARCHAR2(1023)
select count(distinct geo_country_code) from GEO;
COUNT(DISTINCTGEO_COUNTRY_CODE)
-------------------------------
239
select count(*) from GEO;
COUNT(*)
----------
93771
Example SQL:
select time, name, message, priority, saddress, daddress
from event, geo
where event.d_geo_id = geo.geo_id
and geo.geo_country_code = 'US';
Thanks.
|
|
|
Re: Help with an Index [message #401847 is a reply to message #401838] |
Wed, 06 May 2009 11:26   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
For that query, I'd put indexes on EVENT(d.geo_id) and on GEO(geo_country_code,geo_id)
You need to have a look at which queries are causing you problems, and fix those.
|
|
|
Re: Help with an Index [message #401850 is a reply to message #401818] |
Wed, 06 May 2009 11:39   |
cajohn
Messages: 10 Registered: July 2006
|
Junior Member |
|
|
Then how do you join the indexes in the query? Is there a hint I can use?
They only thing I can do to the query is add a hint. The application actually builds the query and looks worse then my example, but I can pass in a hint and it helps with single table index. For example I have an index on saddress and I pass in a hint to use this and the report runs great. County reports suck because of the data being split between two tables.
[Updated on: Wed, 06 May 2009 11:43] Report message to a moderator
|
|
|
Re: Help with an Index [message #401853 is a reply to message #401850] |
Wed, 06 May 2009 11:53   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You don't join indexes.
That's what the fearsomely complex piece of software called the Cost Based Optimiser does.
all you have to do is to ensure that the table statistics are up to date (use DBMS_STATS), and it will do the rest for you.
Hints are for when you've got very complex queries, or very odd data distributions
|
|
|
Re: Help with an Index [message #401859 is a reply to message #401818] |
Wed, 06 May 2009 12:54   |
cajohn
Messages: 10 Registered: July 2006
|
Junior Member |
|
|
Yes, CBO usually does a good job, but like I said the queries from this application are ugly. It usually starts with a where clause of date between a and b. There just happens to be an index based on this date field, so this index gets picked all of the time. The date field is a timestamp and goes down to milli-seconds. This index also contains the ID column, which just happens to be a unique 20 character field. Searching the whole table is just as effective as using this index, but I can not get rid of the index or we will lose support.
So, that is why I need to build a index or indexes and be able to pass in a hint to use them.
[Updated on: Wed, 06 May 2009 12:57] Report message to a moderator
|
|
|
Re: Help with an Index [message #401869 is a reply to message #401818] |
Wed, 06 May 2009 13:18   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Part of your problem is the poorly written SQL.
select time, name, message, priority, saddress, daddress
from event, geo
where event.d_geo_id = geo.geo_id
and geo.geo_country_code = 'US';
It might behave better as
select time, name, message, priority, saddress, daddress
from event
where event.d_geo_id in SELECT geo.geo_id from geo where geo.geo_country_code = 'US';
Post EXPLAIN PLAN for both SQL above.
|
|
|
Re: Help with an Index [message #401881 is a reply to message #401869] |
Wed, 06 May 2009 16:46  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If you are reading tens or hundreds of thousands of rows in each query, then an index is going to struggle.
The best solution would be to partition the big table such that all rows you wanted for any one query would be in a single partition.
Assuming the GEO_ID values are not "organised", hash and range partitioning would be no good - LIST would be the only choice but you would have to list lots of GEO_IDs.
Oracle 11G would help with its new partitioning options that allow you to partition by a column in a parent table (in this case, COUNTRY).
Ross Leishman
|
|
|