Home » SQL & PL/SQL » SQL & PL/SQL » Extract directory path from a datafile name (11.2.x and 12.x, Windows)
Extract directory path from a datafile name [message #658481] Thu, 15 December 2016 09:32 Go to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
I'm writing a bit of code where I need to extract the directory path of some datafiles. I've come up with a technique that works:
orcla> select file_name,substr(file_name,1,length(file_name)-instr(reverse(file_name),'\')) p from dba_data_files;

FILE_NAME                                                         P
----------------------------------------------------------------- --------------------------------------
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_EXAMPLE_D2K76WXY_.DBF  C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_USERS_D2K74S2D_.DBF    C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_UNDOTBS1_D2K74S1F_.DBF C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_SYSAUX_D2K74S1F_.DBF   C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_SYSTEM_D2K74RZ6_.DBF   C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE
C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE\O1_MF_EXAMPLE_D54Y1JJK_.DBF  C:\APP\ORACLE\ORADATA\ORCLA\DATAFILE

6 rows selected.
but that seems ludicrously complicated and uses an undocumented function. Surely there's an easier way? Feel free to tell me if I missing something glaringly obvious.

Thank you for any insight.

--update: I can't rely on the db_create_file_dest parameter, the DBs may not be using only Oracle Managed Files

[Updated on: Thu, 15 December 2016 09:35]

Report message to a moderator

Re: Extract directory path from a datafile name [message #658485 is a reply to message #658481] Thu, 15 December 2016 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select file_name, regexp_substr(file_name,'^.*\\') dir
  2  from dba_data_files;
FILE_NAME                                          DIR
-------------------------------------------------- --------------------------------------------------
E:\ORACLE\BASES\MIKB2\SYSTEM01.DBF                 E:\ORACLE\BASES\MIKB2\
E:\ORACLE\BASES\MIKB2\SYSAUX01.DBF                 E:\ORACLE\BASES\MIKB2\
E:\ORACLE\BASES\MIKB2\UNDOTBS01.DBF                E:\ORACLE\BASES\MIKB2\
E:\ORACLE\BASES\MIKB2\TS_D0101.DBF                 E:\ORACLE\BASES\MIKB2\
E:\ORACLE\BASES\MIKB2\TS_I0101.DBF                 E:\ORACLE\BASES\MIKB2\
E:\ORACLE\BASES\MIKB2\ORAFAQ01.DBF                 E:\ORACLE\BASES\MIKB2\
Re: Extract directory path from a datafile name [message #658487 is a reply to message #658485] Thu, 15 December 2016 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's a simpler way with instr, if you set the position parameter to -1 it'll search backwards from the end of the string.
select substr(file_name, 1, instr(file_name, '/', -1, 1)) from dba_data_files;
Re: Extract directory path from a datafile name [message #658489 is a reply to message #658487] Thu, 15 December 2016 10:36 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
I knew there had to be a better way. I'm going with the CM solution (adjusted for Windows) because I always have to slow down when working out regular expressions. Oracle doesn't have to slow down: both solutions come up with the same execution plan.

Thank you, guys.

[Updated on: Thu, 15 December 2016 10:38]

Report message to a moderator

Re: Extract directory path from a datafile name [message #658491 is a reply to message #658487] Thu, 15 December 2016 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, silly I am, I have it in one of my scripts to display the opposite: the file name without the path. Smile

Re: Extract directory path from a datafile name [message #658516 is a reply to message #658491] Fri, 16 December 2016 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know the execution plan doesn't show the effect of oracle functions on selected columns.
If you want to know which is faster you'll actually have to execute against a large data set:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> set timing on
SQL> begin

for rec in (
with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 1000000)
select regexp_substr(file_name,'^.*\\') from data) loop
null;

end loop;
end;  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.15
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.10
SQL> begin

for rec in (
with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 100000)
select substr(file_name, 1, instr(file_name, '\', -1, 1)) from data) loop
null;

end loop;
end;  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL>


And it's the substr/instr. Though lets be honest, in normal usage you'd never notice the difference.
Re: Extract directory path from a datafile name [message #658517 is a reply to message #658516] Fri, 16 December 2016 03:26 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
I'm actually surprised: the cost of an operation in an exec plan does include CPU as well as IO in some obscure algorithm, and the cost of the various techniques so far has been the same. However, clearly you have proved that not to be accurate. If I have time, I'll run it through a trace. I would expect the extra time to be in the fetches, as the projected columns are prepared for return to the client.
Re: Extract directory path from a datafile name [message #658519 is a reply to message #658517] Fri, 16 December 2016 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
I wouldn't. If you move the function to the where clause you get exactly the same effect:
SQL> set autotrace traceonly

SQL> with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 1000000)
select * from data
where substr(file_name, 1, instr(file_name, '/', -1, 1)) = 'A';  2    3

no rows selected

Elapsed: 00:00:01.24

Execution Plan
----------------------------------------------------------
Plan hash value: 761049541

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    36 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                          |      |     1 |    36 |     2   (0)| 00:00:01 |
|   2 |   COUNT                        |      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SUBSTR("FILE_NAME",1,INSTR("FILE_NAME",'/',-1,1))='A')
   3 - filter(LEVEL<1000000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 1000000)
select * from data
where regexp_substr(file_name,'^.*\\') = 'A';  2    3

no rows selected

Elapsed: 00:00:05.89

Execution Plan
----------------------------------------------------------
Plan hash value: 761049541

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    36 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                          |      |     1 |    36 |     2   (0)| 00:00:01 |
|   2 |   COUNT                        |      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( REGEXP_SUBSTR ("FILE_NAME",'^.*\\')='A')
   3 - filter(LEVEL<1000000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

Plans are the same, but the instr is still faster.
Re: Extract directory path from a datafile name [message #658520 is a reply to message #658519] Fri, 16 December 2016 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Basically oracle isn't any good at factoring in how long functions take to execute, even internal ones (I wouldn't expect them to be any good at user created ones). I don't suppose they've fixed that in 12c.
Re: Extract directory path from a datafile name [message #658521 is a reply to message #658519] Fri, 16 December 2016 04:02 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Ah! But never mind what explain plan says, my database (12.1.0.2) applies the filter at the same point in both statements when I actually run them:
orclz>
orclz> set timing on
orclz> with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 1000000)
  2  select * from data
  3  where substr(file_name, 1, instr(file_name, '/', -1, 1)) = 'A';

no rows selected

Elapsed: 00:00:01.06
orclz> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8u1uug6kqa380, child number 0
-------------------------------------
with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as
file_name from dual connect by level < 1000000) select * from data
where substr(file_name, 1, instr(file_name, '/', -1, 1)) = 'A'

Plan hash value: 761049541

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     2 (100)|          |
|*  1 |  VIEW                          |      |     1 |    36 |     2   (0)| 00:00:01 |
|   2 |   COUNT                        |      |       |       |            |          |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SUBSTR("FILE_NAME",1,INSTR("FILE_NAME",'/',-1,1))='A')


23 rows selected.

Elapsed: 00:00:00.06
orclz>
orclz> with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as file_name from dual connect by level < 1000000)
  2  select * from data
  3  where regexp_substr(file_name,'^.*\\') = 'A';

no rows selected

Elapsed: 00:00:03.13
orclz> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dt8c4h2xb5p77, child number 0
-------------------------------------
with data as (select 'E:\ORACLE\BASES\MIKB2\SYSTEM'||rownum as
file_name from dual connect by level < 1000000) select * from data
where regexp_substr(file_name,'^.*\\') = 'A'

Plan hash value: 761049541

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     2 (100)|          |
|*  1 |  VIEW                          |      |     1 |    36 |     2   (0)| 00:00:01 |
|   2 |   COUNT                        |      |       |       |            |          |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( REGEXP_SUBSTR ("FILE_NAME",'^.*\\',HEXTORAW('78F8934CF97F0000D04
              2B743F77F000000000000000000008044B743F77F00000000000000000000000000000000000021
              00000000000000B8F8934CF97F000003000000000000000000000001000000'))='A')


25 rows selected.

Elapsed: 00:00:00.06
orclz>
I'm sure you are right, that the CBO produces a poor estimate for function evaluation. It is often stated that Oracle's regular expression implementation is slow, and combining that with the fact that I have never really studied REs as I should, I try to avoid them.
Re: Extract directory path from a datafile name [message #658522 is a reply to message #658521] Fri, 16 December 2016 04:07 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
I'd always assumed (perhaps wrongly) that CBO essentially ignored functions within the select [output]. It's irrelevant to it's task: Getting the required rows back as fast as possible, what the user does with the rows when they are back in terms of functions won't affect how long it takes to get them to that point. Obviously this changes where they are in predicates and we all know how arbitrary that gets. I'd never expect an execution plan to reflect the effort in a function within the projected columns, basically.

I could be wrong, it was just my original belief.

[Updated on: Fri, 16 December 2016 04:16]

Report message to a moderator

Re: Extract directory path from a datafile name [message #658523 is a reply to message #658522] Fri, 16 December 2016 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
John - that was the execution plan I showed, not the explain plan and it shows the same as yours.
As far as I can tell oracle basically ignores how long functions take. It'll show them being run but the only affect they ever have on a plan is stop it using an index if they're wrapped around an indexed column.
Re: Extract directory path from a datafile name [message #658524 is a reply to message #658523] Fri, 16 December 2016 04:32 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Remember that autotrace doesn't show the plan used, it just runs explain plan after running the statement (or not with traceonly explain)
Re: Extract directory path from a datafile name [message #658525 is a reply to message #658524] Fri, 16 December 2016 04:41 Go to previous message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Fair enough, but it doesn't really change anything - it's showing what you get anyway.
Previous Topic: Skipping code in a procedure?
Next Topic: dynamic sql to grant privilege
Goto Forum:
  


Current Time: Fri Oct 19 11:28:25 CDT 2018