Think simple and spare yourself a facepalm

On one of my training sessions, I was presented with a simple query which looked like this:

SELECT company,
            COUNT(*)
FROM invoices
WHERE can_access( company ) = 1
GROUP BY company;

Execution plan with statistics was a follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      2 |        |       |   264 (100)|          |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   1 |  VIEW                   |                   |      2 |   1733 | 36393 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   2 |   HASH GROUP BY         |                   |      2 |   1733 | 15597 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      2 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:03.00 |    2402 |       |       |          | |*  4 |     INDEX FAST FULL SCAN| INVOICES          |     82 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:02.74 |    2402 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$2 / from$_subquery$_001@SEL$1    2 - SEL$2    4 - SEL$2 / INVOICES@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("CAN_ACESS"(TO_NUMBER("COMPANY"))=1)

Function can_access was containing a single SQL and some simple PL/SQL code. Purpose of this function was to filter records based on privileges of current user.

First, I told them, that I don’t like the usage of PL/SQL function at all in this kind of situation. It’s a CPU burner on SQL and PL/SQL context switches in first place, and second, it’s hiding some important information from Oracle optimizer (selectivity for example).

Response was that its legacy stuff and that they have to deal with it somehow … ouch. The problem was, that they knew that function could be ran on grouped result set (limiting calls by great deal), but they was unable to force Oracle to do so.

So I tried usual shenanigans with parentheses, no_merge, no_query_transformation and great deal of begging. But it was to no use. Execution plan looked always the same. In the end I used the following trick to do the job:

SELECT * FROM
            (SELECT /*+ no_merge */
                        company,
                        COUNT(*)
            FROM invoices
            GROUP BY company)
WHERE (SELECT can_access( company ) FROM DUAL) = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                   |      1 |        |       |  2758 (100)|          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |*  1 |  FILTER                  |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |   2 |   VIEW                   |                   |      1 |   2536 | 55792 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   3 |    HASH GROUP BY         |                   |      1 |   2536 | 22824 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |    17M|  3187K|     1/0/0| |   4 |     PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   5 |      INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | |   6 |   FAST DUAL              |                   |   2537 |      1 |       |     2   (0)| 00:00:01 |       |       |   2537 |00:00:00.01 |       0 |       |       |          | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    5 - SEL$2 / INVOICES@SEL$2    6 - SEL$3 / DUAL@SEL$3 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(=1)

Half processed buffers, nice time, great! After that I left home. Next day I’m telling my success story to a colleague of mine and he is like

“... Umm ... why don’t you use HAVING?”

 So final solution should have looked like this:

SELECT company,
            COUNT(*)
FROM invoices
GROUP BY company
HAVING can_access(company) = 1;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      1 |        |       |   221 (100)|          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |*  1 |  FILTER                 |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   2 |   HASH GROUP BY         |                   |      1 |     26 |   234 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.14 |    1201 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   4 |     INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    4 - SEL$1 / INVOICES@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("CAN_ACCESS"(TO_NUMBER("COMPANY"))=1)

So remember folks … don’t try to solve stuff, when you are tired after a long day and think simple. You will spare yourself some facepalm.