Re: [h2] Indexing user defined tables

[Noel Grandin]

On 2015-10-28 03:10 PM, Dietmar Höhmann wrote:

Am Mittwoch, 28. Oktober 2015 13:36:48 UTC+1 schrieb Noel Grandin:

But since H2 indexes are internally just regular tables, it should not be 
hard to create some internal tables that
represent indexes on the remote tables.


So i'll reflect all columns used in indexes to a local table, along with the 
Notes document ID and create the indexes on
the local table, right?


Pretty much.


(A little more documentation on the implementation of user defined tables would 
be very helpful ...)


There is lots of documentation - it is called code :-)
You are welcome to ask questions, and of course, you are welcome to submit 
patches with improved documentation :-)

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Re: What's your experience which huge databases (5M+)?

[Steve McLeod]

5M+ whats? rows? bytes?


On Tuesday, 27 October 2015 10:10:04 UTC+1, Benjamin Asbach wrote:
>
> Hi there,
>
> I'm looking for some experiences which tables with a huge amount of data 
> (5M+). To be a little bit more concrete what I'm interested in:
>
> * How much data do you store (lines per table (amount of columns) , total 
> lines)
> * Which hardware you use (CPU, RAM)
> * How you run H2
> * How do you query your database and how's the response time
>
> Looking forward for feedback
> Benjamin
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Indexing user defined tables

[Dietmar Höhmann]

Am Mittwoch, 28. Oktober 2015 13:36:48 UTC+1 schrieb Noel Grandin:
>
> But since H2 indexes are internally just regular tables, it should not be 
> hard to create some internal tables that 
> represent indexes on the remote tables. 
>

So i'll reflect all columns used in indexes to a local table, along with 
the Notes document ID and create the indexes on the local table, right?

(A little more documentation on the implementation of user defined tables 
would be very helpful ...)
 

> The tricky part will be keeping the local indexes in sync with the notes 
> data. 
>

Indeed Notes/Domino does not provide easy to use events for document (i.e. 
row) changes. But it is quite easy to query a database for all changed 
documents (including deletions). So if periodic index updates are 
sufficient, it should not be that hard to do. Or we could trigger an index 
update just before the index is used.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Indexing user defined tables

[Noel Grandin]

On 2015-10-28 01:50 PM, Dietmar Höhmann wrote:

I'm thinking about an open source tool to make Lotus Notes databases available 
as H2 user defined tables. Unfortunately
we have no indexes in Notes that would be of much use in this context. My 
question: Is it possible to provide the raw
table data as user defined table and let H2 do the indexing?


Not "out of the box", no.

But since H2 indexes are internally just regular tables, it should not be hard to create some internal tables that 
represent indexes on the remote tables.

(H2 internally only has tables, we don't expose any other kind of 
data-structure to the code above the storage engine).

The tricky part will be keeping the local indexes in sync with the notes data.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Indexing user defined tables

[Dietmar Höhmann]

I'm thinking about an open source tool to make Lotus Notes databases 
available as H2 user defined tables. Unfortunately we have no indexes in 
Notes that would be of much use in this context. My question: Is it 
possible to provide the raw table data as user defined table and let H2 do 
the indexing?

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Indexing user defined tables

[Dietmar Höhmann]

Looking into the "documentation" ;-), I think it might be an option to 
subclass org.h2.index.PageBtreeIndex and adapt it to use my ud table 
instead of an internal table. Could that work? (Could it work without 
messing with the packages?)

Am Mittwoch, 28. Oktober 2015 14:17:22 UTC+1 schrieb Noel Grandin:
>
>
>
> On 2015-10-28 03:10 PM, Dietmar Höhmann wrote: 
> > Am Mittwoch, 28. Oktober 2015 13:36:48 UTC+1 schrieb Noel Grandin: 
> > 
> > But since H2 indexes are internally just regular tables, it should 
> not be hard to create some internal tables that 
> > represent indexes on the remote tables. 
> > 
> > 
> > So i'll reflect all columns used in indexes to a local table, along with 
> the Notes document ID and create the indexes on 
> > the local table, right? 
> > 
> Pretty much. 
>
> > (A little more documentation on the implementation of user defined 
> tables would be very helpful ...) 
>
> There is lots of documentation - it is called code :-) 
> You are welcome to ask questions, and of course, you are welcome to submit 
> patches with improved documentation :-) 
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] HAVING without GROUP BY

[Rami Ojares]

Not grouped by the title column. The title columns are aggregated via 
a concatenation operation: string_agg()


Does that mean that the select clause is interpreted before the having 
clause?



To be more specific what does GROUP BY() mean?


() is the empty GROUPING SET. The subtle difference between GROUP BY 
() and no GROUP BY / HAVING at all is the fact that with GROUP BY (), 
you can get zero rows. Without GROUP BY / HAVING, you will get at 
least one row.


The SQL Server documentation explains this very nicely with examples:
https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx 



I read the page but to me it seemed a little difficult to grasp.
Let's go over an example.

TABLE: T1
*---*--*
| TITLE | YEAR |
*===*==*
| abcdd | 1973 |
*---*--*
| abxyz | 1973 |
*---*--*
| ddefg | 1976 |
*---*--*

SELECT STRING_AGG(TITLE, ', ')
FROM T1
WHERE TITLE LIKE 'ab%'
HAVING COUNT(*) > 1

According to you this returns: "abcdd, abxyz"
because first 2 rows are filtered by the where clause from T1.
Then that result is grouped using the aggregate operators in select clause.
And finally having clause filters out those aggregated rows that do not 
satify

having clauses condition applied to groupings before aggregation.

Is this interpretation correct?

Questions that arises immediately is that what if GROUP BY clause does 
exist.
Do we aggregate first using aggregate operators in select or group bu 
clause?

When is the having in that case applied?

Is the evaluation order (when order by exists)
1) select, group by, having
2) group by, select, having
3) group by, having, select
?

- Rami

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] HAVING without GROUP BY

[Rami Ojares]

Ok, now I think I got it.
GROUP BY () groups all the rows into one group (although logically they 
were one group already)
but this is needed if one wants to use aggregate operators in the 
restriction of rows.
And now that all the rows are in a group we can use the having clause to 
filter that group.

Thank you for clarifications, Lukas.

Cube, rollup and grouping sets do not seem to me very useful shorthands.
Do you have examples of why they would be useful?

- Rami

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] HAVING without GROUP BY

[Lukas Eder]

2015-10-28 11:26 GMT+01:00 Rami Ojares :

> Ok, now I think I got it.
> GROUP BY () groups all the rows into one group (although logically they
> were one group already)
> but this is needed if one wants to use aggregate operators in the
> restriction of rows.
> And now that all the rows are in a group we can use the having clause to
> filter that group.
>

Yes


> Thank you for clarifications, Lukas.
>
> Cube, rollup and grouping sets do not seem to me very useful shorthands.
> Do you have examples of why they would be useful?


Why not? How else would you aggregate revenue over several dimensions?

- Total revenue, revenue per business unit, revenue per sales employee:
ROLLUP
- Total revenue, revenue per business unit, revenue per country, revenue
per country and business unit: CUBE

The same can be achieved with lots of UNION ALL repetition, as explained in
that SQL Server page.

Cheers
Lukas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] HAVING without GROUP BY

[Rami Ojares]

Why not? How else would you aggregate revenue over several dimensions?

- Total revenue, revenue per business unit, revenue per sales 
employee: ROLLUP
- Total revenue, revenue per business unit, revenue per country, 
revenue per country and business unit: CUBE


The same can be achieved with lots of UNION ALL repetition, as 
explained in that SQL Server page.


Of course I have nothing against such shorthands but since these do so 
many things "under the cover"
their exact functionality is hard to remember for the user and might 
produce results that are not really needed in many cases.


I would prefer the more explicit version Eg.

SELECT 'Total' AS TITLE, SUM(revenue) AS THE_SUM FROM T1
UNION
SELECT 'Per Business Unit / ' || BUSINESS_UNIT_NAME AS TITLE, 
SUM(revenue) AS THE_SUM FROM T1 GROUP BY BUSINESS_UNIT_ID

UNION
...

But this is clearly a matter of preference.

- Rami

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.