SQL tuning AND operator.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL tuning AND operator.

  1. #1
    Join Date
    Jul 2001
    Posts
    6

    Exclamation

    The following statement:

    select order_number, order_total, order_date, customer_id, order_type_id
    from coe_orders
    where order_date > sysdate - 7
    and order_type_id = 1019 and order_total > 400
    order by order_total;

    If we look at the where clause: order date has its own index and order_type_id has its own index in coe_orders.
    However, no matter which way I'am ordering the columns in the where clasue the optimizer always chooses to use the index on the order_type_id column (which by the way has many more records than sysdate-7).
    Beside using a hint is there another way to change the Where clause so the optimizer chooses the most restrictive Index? what's the process that the optimizer chooses between two indexes?

    Thank You very much for any help.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Are you using cost based optimizer? Do you have accurate statistics collected about those table and indexes?

    Anyway, if you want to prevent optimizer to use index on ORDER_TYPE_ID without using a hint, you can change that part of WHERE clause from

    and order_type_id = 1019

    to

    and order_type_id+0 = 1019

    That way optimizer will not be able to use index on ORDER_TYPE_ID, so it will choose to use the index on ORDER_DATE.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Keep in mind that numerous factors could impact on the Cost Based Optimizer:

    - sort_area_size in case you do not set hash_area_size
    - hash_join_enabled and hash_area_size
    - db_file_multiblock_read_count might influense the full scans
    - samples in analyze (I use compute for tables as well, not just for indexes






  4. #4
    Join Date
    Jul 2001
    Posts
    6
    I'am only using the rule based optimizer.

    Thank You, your replies were very helpful.

Posting Permissions

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


Click Here to Expand Forum to Full Width