Hi Noel
I tired what you suggested. I report here the results (to be honest now I
had my PC doing some other operations...not too much resources
expansive.....):
1)*SELECT COUNT(HDR_EVENT_NUMBER ) AS Y, HDR_EVENT_NUMBER AS TEXT FROM
PUBLIC.XDASV1 GROUP BY HDR_EVENT_NUMBER*;
Result:
Y TEXT
7285 XDAS_AE_CREATE_PEER_ASSOC
7390 XDAS_AE_MODIFY_ACCOUNT
7285 XDAS_AE_MODIFY_DATA_ITEM_ASSOC_CONTEXT
7390 XDAS_AE_CREATE_ACCOUNT
7181 XDAS_AE_RECEIVE_DATA_VIA_ASSOC
7181 XDAS_AE_TERMINATE_DATA_ITEM_ASSOC
7181 XDAS_AE_DISABLE_ROLE
7286 XDAS_AE_INVOKE_SERVICE
7181 XDAS_AE_REQUEST_ESCALATED
7494 XDAS_AE_NOT_SPECIFIED
7390 XDAS_AE_ENABLE_ACCOUNT
7181 XDAS_AE_AUD_CONFIG
7181 XDAS_AE_SHUTDOWN_SYS
7286 XDAS_AE_QUERY_PROCESS_CONTEXT
7181 XDAS_AE_AUD_DS_CORR
7285 XDAS_AE_TERMINATE_PEER_ASSOC
7181 XDAS_AE_DELETE_ROLE
7181 XDAS_AE_APPROVAL_REQUESTED
7286 XDAS_AE_ENABLE_SERVICE
7286 XDAS_AE_REMOVE_SERVICE
7285 XDAS_AE_MODIFY_PROCESS_CONTEXT
7181 XDAS_AE_RESOURCE_EXHAUST
7286 XDAS_AE_INSTALL_SERVICE
7286 XDAS_AE_MODIFY_SERVICE_CONFIG
7181 XDAS_AE_AUD_DS_FULL
7181 XDAS_AE_BACKUP_DATASTORE
7390 XDAS_AE_DISABLE_ACCOUNT
7286 XDAS_AE_QUERY_SERVICE_CONFIG
7286 XDAS_AE_TERMINATE_SERVICE
7181 XDAS_AE_MODIFY_DATA_ITEM_CONTENTS
7181 XDAS_AE_CREATE_ROLE
7181 XDAS_AE_QUERY_DATA_ITEM_CONTENTS
7181 XDAS_AE_RECOVER_DATASTORE
7286 XDAS_AE_DISABLE_SERVICE
7390 XDAS_AE_QUERY_SESSION
7181 XDAS_AE_APPROVAL_RECEIVED
7181 XDAS_AE_QUERY_ROLE
7390 XDAS_AE_MODIFY_SESSION
7181 XDAS_AE_SEND_DATA_VIA_ASSOC
7390 XDAS_AE_CREATE_SESSION
7388 XDAS_AE_DELETE_DATA_ITEM
7181 XDAS_AE_MODIFY_AUTH_TOKEN
7181 XDAS_AE_NOTIFICATION_SENT
7390 XDAS_AE_DELETE_ACCOUNT
7181 XDAS_AE_QUERY_ASSOC_CONTEXT
7181 XDAS_AE_CREATE_DATA_ITEM_ASSOC
7181 XDAS_AE_START_SYS
7181 XDAS_AE_RESOURCE_CORRUPT
7181 XDAS_AE_ENABLE_ROLE
7286 XDAS_AE_QUERY_DATA_ITEM_ATT
7181 XDAS_AE_MODIFY_ASSOC_CONTEXT
7390 XDAS_AE_QUERY_ACCOUNT
7181 XDAS_AE_QUERY_DATA_ITEM_ASSOC_CONTEXT
7390 XDAS_AE_TERMINATE_SESSION
7390 XDAS_AE_CREATE_DATA_ITEM
7286 XDAS_AE_MODIFY_DATA_ITEM_ATT
*(56 righe, 169781 ms)*
2) *explain SELECT COUNT(HDR_EVENT_NUMBER ) AS Y, HDR_EVENT_NUMBER AS TEXT
FROM PUBLIC.XDASV1 GROUP BY HDR_EVENT_NUMBER;*
PLAN
SELECT
COUNT(HDR_EVENT_NUMBER) AS Y,
HDR_EVENT_NUMBER AS TEXT
FROM PUBLIC.XDASV1
/* PUBLIC.XDASV1.tableScan */
GROUP BY HDR_EVENT_NUMBER
*(1 riga, 0 ms)*
3) *explain analyze SELECT COUNT(HDR_EVENT_NUMBER ) AS Y, HDR_EVENT_NUMBER
AS TEXT FROM PUBLIC.XDASV1 GROUP BY HDR_EVENT_NUMBER;*
PLAN
SELECT
COUNT(HDR_EVENT_NUMBER) AS Y,
HDR_EVENT_NUMBER AS TEXT
FROM PUBLIC.XDASV1
/* PUBLIC.XDASV1.tableScan */
/* scanCount: 406527 */
GROUP BY HDR_EVENT_NUMBER
/*
total: 255747
XDASV1.XDASV1_DATA read: 255331 (99%)
overflow read: 416 (0%)
*/
*(1 riga, 216422 ms)*
Il giorno 28 ottobre 2011 10:27, Noel Grandin <[email protected]> ha
scritto:
> Hi Angelo
>
> Try doing this:
>
> SELECT
> COUNT(HDR_EVENT_NUMBER ) AS Y,
> HDR_EVENT_NUMBER AS TEXT
> FROM PUBLIC.XDASV1
> GROUP BY HDR_EVENT_NUMBER
>
> Regards, Noel.
>
>
>
> Angelo Immediata wrote:
>
> Hi Thomas
>
> I tried your suggestion; these are results:
> *1) explain select count(ID) as y, HDR_EVENT_NUMBER as text from
> XDASV1 GROUP BY HDR_EVENT_NUMBER;*
>
> Result:
> *PLAN *
> *SELECT*
> * COUNT(ID) AS Y,*
> * HDR_EVENT_NUMBER AS TEXT*
> *FROM PUBLIC.XDASV1*
> * /* PUBLIC.XDASV1.tableScan */*
> *GROUP BY HDR_EVENT_NUMBER*
>
> *2) explain ANALYZE select count(ID) as y, HDR_EVENT_NUMBER as text from
> XDASV1 GROUP BY HDR_EVENT_NUMBER;*
>
> Result:
>
> *PLAN *
> *SELECT*
> * COUNT(ID) AS Y,*
> * HDR_EVENT_NUMBER AS TEXT*
> *FROM PUBLIC.XDASV1*
> * /* PUBLIC.XDASV1.tableScan */*
> * /* scanCount: 406527 */*
> *GROUP BY HDR_EVENT_NUMBER*
> */**
> *total: 255747*
> *XDASV1.XDASV1_DATA read: 255331 (99%)*
> *overflow read: 416 (0%)*
> **/*
>
> While this is the index I have on HDR_EVENT_NUMBER:
> *CREATE INDEX IF NOT EXISTS CMP_AUDIT_EVTNBR_IDX ON
> XDASV1(HDR_EVENT_NUMBER);*
> Do you have any other suggestion/idea?
>
> Thank you
> Angelo
>
> Il giorno 28 ottobre 2011 09:21, Thomas Mueller <
> [email protected]> ha scritto:
>
>> Hi,
>>
>> And how does the plan look like (explain analyze)?
>> http://h2database.com/html/performance.html#explain_plan
>>
>> Regards,
>> Thomas
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/h2-database?hl=en.
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
