About Oracle performance (Multi-SELECT + Single COUNT() vs Single-SELECT + Multi COUNT() )

Question

Which is better(for only speed) way?

1. Single SELECT (Multi COUNT(), Multi TO_CHAR())

SELECT
    T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD') RG_DATE, COUNT(T.UUID) VISITS
FROM
    ACCOUNT T
GROUP BY
    T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD')
HAVING 
    COUNT(T.UUID) > 0

EDIT

SELECT
  T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD') RG_DATE, COUNT(T.UUID) VISITS
FROM
  ACCOUNT T
GROUP BY
  T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD')
HAVING 
  COUNT(T.UUID) > 0
  AND COUNT(T.UUID) >= 2       -- Spring mybatis generated code
  AND COUNT(T.UUID) < 5        -- Spring mybatis generated code

2. Double SELECT (Single COUNT(), Multi TO_CHAR())

SELECT
    T.UUID, T.RG_DATE, T.VISITS
FROM (
    SELECT
        T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD') RG_DATE, COUNT(T.UUID) VISITS
    FROM
        ACCOUNT T
    GROUP BY
        T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD')
    ) T
WHERE
    T.VISITS > 0

3. Multi SELECT (Single COUNT(), Single TO_CHAR())

SELECT
    T.UUID, T.RG_DATE, T.VISITS
FROM (
    SELECT
        T.UUID, T.RG_DATE, COUNT(T.UUID) VISITS
    FROM (
        SELECT
            T.UUID, TO_CHAR(T.RG_DATE, 'YYYY-MM-DD') RG_DATE
        FROM
            ACCOUNT T
        ) T
    GROUP BY
        T.UUID, T.RG_DATE
    ) T
WHERE
    T.VISITS > 0

I've test this in small db and the difference is very small, so I can't decide which is the best one. How about large(maybe giant) db?

Answer 1

Oracle optimizer will transform 3rd query to the equal to 2nd (subquery view merging transformation), so they will have equal plans by default.

First one is better: having will be executed as a filter of group-by operation. If you compare execution plans, you will see the difference.

I'd suggest to use trunc(rg_date) instead of to_char with proper nls_date_format.