SQL: Multiple table.column single index

Question

Is there a SQL database that allows having a single index on multiple table.column so I can query for multiple table.column at the same time and have the index return the table.column and the primary key of the table?

Or may I always have to use an external index like Lucene?

Clarification: The index I want is across multiple tables

I don't understand your question but yes you can have indexes on multiple columns. — Ash Burlaczenko, Jun 08 '11 at 21:27
I clarified the question. I need the index across multiple tables — Eduardo, Jun 09 '11 at 05:19

score 5 · Accepted Answer · answered Jun 08 '11 at 21:41

5

You can an index to a view which join tables in most RDBMS.

This allows you to have a single index on many columns across many tables

In SQL Server, this is an indexed view. Or materialized view in Oracle.

answered Jun 08 '11 at 21:41

gbn

422,506
82
585
676

1

It seems overkill to need to have the data replicated to keep the index. But it seems it is the only solution to maintain that kind of indexes transparently updated in SQL databases. – Eduardo Jun 13 '11 at 05:56
Note that these indexes are restricted though - see [this answer](http://stackoverflow.com/questions/8506487/cannot-create-index-on-view-view-table-name-because-the-view-is-not-schema-bou) for a pretty thorough explanation. – moodboom Dec 17 '12 at 15:05

score 0 · Answer 2 · answered Jun 08 '11 at 21:29

0

You can't have a single index applied to columns in multiple tables. With Lucene it is possible to use external indexes as you mentioned.

Check the following link for Lucene:

Using Lucene – External Indexes

answered Jun 08 '11 at 21:29

Hasan Fahim

3,875
1
30
51

score 0 · Answer 3 · answered Jun 08 '11 at 21:43

Is there a SQL database that allows having a single index on multiple table.column so I can query for multiple table.column at the same time and have the index return the table.column and the primary key of the table?

Not sure I get your question, but it seems you're wondering about the potential for bitmap index scans.

If so, yes. PostgreSQL allows to do that. Best I'm aware, MySQL won't, nor will SQLite. Not sure about Oracle and SQL-Server (though I'd expect yes for both).

I clarified the question. I need the index across multiple tables — Eduardo, Jun 09 '11 at 05:19

SQL: Multiple table.column single index

3 Answers3