Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: ContainsTable function in Oracle

    Hi,

    I would like to know the equivalent function or any workarounds for the SQLServer specific 'containsTable' in Oracle,..

    Regards,
    Shefu

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    For those of us who don't know SQL Server, could you tell us what exactly "containsTable" does?

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Code:
    CONTAINSTABLE : 
    Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
    
    Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
    
    Syntax
    
    CONTAINSTABLE ( table , { column | * } , ' < contains_search_condition > '
        [ , top_n_by_rank ] )
    
    < contains_search_condition > ::=
            { < simple_term >
            | < prefix_term >
            | < generation_term >
            | < proximity_term >
            |  < weighted_term >
            }
            | { ( < contains_search_condition > )
            { AND | AND NOT | OR } < contains_search_condition > [ ...n ]
            }
    
    < simple_term > ::=
        word | " phrase "
    
    < prefix term > ::=
        { "word * " | "phrase * " }
    
    < generation_term > ::=
        FORMSOF ( INFLECTIONAL , < simple_term > [ ,...n ] )
    
    < proximity_term > ::=
        { < simple_term > | < prefix_term > }
        { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]
    
    < weighted_term > ::=
        ISABOUT
            ( { {
                    < simple_term >
                    | < prefix_term >
                    | < generation_term >
                    | < proximity_term >
                    }
                [ WEIGHT ( weight_value ) ]
                } [ ,...n ]
            )

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Oracle Text is what you need .

    You will have to create a Full-Text index on the column you want to search in. You have 2 types of index you can create for that : CTXCAT for small columns (5 lines max) or CONTEXT. The two have diffferent search options associated with them.

    CTXCAT is more efficient especially if you have to search on another criteria than the FTS (on a date for example) since you can index other columns with standard B-Tree index WITHIN the CTXCAT structure, but this kind of index takes a lot of space, hence its limit for small columns.

    CONTEXT indexes offer more possibilities in terms of search options on the FTS column, but performance suffers A LOT if you have to filter on other columns.

    A drawback of CONTEXT is that up to 9iR2, it needs manual synchronisation (the index is not updated at each insert/update). From 10gR1, you can choose to update it at each DML operation. CTXCAT indexes are always automatically synchronised.

    Now I'd suggest you read the documentation .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •