tom-- · December 26, 2013 14:13
diff --git a/cubrid join queries.txt b/cubrid join queries.txt
 09:40 Topic is CUBRID Open source database highly optimized for Web applications || http://www.cubrid.org || CUBRID manual http://www.cubrid.org/manual/841/en || For questions also see: http://www.cubrid.org/questions
 09:40 Set by eusto on July 19, 2012 11:31:26 AM EDT
 09:40 Mode is +cnt
 10:48 tom[]: not really a cubrid q but since the peeps here are so clever: http://thefsb.tumblr.com/post/50437998370
 10:49 tom[]: How to get query results in normal form from a schema in normal form?
 10:52 @rimio: tom[]: the guy likes to emulate a planner :)
 10:53 tom[]: the guy being me :P
 10:53 @rimio: :))
 10:53 tom[]: isn't this an old question in SQL databases?
 10:54 @rimio: might be, dunno
 10:55 eusto__ is now known as eusto
 10:55 @rimio: but I don't think your approach is optimal
 10:55 ChanServ sets mode +o eusto
 10:55 @rimio: IN is not a particularly fast op
 10:55 @eusto: i don't understand what you're trying to do
 10:55 tom[]: i described two approaches
 10:55 @rimio: and you're losing some indexes there
 10:55 @eusto: rimio: shut up, it's fast in CUBRID:P
 10:55 @rimio: =))
 10:56 tom[]: i don't like either approach
 10:56 tom[]: http://thefsb.tumblr.com/post/50437998370 explains what i'm trying to do
 10:56 @rimio: well
 10:56 @rimio: in CUBRID
 10:57 @rimio: you can use class fields
 10:57 tom[]: is it not clear? my app wants to have the result data in normal form
 10:57 @rimio: or OID fields
 10:57 @rimio: whatever they are called
 10:58 @rimio: so you would have a schema for the table quote
 10:58 @rimio: quote (string quote, movie movie, actor actor)
 10:59 @rimio: movie movie = field called "movie" of the type "row pointer into movie table"
 10:59 @rimio: then you can just select quote, movie.title, actor.name from quote;
 11:00 @rimio: lightning fast :)
 11:01 @rimio: of course, you could run into problems when you delete tuples from "movies" and "actors"
 11:01 @rimio: but that can be worked around
 11:01 @eusto: only if you're using the reusable oid flag
 11:01 @eusto: i think
 11:02 @rimio: yes
 11:02 @eusto: i mean, you will have problems only if you're using "REUSABLE OID"
 11:04 @eusto: unfortunately, the first approach is the bets one, i think
 11:05 @eusto: what rimio is saying is not different from the first approach, it is just a CUBRID optimization to "pk->fk" relationships
 11:06 @rimio: eusto: although, filtering the quotes table in a derived table
 11:06 @rimio: will force the planner to use the derived table as outer
 11:06 @eusto: i thought about that
 11:06 @rimio: and do idx-scan on the other two
 11:06 @rimio: but the planner might choose that anyway
 11:06 @eusto: yes but it still produces duplicates
 11:07 @eusto: this is what throws me off
 11:07 @eusto: This can be inefficient due to repetition of movie titles and actor names in the result set.
 11:08 @rimio: so how should the output look like?
 11:08 @rimio: without duplicates
 11:08 @eusto: so, my guess is, you would like to get a list of DISTINCT(movies,actors) + a list of qotes for this pair which contains keyword?
 11:08 @eusto: in which case, you're kinda out of luck
 11:08 @rimio: not excactly
 11:09 @rimio: he could use aggregation
 11:09 @rimio: which should generate a groupby skip
 11:09 @rimio: right?
 11:09 @eusto: might
 11:09 @eusto: I'm thinking analytics?
 11:10 @rimio: bad idea
 11:10 @rimio: no skip there
 11:10 @rimio: (not yet)
 11:10 @eusto: in order to produce all quotes, you will have to produce all movies and actors, there's no way of getting around this, right?
 11:11 @rimio: i don't see any
 11:18 @eusto: tom[]: can you provide us with a sample output
 11:18 @eusto: ?
 11:26 @rimio: or at least which normal form you're trying to emulate
 12:27 Disconnect for Sleep Mode
 12:27  
 12:27 ————————————— End Session —————————————
 12:27  
 12:39  
 12:39 ————————————— Begin Session —————————————
 12:39  
 12:39 Topic is CUBRID Open source database highly optimized for Web applications || http://www.cubrid.org || CUBRID manual http://www.cubrid.org/manual/841/en || For questions also see: http://www.cubrid.org/questions
 12:39 Set by eusto on July 19, 2012 11:31:26 AM EDT
 12:39 Mode is +cnt
 13:12 tom[]: sorry for disappearing
 13:12 tom[]: i've read what you said
 13:12 tom[]: i'll create an example output
 13:14 tom[]: actually, this is easier: http://www.imdb.com/title/tt1853728/trivia?tab=qt&ref_=tt_trv_qu
 13:15 tom[]: but imagine searching for quotes and getting a table including multiple movies and multiple actors
 13:19 tom[]: the same movie and actor may have many quotes so there may be lots of repetition: https://gist.github.com/tom--/5585628
 13:21 @eusto: so, for the example on gist, you woul like to show 'Django unchained' part only once, right?
 13:22 tom[]: what i want, ultimately, is a data structure in my app that represents 3 tables, normalized like the schema itself
 13:22 @eusto: you're kinda out of luck here
 13:22 tom[]: that's what i suspected
 13:22 @eusto: i mean, you're going to have to aggregate items yourself
 13:23 @eusto: you can do an order by movie, actor
 13:23 tom[]: to get it i think i can get the result table in the gist from sql and normalize it in the app itself
 13:23 @eusto: yeah
 13:23 tom[]: or i can make the multiple queries, like the blog article explained
 13:24 @eusto: the thing is, in a result set, you cannot span multiple columns or rows
 13:24 @eusto: multiple queries is ok if you only expect a few results
 13:24 tom[]: the constraint, it seems, is in SQL itself
 13:24 @eusto: it's worse than a single select and some processing on your part but it's acceptable
 13:25 @eusto: but what if somebody searches for quotes like '%e%'
 13:25 tom[]: then it gets complex!
 13:25 @eusto: since it's the most common letter in the english language, you can expect to find it in all quotes, yes
 13:26 tom[]: tables with relations is a good way to store this kind of data. but SQL doesn't provide a way to ask for and receive normalized result sets
 13:26 @eusto: yes, SQL sucks
 13:27 @eusto: actually, the fact that you're including two more columns in a select list should not have a big performance impact
 13:27 @eusto: I mean, select a, b, c from x
 13:27 @eusto: is comparable with select a from x
 13:27 @eusto: most of the time being spent in actualy reading x
 13:27 @eusto: rather than producing the output
 13:28 @eusto: values for b and c are fetched anyway, they're just not placed into the result
 13:28 @eusto: so you don't have to concern yourself with this
 13:28 @eusto: normalizing them should be pretty easy though
 13:28 tom[]: as it happens, there's a unique index on artist(id, name) and on movie(id, title) so the server only needs to use the index
 13:29 tom[]: to get the joined fields
 13:29 @eusto: yes, i suppose so
 13:30 @eusto: depened on the plan the server will pick
	09:40 Topic is CUBRID Open source database highly optimized for Web applications \|\| http://www.cubrid.org \|\| CUBRID manual http://www.cubrid.org/manual/841/en \|\| For questions also see: http://www.cubrid.org/questions
	09:40 Set by eusto on July 19, 2012 11:31:26 AM EDT
	09:40 Mode is +cnt
	10:48 tom[]: not really a cubrid q but since the peeps here are so clever: http://thefsb.tumblr.com/post/50437998370
	10:49 tom[]: How to get query results in normal form from a schema in normal form?
	10:52 @rimio: tom[]: the guy likes to emulate a planner :)
	10:53 tom[]: the guy being me :P
	10:53 @rimio: :))
	10:53 tom[]: isn't this an old question in SQL databases?
	10:54 @rimio: might be, dunno
	10:55 eusto__ is now known as eusto
	10:55 @rimio: but I don't think your approach is optimal
	10:55 ChanServ sets mode +o eusto
	10:55 @rimio: IN is not a particularly fast op
	10:55 @eusto: i don't understand what you're trying to do
	10:55 tom[]: i described two approaches
	10:55 @rimio: and you're losing some indexes there
	10:55 @eusto: rimio: shut up, it's fast in CUBRID:P
	10:55 @rimio: =))
	10:56 tom[]: i don't like either approach
	10:56 tom[]: http://thefsb.tumblr.com/post/50437998370 explains what i'm trying to do
	10:56 @rimio: well
	10:56 @rimio: in CUBRID
	10:57 @rimio: you can use class fields
	10:57 tom[]: is it not clear? my app wants to have the result data in normal form
	10:57 @rimio: or OID fields
	10:57 @rimio: whatever they are called
	10:58 @rimio: so you would have a schema for the table quote
	10:58 @rimio: quote (string quote, movie movie, actor actor)
	10:59 @rimio: movie movie = field called "movie" of the type "row pointer into movie table"
	10:59 @rimio: then you can just select quote, movie.title, actor.name from quote;
	11:00 @rimio: lightning fast :)
	11:01 @rimio: of course, you could run into problems when you delete tuples from "movies" and "actors"
	11:01 @rimio: but that can be worked around
	11:01 @eusto: only if you're using the reusable oid flag
	11:01 @eusto: i think
	11:02 @rimio: yes
	11:02 @eusto: i mean, you will have problems only if you're using "REUSABLE OID"
	11:04 @eusto: unfortunately, the first approach is the bets one, i think
	11:05 @eusto: what rimio is saying is not different from the first approach, it is just a CUBRID optimization to "pk->fk" relationships
	11:06 @rimio: eusto: although, filtering the quotes table in a derived table
	11:06 @rimio: will force the planner to use the derived table as outer
	11:06 @eusto: i thought about that
	11:06 @rimio: and do idx-scan on the other two
	11:06 @rimio: but the planner might choose that anyway
	11:06 @eusto: yes but it still produces duplicates
	11:07 @eusto: this is what throws me off
	11:07 @eusto: This can be inefficient due to repetition of movie titles and actor names in the result set.
	11:08 @rimio: so how should the output look like?
	11:08 @rimio: without duplicates
	11:08 @eusto: so, my guess is, you would like to get a list of DISTINCT(movies,actors) + a list of qotes for this pair which contains keyword?
	11:08 @eusto: in which case, you're kinda out of luck
	11:08 @rimio: not excactly
	11:09 @rimio: he could use aggregation
	11:09 @rimio: which should generate a groupby skip
	11:09 @rimio: right?
	11:09 @eusto: might
	11:09 @eusto: I'm thinking analytics?
	11:10 @rimio: bad idea
	11:10 @rimio: no skip there
	11:10 @rimio: (not yet)
	11:10 @eusto: in order to produce all quotes, you will have to produce all movies and actors, there's no way of getting around this, right?
	11:11 @rimio: i don't see any
	11:18 @eusto: tom[]: can you provide us with a sample output
	11:18 @eusto: ?
	11:26 @rimio: or at least which normal form you're trying to emulate
	12:27 Disconnect for Sleep Mode
	12:27
	12:27 ————————————— End Session —————————————
	12:27
	12:39
	12:39 ————————————— Begin Session —————————————
	12:39
	12:39 Topic is CUBRID Open source database highly optimized for Web applications \|\| http://www.cubrid.org \|\| CUBRID manual http://www.cubrid.org/manual/841/en \|\| For questions also see: http://www.cubrid.org/questions
	12:39 Set by eusto on July 19, 2012 11:31:26 AM EDT
	12:39 Mode is +cnt
	13:12 tom[]: sorry for disappearing
	13:12 tom[]: i've read what you said
	13:12 tom[]: i'll create an example output
	13:14 tom[]: actually, this is easier: http://www.imdb.com/title/tt1853728/trivia?tab=qt&ref_=tt_trv_qu
	13:15 tom[]: but imagine searching for quotes and getting a table including multiple movies and multiple actors
	13:19 tom[]: the same movie and actor may have many quotes so there may be lots of repetition: https://gist.github.com/tom--/5585628
	13:21 @eusto: so, for the example on gist, you woul like to show 'Django unchained' part only once, right?
	13:22 tom[]: what i want, ultimately, is a data structure in my app that represents 3 tables, normalized like the schema itself
	13:22 @eusto: you're kinda out of luck here
	13:22 tom[]: that's what i suspected
	13:22 @eusto: i mean, you're going to have to aggregate items yourself
	13:23 @eusto: you can do an order by movie, actor
	13:23 tom[]: to get it i think i can get the result table in the gist from sql and normalize it in the app itself
	13:23 @eusto: yeah
	13:23 tom[]: or i can make the multiple queries, like the blog article explained
	13:24 @eusto: the thing is, in a result set, you cannot span multiple columns or rows
	13:24 @eusto: multiple queries is ok if you only expect a few results
	13:24 tom[]: the constraint, it seems, is in SQL itself
	13:24 @eusto: it's worse than a single select and some processing on your part but it's acceptable
	13:25 @eusto: but what if somebody searches for quotes like '%e%'
	13:25 tom[]: then it gets complex!
	13:25 @eusto: since it's the most common letter in the english language, you can expect to find it in all quotes, yes
	13:26 tom[]: tables with relations is a good way to store this kind of data. but SQL doesn't provide a way to ask for and receive normalized result sets
	13:26 @eusto: yes, SQL sucks
	13:27 @eusto: actually, the fact that you're including two more columns in a select list should not have a big performance impact
	13:27 @eusto: I mean, select a, b, c from x
	13:27 @eusto: is comparable with select a from x
	13:27 @eusto: most of the time being spent in actualy reading x
	13:27 @eusto: rather than producing the output
	13:28 @eusto: values for b and c are fetched anyway, they're just not placed into the result
	13:28 @eusto: so you don't have to concern yourself with this
	13:28 @eusto: normalizing them should be pretty easy though
	13:28 tom[]: as it happens, there's a unique index on artist(id, name) and on movie(id, title) so the server only needs to use the index
	13:29 tom[]: to get the joined fields
	13:29 @eusto: yes, i suppose so
	13:30 @eusto: depened on the plan the server will pick
No results found