Is there a way to find all distinct values for multiple columns in one query?

I would really appreciate a bit of help/pointers on the following problem.

Background Info:

Database version: Oracle 9i

Java version: 1.4.2

The problem

I have a database table with multiple columns representing various meta data about a document.

E.g.:

CREATE TABLE mytable
(
document_id integer,
filename varchar(255),
added_date date,
created_by varchar(32),
....
)

Due to networking/latency issues between a webserver and database server, I would like to minimise the number of queries made to the database.

The documents are listed in a web page, but there are thousands of different documents.

To aid navigation, we provide filters on the web page to select just documents matching a certain value - e.g. created by user 'joe bloggs' or created on '01-01-2011'. Also, paging is provided so triggering a db call to get the next 50 docs or whatever.

The web pages themselves are kept pretty dumb - they just present what's returned by a java servlet. Currently, these filters are each provided with their distinct values through separate queries for distinct values on each column.

This is taking quite a long time due to networking开发者_开发百科 latency and the fact it means 5 extra queries.

My Question

I would like to know if there is a way to get this same information in just one query?

For example, is there a way to get distinct results from that table in a form like:

DistinctValue    Type
01-01-2011       added_date
01-02-2011       added_date
01-03-2011       added_date
Joe Bloggs       created_by
AN Other         created_by
....             ...

I'm guessing one issue with the above is that the datatypes are different across the columns, so dates and varchars could not both be returned in a "DistinctValue" column.

Is there a better/standard approach to this problem?

Many thanks in advance.

Jay

Edit

As I mentioned in a comment below, I thought of a possibly more memory/load effective approach that removes the original requirement to join the queries up -

I imagine another way it could work is instead of populating the drop-downs initially, have them react to a user typing and then have a "suggester" style drop-down appear of just those distinct values that match the entered text. I think this would mean a) keeping the separate queries for distinct values, but b) only running the queries individually as needed, and c) reducing the resultset by filtering the unique values on the user's text.

---

This query will return an output as you describe above:

SELECT DocumentID As DocumentID, 'FileName' As AttributeType, FileName As DistinctValue
 FROM TableName
UNION
SELECT DocumentID, 'Added Date', Added_date FROM TableName
UNION
SELECT DocumentID, 'Created By', created_by FROM TableName
UNION
....

If you have the privilege you could create a view using this SQL and you could use it for your queries.

---

Due to networking/latency issues between a webserver and database server, I would like to minimise the number of queries made to the database.

The documents are listed in a web page, but there are thousands of different documents.

You may want to look into Lucene. Whenever I see "minimise queries to db" combined with "searching documents", this is what I think of. I've used this with very good success, and can be used with read-only or updating environments. Oracle's answer is Oracle Text, but (to me anyway) its a bit of a bear to setup and use. Depends on your company's technical resources and strengths.

Anyway, sure beats the heck out of multiple queries to the db for each connection.