15
i am so sorry! the english is not my mother language.
but i need everyone help actually.
it takes 16 seconds to query the this sql
SELECT count(*) FROM Sold_Listing_V as slv WHERE slv.Is_Paid ='1'
so , we ask for help
the sold_listing_v is a View ,
CREATE OR REPLACE VIEW sold_listing_v AS
SELECT item_t.item_ebay_id, userinfo.user_ebay_id, item_t.list_type, item_t.title AS item_title, item_t.is_second_chance, item_t.sub_title, item_t.duration, item_t.start_time, item_t.quantity, item_t.header_pic_url, item_t.end_time, item_t.parent_item_ebay_id, item_t.bin_price, item_t.start_price, item_t.reserve_price, item_t.post_site, wd_t.winner_detail_id, wd_t.is_shipping_notification, wd_t.quantity AS winner_quantity, wd_t.transaction_id, wd_t.winner_ebay_id, wd_t.is_paid, wd_t.left_feedback_score, wd_t.received_feedback_score, wd_t.is_invoice, wd_t.is_contact, item_t.counter_style, wd_t.is_shipped, wd_t.winning_price, bidder_t.bidder_ebay_id, bidder_t.email, bidder_t.shipping_address, bidder_t.feedback_score, userinfo.user_ebay_info_id, userinfo.user_id, wd_t.quantity AS wdquantity, odt.order_id, odt.order_detail_id, note.note_content
FROM winner_detail_t wd_t
LEFT JOIN item_t ON item_t.item_ebay_id::text = wd_t.item_ebay_id::text
LEFT JOIN transaction_note_t note ON note.winner_detail_id = wd_t.winner_detail_id
LEFT JOIN user_ebay_info userinfo ON userinfo.user_ebay_info_id = item_t.user_ebay_info_id
LEFT JOIN bidder_t ON bidder_t.bidder_ebay_id::text = wd_t.winner_ebay_id::text
LEFT JOIN order_detail_t odt ON odt.winner_detail_id = wd_t.winner_detail_id;
the record number of the item_t , winner_detail_t is very large.
so ,we decided to create partition table .
which steps should we do take ?
1.
because these tables have constraint keys ,they reference each other.
2.
how should we to move the data in these tables , move these data from parent tables to
sub tables .
3.
if the step 1, and step 2 was completed scuessfully ,
but the view sold_listing_v , may be not right ,because of the patition tables.
how should we change this sql to create a view
