PETER - getting started with Oracle
- Download the source code and unpack it
- Prepare a your EST in an appropriate table, that contains at least two columns - one for the EST string's ID and one for the EST itself. The EST strings should only contain the characters A,C,G,T
- Compile PETER as indicated in the instructions for command-line usage
- Copy CppPTIndexInterface.so to $ORACLE_HOME/bin and set proper file and owner rights to the library file
- Make sure that Oracle is configured for the handling of external procedures. Sample files for listener.ora and tnsnames.ora are listed below, configuration details can be found in the database manual. You might need to restart the listener after reconfiguration.
----tnsnames.ora----------------------------------------------
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = thinkpad)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
----------------------------------------------------------------
----listener.ora------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = thinkpad)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
----------------------------------------------------------------
- Test the connection with
tnsping EXTPROC_CONNECTION_DATA - Run install_peter.sql to register the index and table functions
- Create an index on your table with the following command:
CREATE INDEX peter_idx ON <TABLE>(<ATTRIBUTE>) INDEXTYPE IS Peter
Example: create an index on table dbEST, column EST:
CREATE INDEX peter_idx on dbEST(EST) INDEXTYPE IS Peter - Run similarity searches
- Search for a single pattern with Hamming distance in a table with a PETER index on the EST column:
SELECT * FROM <TABLE> WHERE PThd(<ATTRIBUTE>,<PATTERN>,<THRESHOLD>)=1
Example (for hamming-distance=2):
SELECT * FROM dbEST WHERE PThd(EST, 'ACCGTTAAAGTC',2)=1 - Search for a single pattern with Edit distance in a table with a PETER index on the EST column:
SELECT * FROM <TABLE> WHERE PTed(<ATTRIBUTE>,<PATTERN>,<THRESHOLD>)=1
Example (for edit-distance=2):
SELECT * FROM dbEST WHERE PTed(EST, 'ACCGTTAAAGTC',2)=1
- Search for a single pattern with Hamming distance in a table with a PETER index on the EST column:
- Run similarity joins
Make sure that you have created an index on both EST columns.- Join two EST indices on Hamming distance
SELECT * from Table(PTHDJOIN(<PETER_INDEX_TABLE_A>,<PETER_INDEX_TABLE_B>,<THRESHOLD>)
Example for k=1:
SELECT * from Table(PTHDJOIN(peter_idx_tbl_a,peter_idx_tbl_b,1)
- Join two EST indices on Edit distance
SELECT * from Table(PTEDJOIN(<PETER_INDEX_TABLE_A>,<PETER_INDEX_TABLE_B>,<THRESHOLD>)
Example for k=1:
SELECT * from Table(PTEDJOIN(peter_idx_tbl_a,peter_idx_tbl_b,1)
- Join two EST indices on Hamming distance
For any questions or comments, please contact Astrid Rheinländer (rheinlae (youknowwhat) informatik.hu-berlin.de).