Home » SQL & PL/SQL » SQL & PL/SQL » Help with an Index (10.2.0.4)
Help with an Index [message #401818] Wed, 06 May 2009 09:44 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12415
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: concatinating similar rows
Next Topic: package going invalid, without modifying dependency
Goto Forum:
  


Current Time: Wed Dec 07 22:38:11 CST 2016

Total time taken to generate the page: 0.12178 seconds