Syntax¶

HLL_ESTIMATE(<state>)

Arguments¶

state

An expression that contains state information generatedby a call toHLL_ACCUMULATE orHLL_COMBINE.

Examples¶

This example shows how to use the three related functionsHLL_ACCUMULATE,HLL_ESTIMATE, andHLL_COMBINE.

Create a simple table and data:

-- Create a sequence to use to generate values for the table.CREATEORREPLACESEQUENCEseq92;CREATEORREPLACETABLEsequence_demo(c1INTEGERDEFAULTseq92.nextval,dummySMALLINT);INSERTINTOsequence_demo(dummy)VALUES(0);-- Double the number of rows a few times, until there are 8 rows:INSERTINTOsequence_demo(dummy)SELECTdummyFROMsequence_demo;INSERTINTOsequence_demo(dummy)SELECTdummyFROMsequence_demo;INSERTINTOsequence_demo(dummy)SELECTdummyFROMsequence_demo;

Copy

Create a table that contains the “state” that represents the currentapproximate cardinality information for the table named sequence_demo:

CREATEORREPLACETABLEresultstate1AS(SELECThll_accumulate(c1)ASrs1FROMsequence_demo);

Copy

Now create a second table and add data. (In a more realistic situation,the user could have loaded more data into the first table and divided thedata into non-overlapping sets based on the time that the data was loaded.)

CREATEORREPLACETABLEtest_table2(c1INTEGER);-- Insert data.INSERTINTOtest_table2(c1)SELECTc1+4FROMsequence_demo;

Copy

Get the “state” information for just the new data.

CREATEORREPLACETABLEresultstate2AS(SELECThll_accumulate(c1)ASrs1FROMtest_table2);

Copy

Combine the “state” information for the two batches of rows:

CREATEORREPLACETABLEcombined_resultstate(c1)ASSELECThll_combine(rs1)ASapc1FROM(SELECTrs1FROMresultstate1UNIONALLSELECTrs1FROMresultstate2);

Copy

Get the approximate cardinality of the combined set of rows:

SELECThll_estimate(c1)FROMcombined_resultstate;

Copy

Output:

+------------------+| HLL_ESTIMATE(C1) ||------------------||               12 |+------------------+

Copy