Results 1 to 9 of 9

Thread: Oracle queries

  1. #1
    Join Date
    Jul 2003
    Posts
    46

    Unanswered: Oracle queries

    Hi,
    I have two queries which are executed very fast (about 600ms):
    1.select * from t where a like 'aaa%';
    2.select * from t where b like 'bbb%';
    and the third one which is executed very long (over three minutes):
    3.select * from t where a like 'aaa%' and b like 'bbb%';
    I have indices on column a and column b.
    Why does the execution of the third query take so much time?
    Oracle uses index on b when executes third query. There are much more records satisfies condition b like 'bbb%' then condition a like 'aaa%'. But I have these two indices and index a was created later than b and I cann't do much about these.
    But in my opinion if queries 1. and 2. are executed very fast then query 3. also should be executed fast (a little slowlier then 1. or 2. but not over 3 minutes). If it is not true please explain me why and what are possible solutions ?
    Thank you very much
    Szalas

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Show the EXPLAIN PLANs for all three queries

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    and also state whether you have an index on columns a,b

    if not, then you should create one.

    OR, you could rewrite the query like this:

    PHP Code:
    select a,b from 
     
    (select a,b from t where a like 'aaa%')
    where b like 'bbb%'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    It's a long story, execution plans are based on an estimate of what Oracle expects to occur. How does it know that an index scan on condition/index b will return more rows than an index scan on condition/index a?

    It doesn't but you can help it learn a little about your data by instructing it to analyse the tables and or indexes. Oracle will then understand a little more about the selectivity (efficiency) of an index.

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Jul 2003
    Posts
    46
    Hi

    I do not have index on columns a,b. I feel that such an index would be the solution but I'm not allowed to create indices on this table.
    So I understand that if there are 1 000 000 rows satisfies condition b like 'bbb%' execution of the third query is equal to the execution of query performed on the table with 1 000 000 rows with full scan and this is why it takes so long.

    Thank you very much
    Szalas

  6. #6
    Join Date
    Jul 2003
    Posts
    46
    Hi,

    And also I have only rule based optimizer.

    Szalas

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Using rule based optimiser, Oracle uses a rule rank for each method of access.

    For example, an equals condition on a column with a unique constraint or primary key is likely to be ranked higher than an equals condition on a column with a simple index (as it is likely to return less rows).

    I believe that for conditions which have equal ranks, then they are dealt with in order (bottom up) in your query. You could confirm this by changing you query from where a... and b... to where b.... and a.....

    An index on a+b would not help for your particular condition, as it could only use the (a) part of the index anyway.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Jul 2003
    Posts
    46
    Hi,
    In my opinion rule based optimizer choses last created index if there are two equal rank indices.
    I agree that index on a+b won't be used if there are index on a and index on b.
    I'm interesting in such situation that there are no indices on a and b only one on a+b. Oracle will chose index a+b.
    Will Oracle check all conditions without touching table or will only find rows with column a like 'aaa%' using index ,then take them from table and check second condition ?
    Thank you for your patient and help
    Szalas

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm interesting in such situation that there are no indices on a and b only one on a+b. Oracle will chose index a+b.
    Will Oracle check all conditions without touching table or will only find rows with column a like 'aaa%' using index ,then take them from table and check second condition ?
    Yes, having an index on a+b with a where clause of "a like 'aaa%' and b like 'bbb%' " would effectively be similar to having an index on a only. The index on a+b cannot be used to resolve the second part of the clause (bbb%) due to the like condition on a.

    a = 'aaa' and b like 'bbb%' could use index

    a like 'aaa%' and b like 'bbb%' can only use index for the a like 'aaa%', the rest would need to be filtered by oracle.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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