Sunday, 15 January 2012

sql - taking a very efficient join between two tables in Oracle -


I have a large table with millions of data (too big too).

The tables are as follows: post_id for the post: Partition key: user_id user_id comment on post: commentid, post_id, comment_creation_date, comment_type,

  post post_id, user_id, description, Primary key for creation_date, xyz, abc, etc. Note: Comment = comment_creation_date   

Note: I can not change table schema in any way to create new index comment = commentid, postid Comment = commentid The primary key of the indexed colums

The comment type is of the string < / P>

There is now a list of comments type and type a comment_action_data category I need to find all the posts that have been commented like that.

A simple, very incompetent solution will be done Select from Post P *, Comment C where c.post_id = p.post_id where c.comment_creation_date> ? And c.comment_creation_date & lt; ? And p.posttype IN (some list)

How can I optimize this query? What if the comment was the same thing instead of the last_modited_date, comment_date Note:

  last_modified_date is not indexed and comment_date is   

Once the query is successful, then I should get all comments for a post together. Example if C1, C2, C3 with Post 1

PS: I am not good at designing questions. I know that is not good for performance.

I'm not sure it will save time, but maybe move your comment section to a subquery Will help:

  SELECT * FROM Post by Join (from select * comment WHERE comment_creation_date & gt;? And comment_creation_date & lt ;? and 'stringlist' like '%' || comment_type | | '%') Sea on C. post_id = p.post_id    

No comments:

Post a Comment