Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212

    Question Unanswered: same SELECT statement 2 different explain plans?

    Hi all,
    can somebody please explain what is the defference between these two:

    1) Scenario 1:
    There is default oracle schema in 10g called OE. It has some data in it includiding table ORDERS. this table has 105 records and unique index on ORDER_ID.

    2) Scenario 2:
    I created identical table on my own schema using:
    create table tmp_ORDERS as select * from OE.ORDERS;

    then I created same index as is on original table:
    create unique index tmp_orders_pk on tmp_ORDERS(ORDER_ID);

    When I run explain plan for:
    select count(*) from oe.orders; -- oracle original schema and table
    it does full scan on PK index, with cost = 1

    When I run explain plan for my table:
    select count(*) from tmp_orders;
    it does table full scan, with cost = 3

    As I said, tables are identical with 105 rows in each, there is unique index in each table on same column.

    I've been trying to make it using index on my table for hours. It's driving me crazy!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    yes, I used:

    EXEC DBMS_STATS.gather_table_stats(<schema name>, <table name>);
    EXEC DBMS_STATS.gather_index_stats(<schema name>, <index name>);

    should I used something else to compute statistics?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> connect oe/oe
    Connected.
    SQL> grant select on orders to user1;
    
    Grant succeeded.
    
    SQL> connect user1/user1
    Connected.
    SQL> create table orders as select * from oe.orders;
    
    Table created.
    
    SQL> create unique index orders_pk on orders(order_id);
    
    Index created.
    
    SQL> exec dbms_stats.gather_schema_stats('USER1');
    
    PL/SQL procedure successfully completed.
    
    SQL> set autotrace on explain
    
    SQL> select count(*) from oe.orders;
    
      COUNT(*)
    ----------
           105
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2738315105
    
    ---------------------------------------------------------------------
    | Id  | Operation	 | Name     | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------
    |   0 | SELECT STATEMENT |	    |	  1 |	  1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |	    |	  1 |		 |	    |
    |   2 |   INDEX FULL SCAN| ORDER_PK |	105 |	  1   (0)| 00:00:01 |
    ---------------------------------------------------------------------
    
    SQL> select count(*) from orders;
    
      COUNT(*)
    ----------
           105
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 630573765
    
    ---------------------------------------------------------------------
    | Id  | Operation	   | Name   | Rows  | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |	    |	  1 |	  3   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |	    |	  1 |		 |	    |
    |   2 |   TABLE ACCESS FULL| ORDERS |	105 |	  3   (0)| 00:00:01 |
    ---------------------------------------------------------------------
    
    
    SQL> alter table orders modify (order_id not null);
    
    Table altered.
    
    SQL> select count(*) from orders;
    
      COUNT(*)
    ----------
           105
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3585151106
    
    ----------------------------------------------------------------------
    | Id  | Operation	 | Name      | Rows  | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------
    |   0 | SELECT STATEMENT |	     |	   1 |	   1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |	     |	   1 |		  |	     |
    |   2 |   INDEX FULL SCAN| ORDERS_PK |	 105 |	   1   (0)| 00:00:01 |
    ----------------------------------------------------------------------
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    thanks a lot!

Posting Permissions

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