1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please answer- SQL

Discussion in 'SQL PL/SQL' started by venkateshguru, Dec 14, 2014.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi All,

    created the table by using the below script.

    CREATE TABLE TEST AS SELECT CASE WHEN ROWNUM BETWEEN 1 AND 10 THEN 100 ELSE 200 END A, LEVEL/3 B, SYSDATE+LEVEL S_BOOK_DATE
    FROM DUAL CONNECT BY LEVEL < 25000

    Ran the below statement with and with out index. SQL_ID is same but plan is different. How its possible?

    SELECT * FROM TEST WHERE A=100

    CREATE INDEX TEST_A ON TEST(A)

    Appreciate your response.

    Thanks,
    Venkatesh G
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Venkatesh,

    Oracle uses it's optimizer to determine (based on database statistics) which execution plan is best suited for the query.

    In your scenario, in the absence of any index on table TEST, there is no other way than to do a full table scan to get the rows. When an index is created on column "A", and you do a query using a condition on this column, it's quite understandable that the best route to obtain the data is by using the index on column A and in this case, I suppose you got an execution plan where the table access is by the index TEST_A.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Thank you rajen and Sergey Krasnoslobodtsev for the reply.

    As per my understanding, if cursor_sharing parameter is "EXACT" then it should use the same plan for both the queries. I ran the queries 2 times without index and it uses the same sql_id and plan. After creating the index it uses the same SQL ID but plan is different. Will analyse . Thank you.

    --Venkatesh G
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it won't because creating the index invalidated the previous cursor. This causes Oracle to hard parse the statement, generating an execution plan in the process. The 'new' plan is reused for subsequent runs of the same query. With adaptive cursor sharing Oracle can generate multiple plans for a given statement (when bind variables are used rather than string literals); depending on the bind value passed Oracle may decide that more data is being returned for a given bind value and run a full table scan rather than an index access path. You changed the running environment; you cannot expect Oracle to ignore new details and continue to use the same path if the optimizer decides that original plan is expensive and inefficient.
     
    venkateshguru likes this.