Re: Partitioning
Posted by: Rick James
Date: May 10, 2016 10:15AM

Three bulky strings versus one INT/BIGINT: It is a toss-up. Since you plan to do some processing, then reach back into the table to get the strings, that may be the best way to do it. Especially if you need the strings from only some of the rows that.

INT SIGNED can hold +/- 2 billion. (A signed, 4-byte, 32-bit number)
INT UNSIGNED can hold 0..4 billion. (Unsigned, 4-byte, 32-bit number)
BIGINT can hold a huge number (8 bytes)

Three tables versus one: Generally 1 table is better. Finding a single row with a good index will take about the same time, whether it is 2B rows or 9B rows. So there needs to be another reason for splitting into 3 tables.

SELECT START_CM, END_CM, SCORE FROM BOOK_TBL WHERE URI='MC001'
would need INDEX(URI). I assume URI is not unique, and the SELECT might return multiple rows?

SELECT TEXT_1, TEXT_2 FROM BOOK_TBL WHERE URI_CM1='ABxxxxx' AND URI_CM2='EFxxxxx' AND URI='MCxxxxx'
This depends on how big the columns are. TEXT is problematical. VARCHAR has limitations. So, one of these is best:
INDEX(uri, uri_cm1, uri_cm2) -- in place of the previous suggestion, or
INDEX(uri) -- as before.

Options: ReplyQuote


Subject
Written By
Posted
April 19, 2016 12:29PM
April 20, 2016 11:34PM
April 21, 2016 07:47AM
April 22, 2016 06:41PM
April 22, 2016 06:59PM
April 23, 2016 05:11PM
April 25, 2016 09:27AM
April 30, 2016 05:08PM
May 04, 2016 11:41PM
May 09, 2016 09:18AM
Re: Partitioning
May 10, 2016 10:15AM
May 11, 2016 08:11AM
May 17, 2016 07:49AM
May 23, 2016 03:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.