Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Thumbs down Unanswered: IOT and Optimizer

    Hi All,
    From the documentation, I came to know that if a table is IOT then it always uses CBO for the queries even if the OPTIMIZER_MODE is set to RULE.
    But it doesn't happen. Can anybody explain me why?
    I have set OPTIMIZER_MODE=RULE in the parameter file.

    e.g. There are 2 tables employess and tblEmployees. The former one is a HOT and the second one is IOT.
    I enabled autotrace and did the following:

    SET autotrace trace

    select * from hr.employees where employee_id=100;
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES'
    2 1 INDEX (UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE)

    select * from hr.tblemployee where employee_id=100;
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=1 Bytes=133)
    1 0 INDEX (UNIQUE SCAN) OF 'PK_EMPLOYEE' (UNIQUE) (Cost=1 Card
    =1 Bytes=133)

    If you notice both queries are using RBO.

    Thanks in advance
    qAnand

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No - the second statement's plan shows cost information, indicating that the CBO was in fact used. The "Optimizer=RULE" comment just shows the optimizer mode that was in force at the time the statement ran.

  3. #3
    Join Date
    Jul 2003
    Posts
    70
    Ok, Thanks for your info. BTW where will I get the information that it has used CBO instead of RBO

    Regards,
    Anand

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by qAnand
    Ok, Thanks for your info. BTW where will I get the information that it has used CBO instead of RBO
    Right here:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=1 Bytes=133)
    1 0 INDEX (UNIQUE SCAN) OF 'PK_EMPLOYEE' (UNIQUE) (Cost=1 Card
    =1 Bytes=133)


    If the plan shows a cost, then it came from the CBO.

Posting Permissions

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