QUESTION: Select <PK> from <TABLE> - index scan or table scan??

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • BD
    #1

    QUESTION: Select <PK> from <TABLE> - index scan or table scan??

    Hi, all.

    My background is more Oracle than db2. My skills at SQL tuning are
    quite limited.

    I'm running 8.2 on Windows.

    I'm tasked with some SQL optimization, and am doing some explain plans
    on various queries.

    The cost of one particular part of a query struck me as a bit high, so
    I did some digging.

    I now have 2 tables, each of which has a single-column (non-composite)
    clustered PK index on it.

    For one of the tables, SELECT <PKFROM <TABLEresults in an index
    scan.

    For another, SELECT <PKFROM <TABLEresults in a full table scan.

    I'm stumped as to why the optimizer would call for a full table scan
    when I'm selecting only the value that is in the PK, and therefore the
    PK index. Why would it not call for an index scan of the primary key
    index?

    I just ran stats, and did a reorg of the tables.

    Can someone suggest why this might be happening?

    Cheers and thanks,

    BD
    Tags: None
    • Mark A
      #2
      Re: QUESTION: Select &lt;PK&gt; from &lt;TABLE&gt ; - index scan or table scan??

      "BD" <robert.drea@gm ail.comwrote in message
      news:2a2f5cea-9eff-43d0-91d4-a0ac7993908c@h1 1g2000prf.googl egroups.com...
      Hi, all.
      >
      My background is more Oracle than db2. My skills at SQL tuning are
      quite limited.
      >
      I'm running 8.2 on Windows.
      >
      I'm tasked with some SQL optimization, and am doing some explain plans
      on various queries.
      >
      The cost of one particular part of a query struck me as a bit high, so
      I did some digging.
      >
      I now have 2 tables, each of which has a single-column (non-composite)
      clustered PK index on it.
      >
      For one of the tables, SELECT <PKFROM <TABLEresults in an index
      scan.
      >
      For another, SELECT <PKFROM <TABLEresults in a full table scan.
      >
      I'm stumped as to why the optimizer would call for a full table scan
      when I'm selecting only the value that is in the PK, and therefore the
      PK index. Why would it not call for an index scan of the primary key
      index?
      >
      I just ran stats, and did a reorg of the tables.
      >
      Can someone suggest why this might be happening?
      >
      Cheers and thanks,
      >
      BD
      If the number of rows in the table is small enough, it actually might be
      faster to scan the entire table for that one column than to scan the index.


        Comment

        • BD
          #3
          Re: QUESTION: Select &lt;PK&gt; from &lt;TABLE&gt ; - index scan or table scan??

          On Feb 13, 4:00 pm, "Mark A" <nob...@nowhere .comwrote:
          "BD" <robert.d...@gm ail.comwrote in message
          >
          news:2a2f5cea-9eff-43d0-91d4-a0ac7993908c@h1 1g2000prf.googl egroups.com...
          >
          >
          >
          >
          >
          Hi, all.
          >
          My background is more Oracle than db2. My skills at SQL tuning are
          quite limited.
          >
          I'm running 8.2 on Windows.
          >
          I'm tasked with some SQL optimization, and am doing some explain plans
          on various queries.
          >
          The cost of one particular part of a query struck me as a bit high, so
          I did some digging.
          >
          I now have 2 tables, each of which has a single-column (non-composite)
          clustered PK index on it.
          >
          For one of the tables, SELECT <PKFROM <TABLEresults in an index
          scan.
          >
          For another, SELECT <PKFROM <TABLEresults in a full table scan.
          >
          I'm stumped as to why the optimizer would call for a full table scan
          when I'm selecting only the value that is in the PK, and therefore the
          PK index. Why would it not call for an index scan of the primary key
          index?
          >
          I just ran stats, and did a reorg of the tables.
          >
          Can someone suggest why this might be happening?
          >
          Cheers and thanks,
          >
          BD
          >
          If the number of rows in the table is small enough, it actually might be
          faster to scan the entire table for that one column than to scan the index..- Hide quoted text -
          >
          - Show quoted text -
          Yeah, a co-worker suggested that to me just now. I'll test that by
          stripping all but a few rows out of a table that results in an index
          scan on a PK select, and see if the behavior changes.

          Thanks!

            Comment

            Previous template Next
            Working...