I have asked the same question for some days on oracle forums, but no answer came:( link is:http://forums.oracle.com/forums/thread.jspa?threadID=2162345&tstart=0
Hi, I build BBS using BDB as backend database, forum database, topic database and post database share one environment. This BBS web application is multi-thread program. If user selects one forum, its topics will be listed in order of last reply time;selecting one topic, posts are listed in order of reply time as well.
struct forum {
UInt16 forumID;
string forumName;
string _lastPoster; // who is the last one replied in this forum
};
struct topic {
UInt32 topicID;
UInt16 forumID; // topic comes from this forum
string title; // topic title
UInt64 dateOfLastReply; // when last reply to this topic happen
};
struct post {
UInt64 postID;
UInt32 topicID; // post comes from this topic
string title; // post title as of topic
UInt64 dateOfPost; // when this post is created
};
I create one primary database and two secondary databases for topic, primary key is topicID, secondary keys are forumID and dateOfLastReply respectively, and I want to show 1st 25 topics in latest reply time order on the 1st browser page, 2nd 25 topics on the 2nd browser page, and etc.
if using SQL, it will be: SELECT topicID FROM topic WHERE forumID=xx ORDER BY dateOfLastReply DESC
From performance perspective, I want get all topics id of one same forum, and need them come in reply time order, then retrieve topic one by one based on returned topicID, how can I do this? guess I have to use joins. Plus, do you have any suggestion about retrieval performance given the fact that topics retrieval will happen each time browser want to request the next page, that is, 2nd 25 topics of this forum? Is DB_DBT_MULTIPLE helpful to me?
thanks.
You need to create a secondary database with a composite key, consisting of (forum_id, dateoflastreply). Then, all the results you want will be in a contiguous range of rows, and you can query on them by doing a range scan (eg, 20 rows starting with (1, 2010-01-01)).