Monday, March 30, 2015

Join using Like Condition - Using Oracle Text

This is also based on one of my posting in OTN. Here, I have demonstrated the use of Oracle Text in place of LIKE operator. This may give significant benefit in terms of performance.

The problem.
create table location_source
(code number,
description varchar2(100)
);

 

insert into location_source values('', 'check in london');
insert into location_source values('', 'flight Paris 9.30');
insert into location_source values('', 'baggage allowance 20kg Hong kong');
insert into location_source values('', 'check out 6.30');
insert into location_source values('', 'new york 23rd Jul');
insert into location_source values('', 'flight jy688 sydney');

 

create table location_lookup
(code number
location_lookup varchar2(10)
);

 

insert into location_lookup values (001, 'London');
insert into location_lookup values (002, 'London');
insert into location_lookup values (003, 'Paris');
insert into location_lookup values (004, 'Hong Kong');
insert into location_lookup values (005, 'Paris');
insert into location_lookup values (006, 'New York');

The SQL looks something like this:

select s.*, min(l.code) , l.location_lookup
from location_source s, locaction_lookup l
and UPPER(s.description||'%') like upper(l.location_lookup||'%')
group by s.*, l.location_lookup

 

Output Required:

 
Code	Description	                  Code	Location_Lookup
	check in london	                  001	London
	flight Paris 9.30                 003	Paris
	baggage allowance 20kg Hong kong  004	Hong Kong
	check out 6.30		
	new york 23rd Jul	          006	New York
	flight jy688 sydney

Now, The problem is other methods using LIKE operator is running slow for huge amount of data. Oracle Text is very useful in these cases.

Preparing SCOTT to use Oracle Text:
    SQL>   
    SQL> SHOW USER  
    USER is "SYS"  
    SQL>   
    SQL> GRANT ctxapp TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;  
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  

Now the actual test::
    SQL> SHOW user  
    USER is "SCOTT"  
    SQL> --My database version.  
    SQL> SELECT * FROm v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> SELECT * FROM location_source;  
      
          CODE DESCRIPTION  
    ---------- ----------------------------------------------------------------------------------------------------  
               check in london  
               flight Paris 9.30  
               baggage allowance 20kg Hong kong  
               check out 6.30  
               new york 23rd Jul  
               flight jy688 sydney  
      
    6 rows selected.  
      
    SQL> SELECT * FROM location_lookup;  
      
          CODE LOCATION_L  
    ---------- ----------  
             1 London  
             2 London  
             3 Paris  
             4 Hong Kong  
             5 Paris  
             6 New York  
      
    6 rows selected.  
      
    SQL> CREATE INDEX location_source_idx ON location_source(description) INDEXTYPE IS CTXSYS.CONTEXT;  
      
    Index created.  
      
    SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LOCATION_SOURCE', cascade=>TRUE);  
      
    PL/SQL procedure successfully completed.  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT ls.code,  
      2    ls.description,  
      3    MIN(ll.code) new_code,  
      4    ll.location_lookup  
      5  FROM location_source ls  
      6  LEFT OUTER JOIN location_lookup ll  
      7  ON contains(ls.description,ll.location_lookup)>0  
      8  GROUP BY ls.code,  
      9    ls.description,  
     10*   ll.location_lookup  
    SQL> /  
      
          CODE DESCRIPTION                                                                                            NEW_CODE LOCATION_L  
    ---------- ---------------------------------------------------------------------------------------------------- ---------- ----------  
               check in london                                                                                               1 London  
               baggage allowance 20kg Hong kong                                                                              4 Hong Kong  
               check out 6.30  
               new york 23rd Jul                                                                                             6 New York  
               flight Paris 9.30                                                                                             3 Paris  
               flight jy688 sydney  
      
    6 rows selected.  

Tuesday, March 10, 2015

Listing Operating system disk usages with Oracle External Table PREPROCESSOR feature

This is based on one of my posting in OTN.
Database version and directory setups:

    SQL> SHOW user  
    USER is "SYS"  
    SQL>  
    SQL> -- My database version.  
    SQL> SELECT * FROM v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> --Creating directory object.  
    SQL> CREATE OR REPLACE DIRECTORY saubhik AS '/home/oracle/saubhik';  
      
    Directory created.  
    SQL> GRANT READ, WRITE, EXECUTE ON DIRECTORY saubhik to scott;  
      
    Grant succeeded.  
      
    SQL>
    
    Now, the OS setups:
    1. [oracle@localhost saubhik]$  
    2. [oracle@localhost saubhik]$ ## My Operating System.  
    3. [oracle@localhost saubhik]$ uname -a  
    4. Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
    5. [oracle@localhost saubhik]$ pwd  
    6. /home/oracle/saubhik  
    7. [oracle@localhost saubhik]$ cat free_space.sh  
    8. #This shell script is call by External Table's PREPROCESSOR claus.  
    9. # As per the external table definitin the file named as "frespace.txt" is passed as argument 1 ($1) to this script.  
    10. #!/bin/bash  
    11.   
    12. # Reading the output of the file into a varriable  
    13. a=`/bin/cat $1`  
    14.   
    15. # Listing the space informations  
    16. /bin/df -Pkh $a  
    17. exit  
    18. [oracle@localhost saubhik]$  
    19. [oracle@localhost saubhik]$ cat freespace.txt  
    20. /home  
    21. [oracle@localhost saubhik]$  
    Now the External Table setup:
    1. SQL>  
    2. SQL> SHOW USER  
    3. USER is "SCOTT"  
    4. SQL> --My database version.  
    5. SQL> SELECT * FROM v$version;  
    6.   
    7. BANNER  
    8. --------------------------------------------------------------------------------  
    9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    10. PL/SQL Release 11.2.0.1.0 - Production  
    11. CORE    11.2.0.1.0      Production  
    12. TNS for Linux: Version 11.2.0.1.0 - Production  
    13. NLSRTL Version 11.2.0.1.0 - Production  
    14.   
    15. SQL> -- Dropping the table before creating. You may not ned this.  
    16. SQL> DROP TABLE freespace_ext PURGE;  
    17.   
    18. Table dropped.  
    19.   
    20. SQL> --Creating the table.  
    21. SQL> ed  
    22. Wrote file afiedt.buf  
    23.   
    24.   1   CREATE TABLE "SCOTT"."FREESPACE_EXT"  
    25.   2     (       "FILESYSTEM" VARCHAR2(50 BYTE),  
    26.   3     "FSIZE" VARCHAR2(20 BYTE),  
    27.   4     "USED" VARCHAR2(20 BYTE),  
    28.   5     "FREE" VARCHAR2(20 BYTE),  
    29.   6     "USE_PER" VARCHAR2(20 BYTE),  
    30.   7     "MOUNTPOINT" VARCHAR2(20 BYTE)  
    31.   8     )  
    32.   9     ORGANIZATION EXTERNAL  
    33. 10      ( TYPE ORACLE_LOADER  
    34. 11        DEFAULT DIRECTORY "SAUBHIK"  
    35. 12        ACCESS PARAMETERS  
    36. 13        ( RECORDS DELIMITED BY NEWLINE  
    37. 14             PREPROCESSOR SAUBHIK: 'free_space.sh'  
    38. 15             skip 2  
    39. 16             badfile SAUBHIK:'freespace_ext%a_%p.bad'  
    40. 17             logfile SAUBHIK:'freespace_ext%a_%p.log'  
    41. 18             fields terminated by whitespace lrtrim  
    42. 19             missing field values are null (filesystem,  
    43. 20                                            fsize ,  
    44. 21                                            used ,  
    45. 22                                            free ,  
    46. 23                                            use_per ,  
    47. 24                                            mountpoint)  
    48. 25                                               )  
    49. 26        LOCATION  
    50. 27         ( "SAUBHIK":'freespace.txt'  
    51. 28         )  
    52. 29      )  
    53. 30     REJECT LIMIT UNLIMITED  
    54. 31*   PARALLEL 2  
    55. SQL> /  
    56.   
    57. Table created.  
    58.   
    59. SQL> -- A sample testing. Our /home/oracle/freespace.txt contains /home. So it should report the usages of that.  
    60. SQL> SELECT * FROM freespace_ext;  
    61.   
    62. FILESYSTEM                                         FSIZE                USED                 FREE                 USE_PER              MOUNTPOINT  
    63. -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------  
    64. Filesystem                                         Size                 Used                 Avail                Use%                 Mounted  
    65. /dev/mapper/VolGroup00-LogVol04                    2.0G                 104M                 1.8G                 6%                   /home  
    66.   
    67. SQL>  
    Now if you want it in a flexible way:
      SQL> SQL> SHOW USER USER is "SCOTT" SQL> --Creating a pipeline table function to get the free space. The mount point passed as an argument. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE freespace_typ AS OBJECT 2 ( filesystem VARCHAR2(50), 3 fsize VARCHAR2(20), 4 used VARCHAR2(20), 5 free VARCHAR2(20), 6 use_per VARCHAR2(20), 7 mountpoint VARCHAR2(20) 8* ) SQL> / Type created. SQL> CREATE OR REPLACE TYPE freespace_tbl_typ AS TABLE OF freespace_typ; 2 / Type created. SQL> ed Wrote file afiedt.buf 1 ---My wrapper function. 2 CREATE OR REPLACE 3 FUNCTION get_free_space 4 ( 5 pi_path VARCHAR2) 6 RETURN freespace_tbl_typ PIPELINED 7 AS 8 v_file_handle utl_file.file_type; 9 v_dir_name VARCHAR2(50):='SAUBHIK'; 10 v_max_linesize INTEGER :=32767; 11 v_file_name VARCHAR2(50):='freespace.txt'; 12 v_write_buffer VARCHAR2(4000); 13 BEGIN 14 v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize); 15 v_write_buffer:=pi_path; 16 utl_file.put_line(v_file_handle,v_write_buffer,TRUE); 17 utl_file.fclose(v_file_handle); 18 FOR i IN 19 (SELECT filesystem, 20 fsize , 21 used , 22 free , 23 use_per , 24 mountpoint 25 FROM freespace_ext 26 ) 27 LOOP 28 PIPE ROW (freespace_typ(i.filesystem,i.fsize,i.used,i.free,i.use_per,i.mountpoint)); 29 END LOOP; 30 RETURN; 31* END get_free_space; SQL> / Function created. SQL> SELECT * FROM TABLE(get_free_space('/')); FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('/u01')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 /dev/mapper/VolGroup00-LogVol04 2.0G 104M 1.8G 6% /home /dev/mapper/VolGroup00-LogVol03 5.7G 851M 4.6G 16% /tmp /dev/sda1 99M 13M 82M 14% /boot tmpfs 1.5G 719M 782M 48% /dev/shm 7 rows selected. SQL>