Sybase IQ lets you build "joined indexsets". This is amazing but pricey
and really intended more for Data Warehousing than OLTP, although they did 
release a version which permitted writes on-the-fly. (This was implemented 
using a multi-concurrency solution much like PostreSQL uses.)

It essentially pre-joined the data.

Marc A. Leith
redboxdata inc.
E-mail:[EMAIL PROTECTED]

Quoting Jeremiah Jahn <[EMAIL PROTECTED]>:

> I was wondering if there is something I can do that would act similar to
> a index over more than one table. 
> 
> I have about 3 million people in my DB at the moment, they all have
> roles, and many of them have more than one name. 
> 
> for example, a Judge will only have one name, but a Litigant could have
> multiple aliases. Things go far to slow when I do a query on a judge
> named smith. Does any one know a possible way to speed this up? 
> 
> I would think that In a perfect world there would be a way to create an
> index on commonly used joins, or something of that nature. I've tried
> partial indexes, but the optimizer feels that it would be quicker to do
> an index scan for smith% then join using the pkey of the person to get
> their role. For litigants, this makes since, for non-litigants, this
> doesn't. 
> 
> thanx for any insight,
> -jj-
> 
> the basic schema
> 
> actor
>       actor_id PK
>       role_class_code
> 
> identity
>       actor_id FK
>       identity_id PK
>       full_name
> 
> event
>       event_date_time
>       event_id PK
> 
> event_actor
>       event_id FK
>       actor_id FK
> 
> 
> explain select distinct
> actor.actor_id,court.id,court.name,role_class_code,full_name from
> actor,identity,court,event,event_actor where role_class_code = 'Judge' and
> full_name like 'SMITH%' and identity.actor_id = actor.actor_id and
> identity.court_ori = actor.court_ori and actor.court_ori = court.id and
> actor.actor_id = event_actor.actor_id and event_actor.event_id =
> event.event_id and event_date_time > '20021126' order by full_name;
>                                                                            
> QUERY PLAN
> ------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
>  Unique  (cost=726.57..726.58 rows=1 width=92)
>    ->  Sort  (cost=726.57..726.57 rows=1 width=92)
>          Sort Key: identity.full_name, actor.actor_id, court.id, court.name,
> actor.role_class_code
>          ->  Nested Loop  (cost=3.02..726.56 rows=1 width=92)
>                ->  Nested Loop  (cost=3.02..720.72 rows=1 width=144)
>                      ->  Nested Loop  (cost=3.02..9.62 rows=1 width=117)
>                            Join Filter: (("outer".court_ori)::text =
> ("inner".court_ori)::text)
>                            ->  Hash Join  (cost=3.02..4.18 rows=1 width=93)
>                                  Hash Cond: (("outer".id)::text =
> ("inner".court_ori)::text)
>                                  ->  Seq Scan on court  (cost=0.00..1.10
> rows=10 width=34)
>                                  ->  Hash  (cost=3.01..3.01 rows=1 width=59)
>                                        ->  Index Scan using name_speed on
> identity  (cost=0.00..3.01 rows=1 width=59)
>                                              Index Cond: (((full_name)::text
> >= 'SMITH'::character varying) AND ((full_name)::text < 'SMITI'::character
> varying))
>                                              Filter: ((full_name)::text ~~
> 'SMITH%'::text)
>                            ->  Index Scan using actor_speed on actor 
> (cost=0.00..5.43 rows=1 width=50)
>                                  Index Cond: (("outer".actor_id)::text =
> (actor.actor_id)::text)
>                                  Filter: ((role_class_code)::text =
> 'Judge'::text)
>                      ->  Index Scan using event_actor_speed on event_actor 
> (cost=0.00..695.15 rows=1275 width=73)
>                            Index Cond: ((event_actor.actor_id)::text =
> ("outer".actor_id)::text)
>                ->  Index Scan using event_pkey on event  (cost=0.00..5.83
> rows=1 width=52)
>                      Index Cond: (("outer".event_id)::text =
> (event.event_id)::text)
>                      Filter: (event_date_time > '20021126'::bpchar)
> 
> 
> -- 
> "You can't make a program without broken egos."
> -- 
> Jeremiah Jahn <[EMAIL PROTECTED]>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to [EMAIL PROTECTED] so that your
>       message can get through to the mailing list cleanly
> 


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to