Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: LIKE operator causing Performance issue

    Hi All,

    In a Stored Procedure a Cursor has been declared where LIKE '%string' has been used with WHERE clause along with another search condition. Below is the example:

    Select City, State, End_Date
    From Customer
    Where CName Like %KUMAR%
    And End_Date IS NULL;

    The above query is causing a performance issue.

    A combined Index ( Cname, End_Date) has been created but the query is still going for the FULL table scan.

    Syntax of Creating the Index: Create Index ind_1 ON Customer (Cname, End_Date)

    It would be nice if any one can help out to get the better performance.


    Thanks with Regards,
    JD

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jayanta_deb View Post
    Hi All,

    In a Stored Procedure a Cursor has been declared where LIKE '%string' has been used with WHERE clause along with another search condition. Below is the example

    It would be nice if any one can help out to get the better performance.
    A LIKE '%something' is never going to use an index.
    If you really need that, you will have to use Oracle's full text-search feature.

    Having said that: if only a few rows have end_date = NULL, you might be successful with creating the following index:

    Code:
    CREATE INDEX ind_2 ON customer (end_date, 1);
    That will allow Oracle to use that index for the end_date = NULL condition.
    Note the constant vlaue 1 in the index. Without that, NULL values for end_date will not make it into the index.

    If the majoriy of rows (e.g. more than 20-30%) have end_date <> NULL the full table scan is the faster plan anyway.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So, if that second argument allows NULLs into the index (which is pretty cool, I've never seen that as an option before), why would you ever use the default? The only situation I can think of is if it gains you something on the optimization side (like ... if you're INSERTing records like crazy, and they initially start out with a NULL in this column).

    But functionally, I've always wondered why NULLS have been excluded from the index.


    --=cf

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes View Post
    But functionally, I've always wondered why NULLS have been excluded from the index.
    To be precise: NULLs are not excluded, only tuples where all columns are NULL are excluded (which is a small but important difference).

    If I remember correctly, the reason for this is that some kind of "hash" needs to be calculated to be able to store the tuple. If all values of the tuple are NULL, the hash does not make sense.

    To give the credits to whom they belong: I learned this trick on a Tom Kyte seminar

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I guess then, why does the optional parameter

    Code:
    CREATE INDEX ind_2 ON customer (end_date, 1);
    now cause the hash to make sense? Not trying to beg the question anymore really. I guess what you're saying is that at some point in the past, the ability to index NULL values was a technical problem, and they've somehow rememdied it. But, they don't want to change the original behavior of the CREATE INDEX statement in case it causes applications to break/respond differently?

    Code:
    CREATE INDEX ind_2 ON customer (end_date);

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes View Post
    Not trying to beg the question anymore really. I guess what you're saying is that at some point in the past, the ability to index NULL values was a technical problem, and they've somehow rememdied it
    No, Oracle is still not "indexing a NULL value", because by adding a constant the tuple is never null.

    If you create an index on e.g. (col1, col2) a row where col1 IS NULL and col2 IS NULL would not be indexed as well.

    I don't really now how Oracle actually builds up the index blocks (and I'm too lazy to read up the concepts manual on that right now) but for some reason it seems to make sense to not index a tuple that completely consists of null values.

    AFAIK other DBMS (Postgres, DB2) do it the same way, so I guess it's something to do with how index trees are calculated.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    but for some reason it seems to make sense to not index a tuple that completely consists of null values
    That's the piece I'm trying to figure out.

    For instance, I've always wanted an index for NULL values for a system where there are about 3-400 records where COST_CODE is NULL, out of about 4 million rows. but instead, we've been enduring a table scan when people are looking for those 3-400 recs.

    It's always just irked me that I would need to either live with it, or create a dummy value which means "NULL", and enter it in the table. Or I probably could also try to set up a FUNCTION-BASED index, which also just seems like overkill when a NULL value could just be indexed.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The problem is that null is not a value. It means unknown value so how can you index an unknown value? Its just the way the standard works.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    In a programming sense (and a philosophical one) NULL is unknown. But the system obviously knows which values are NULL, since you can write a query like

    Code:
    select * from table where column is null
    Since it can identify it ... then it should be able to index, which, it looks like it's now possible (although I don't know when this feature surfaced).

    I'm just trying to get some advice on why I shouldn't always include this parameter on my CREATE INDEX statements, if I've got plenty of space, and I am not incurring performance problems as a result of indexing NULLs along with all the other values in an already-indexed column.

    --=Chuck

Posting Permissions

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