Is there performance impact when Non-Aggregate SQL functions are used in a SELECTed Column?

Question

We have a report that uses a long and complex query that has the SELECT statement like below:

SELECT  
    NVL(nazwawystawcy,'BRAK') supplier_name, 
    NVL(AdresDostawcy,'BRAK') supplier_address,  
    NVL(NrDostawcy,'BRAK') supplier_registration, 
    DowodZakupu document_number, 
    DataZakupu document_issue_date,
    DataWplywu document_recording_date,
    trx_id,
    KodKrajuNadaniaTIN country_code,
    DokumentZakupu document_type_code,
    payment_split MPP,
    box_number box_number,
    box_amount box_amount,
    box_type box_type,
    display_order display_order
... 
FROM table1 t1
    ,table2 t2
....

We recently made modifications to this Query and just modified the 3rd SELECTed column to add a REGEXP_LIKE

SELECT  
    NVL(nazwawystawcy,'BRAK') supplier_name, 
    NVL(AdresDostawcy,'BRAK') supplier_address,  
    --NVL(NrDostawcy,'BRAK') supplier_registration, 
    Case When (NrDostawcy is not null and regexp_like(substr(NrDostawcy,1,2),'^[a-zA-Z]*$')) Then substr(NrDostawcy,3) else NVL(NrDostawcy,'BRAK') End supplier_registration,
    DowodZakupu document_number, 
    DataZakupu document_issue_date,
    DataWplywu document_recording_date,
    trx_id,
    KodKrajuNadaniaTIN country_code,
    DokumentZakupu document_type_code,
    payment_split MPP,
    box_number box_number,
    box_amount box_amount,
    box_type box_type,
    display_order display_order
... 
FROM table1 t1
    ,table2 t2
....

I checked the Explain Plans of both queries and they turned out to have the same Plan hash value. Does this mean there's no impact on performance if i use Seeded, non-aggregate, SQL Functions in SELECTed columns? I believe there is an impact in performance if they're used in the WHERE clause, but i wasn't sure if the same applies to the SELECTed columns.

Apologies in advance as i can't provide the exact query since it's propietary and is very long and complex. I also don't think I can create a good enough sample that would match the Explain plan of actual query as it joins over 10 tables, with thousand rows of data.

Thank you!

Answer 1

it depends upon the query and the IO's being done to get the data. Sometimes you can try creating a Oracle Function based index, you may see some improvements.

Check this link, it could help you.

https://jeffkemponoracle.com/2007/11/will-oracle-use-my-regexp-function-based-index/

thanks

Answer 2

Since you are running this query on Oracle here's my advice. Run the query with Oracle hint /*+ gather_plan_statistics */. Run it with the first query without regex and with the regex. Then find this query in sharedpool (v$sql). The hint will give you the exact buffer gets, physical reads an also time spent in each step of the plan. With that data you can analyze in details how much more time query with regex needed to execute. I advice you, that you do this on data that returns you more than lets say 10k rows. In this way the difference should be seen (if you run this with 100 rows no difference will be seen).

Answer 3

The execution plan is the same as it needs to query exactly the same data from the same tables. You should also see the amount of data (logical IO) unchanged.

What will not be the same however is the execution time, as the regexp_like will consume more CPU, even if you see the logical IO unchanged.

Note that if you changed the selected columns, the execution plan could change as if all selected columns were part of an index, the optimizer might skip the table access and read the data from an index only.