1 2 Previous Next 16 Replies Latest reply on Aug 1, 2013 7:50 AM by myueongyun

    table full scan

    myueongyun

      hi,


      I'm in the proc developer in korea.

       

      Please excuse me for your trouble because I'm not very good at English.

       

      I am trying TO post question about oracle index

       

      retrived sql is one TABLE whithout USING join, db optimizer MODE IS CBO(ALL_ROWS).

       

      [optimizer inforamtion]

      ------------------------------------ ----------- ------------------------------

      optimizer_capture_sql_plan_baselines boolean     FALSE

      optimizer_dynamic_sampling           integer     2

      optimizer_features_enable            string      11.2.0.3

      optimizer_index_caching              integer     0

      optimizer_index_cost_adj             integer     100

      optimizer_mode                       string      ALL_ROWS

      optimizer_secure_view_merging        boolean     TRUE

      optimizer_use_invisible_indexes      boolean     FALSE

      optimizer_use_pending_statistics     boolean     FALSE

      optimizer_use_sql_plan_baselines     boolean     TRUE

       

       

       

       

      [Table Information]

      OWNER               |TABLE_NAME                    |TABLESPACE_NAME               |CLUSTER_NAME                  |IOT_NAME                      |STATUS  |  PCT_FREE|  PCT_USED| INI_TRANS| MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE| FREELISTS|FREELIST_GROUPS|LOG|B|  NUM_ROWS|    BLOCKS|EMPTY_BLOCKS| AVG_SPACE| CHAIN_CNT|AVG_ROW_LEN|AVG_SPACE_FREELIST_BLOCKS|NUM_FREELIST_BLOCKS|DEGREE              |INSTANCES           |CACHE     |TABLE_LO|SAMPLE_SIZE|LAST_ANALYZE|PAR|IOT_TYPE    |T|S|NES|BUFFER_|FLASH_C|CELL_FL|ROW_MOVE|GLO|USE|DURATION       |SKIP_COR|MON|CLUSTER_OWNER                 |DEPENDEN|COMPRESS|COMPRESS_FOR|DRO|REA|SEG|RESULT_

      --------------------|------------------------------|------------------------------|------------------------------|------------------------------|--------|----------|----------|----------|----------|--------------|-----------|-----------|-----------|------------|----------|---------------|---|-|----------|----------|------------|----------|----------|-----------|-------------------------|-------------------|--------------------|--------------------|----------|--------|-----------|------------|---|------------|-|-|---|-------|-------|-------|--------|---|---|---------------|--------|---|------------------------------|--------|--------|------------|---|---|---|-------

      OPS$PKTIBG2         |FTIOIR                        |KTI2_USER                     |                              |                              |VALID   |        10|          |         1|       255|        163840|    1048576|          1| 2147483645|            |          |               |NO |N|    380680|     19356|           0|         0|         0|       1538|                        0|                  0|         1          |         1          |    N     |ENABLED |      38068|23-JUN-13   |NO |            |N|N|NO |DEFAULT|DEFAULT|DEFAULT|DISABLED|YES|NO |               |DISABLED|YES|                              |DISABLED|DISABLED|            |NO |NO |YES|DEFAULT 

       

       

       

      [INDEX Information] 

      OWNER               |INDEX_NAME                    |INDEX_TYPE                 |TABLE_OWNER         |TABLE_NAME                    |TABLE|UNIQUENES|COMPRESS|PREFIX_LENGTH|TABLESPACE_NAME               | INI_TRANS| MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|PCT_THRESHOLD|INCLUDE_COLUMN| FREELISTS|FREELIST_GROUPS|  PCT_FREE|LOG|    BLEVEL|LEAF_BLOCKS|DISTINCT_KEYS|AVG_LEAF_BLOCKS_PER_KEY|AVG_DATA_BLOCKS_PER_KEY|CLUSTERING_FACTOR|STATUS  |  NUM_ROWS|SAMPLE_SIZE|LAST_ANALYZE|DEGREE                                  |INSTANCES                               |PAR|T|G|S|BUFFER_|FLASH_C|CELL_FL|USE|DURATION       |PCT_DIRECT_ACCESS|ITYP_OWNER                    |ITYP_NAME                     |PARAMETERS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |GLO|DOMIDX_STATU|DOMIDX|FUNCIDX_|JOI|IOT|DRO|VISIBILIT|DOMIDX_MANAGEM|SEG

      --------------------|------------------------------|---------------------------|--------------------|------------------------------|-----|---------|--------|-------------|------------------------------|----------|----------|--------------|-----------|-----------|-----------|------------|-------------|--------------|----------|---------------|----------|---|----------|-----------|-------------|-----------------------|-----------------------|-----------------|--------|----------|-----------|------------|----------------------------------------|----------------------------------------|---|-|-|-|-------|-------|-------|---|---------------|-----------------|------------------------------|------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---|------------|------|--------|---|---|---|---------|--------------|---

      OPS$PKTIBG2         |IDX_FTIOIR_02                 |NORMAL                     |OPS$PKTIBG2         |FTIOIR                        |TABLE|NONUNIQUE|DISABLED|             |KTI2_IND                      |         2|       255|        163840|    1048576|          1| 2147483645|            |             |              |          |               |        10|NO |         1|        710|        53421|                      1|                      3|           193171|VALID   |    376591|     376591|23-JUN-13   |1                                       |1                                       |NO |N|N|N|DEFAULT|DEFAULT|DEFAULT|NO |               |                 |                              |                              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |YES|            |      |        |NO |NO |NO |VISIBLE  |              |YES

       

       

      [INDEX column information]

      INDEX_OWNER         |INDEX_NAME                    |TABLE_OWNER         |TABLE_NAME                    |COLUMN_NAME                   |COLUMN_POSITION|COLUMN_LENGTH|CHAR_LENGTH|DESC

      --------------------|------------------------------|--------------------|------------------------------|------------------------------|---------------|-------------|-----------|----

      OPS$PKTIBG2         |IDX_FTIOIR_02                 |OPS$PKTIBG2         |FTIOIR                        |REMT_PROC_DATE                |              1|            8|          8|ASC

      OPS$PKTIBG2         |IDX_FTIOIR_02                 |OPS$PKTIBG2         |FTIOIR                        |BRN_NO                        |              2|            3|          3|ASC

      OPS$PKTIBG2         |IDX_FTIOIR_02                 |OPS$PKTIBG2         |FTIOIR                        |INST_NO                       |              3|            3|          3|ASC

       

      when I retrieve a table data by using between clause as bellow,  the explain result was processed by index range scan.

       

       

      SQL> SELECT

        2    count(*)

        3  FROM FTIOIR

        4  WHERE REMT_PROC_DATE BETWEEN '20120616' AND '20130715';

       

       

       

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 3219804720

       

       

      -----------------------------------------------------------------------------------

      | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

      -----------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT  |               |     1 |     9 |     4   (0)| 00:00:01 |

      |   1 |  SORT AGGREGATE   |               |     1 |     9 |            |          |

      |*  2 |   INDEX RANGE SCAN| IDX_FTIOIR_02 |   520 |  4680 |     4   (0)| 00:00:01 |

      -----------------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

       

         2 - access("REMT_PROC_DATE">='20120616' AND

                    "REMT_PROC_DATE"<='20130715')

       

       

       

       

      Statistics

      ----------------------------------------------------------

                0  recursive calls

                0  db block gets

                5  consistent gets

                0  physical reads

                0  redo size

              527  bytes sent via SQL*Net to client

              520  bytes received via SQL*Net from client

                2  SQL*Net roundtrips to/from client

                0  sorts (memory)

                0  sorts (disk)

                1  rows processed

       

       

      SQL>


      but in case that I add filtering inforamtion as bellow, the explain result was processed by TABLE FULL SCAN.

      SQL> SELECT

        2    count(*)

        3  FROM FTIOIR

        4  WHERE REMT_PROC_DATE BETWEEN '20120616' AND '20130715'

        5      AND REMT_TYPE = 'O';

       

       

      Elapsed: 00:00:01.17

       

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 48264840

       

       

      -----------------------------------------------------------------------------

      | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

      -----------------------------------------------------------------------------

      |   0 | SELECT STATEMENT   |        |     1 |    11 |  9236   (4)| 00:02:47 |

      |   1 |  SORT AGGREGATE    |        |     1 |    11 |            |          |

      |*  2 |   TABLE ACCESS FULL| FTIOIR | 38388 |   412K|  9236   (4)| 00:02:47 |

      -----------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

       

         2 - filter("REMT_PROC_DATE">='20120616' AND "REMT_TYPE"='O' AND

                    "REMT_PROC_DATE"<='20130715')

       

       

       

       

      Statistics

      ----------------------------------------------------------

                1  recursive calls

                1  db block gets

            19616  consistent gets

            19611  physical reads

                0  redo size

              528  bytes sent via SQL*Net to client

              520  bytes received via SQL*Net from client

                2  SQL*Net roundtrips to/from client

                0  sorts (memory)

                0  sorts (disk)

                1  rows processed

       

      In case that Count(*) change '*', the explain result was processed by TABLE FULL SCAN.

       

       

      SQL> SELECT

        2  *

        3  FROM FTIOIR

        4  WHERE REMT_PROC_DATE BETWEEN '20120616' AND '20130715';

       

       

      78976 rows selected.

       

       

      Elapsed: 00:00:14.74

       

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 85254115

       

       

      ----------------------------------------------------------------------------

      | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

      ----------------------------------------------------------------------------

      |   0 | SELECT STATEMENT  |        | 76777 |   112M|  9259   (4)| 00:02:47 |

      |*  1 |  TABLE ACCESS FULL| FTIOIR | 76777 |   112M|  9259   (4)| 00:02:47 |

      ----------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

       

         1 - filter("REMT_PROC_DATE">='20120616' AND

                    "REMT_PROC_DATE"<='20130715')

       

       

       

       

      Statistics

      ----------------------------------------------------------

              213  recursive calls

                1  db block gets

            24757  consistent gets

            19611  physical reads

                0  redo size

         65754035  bytes sent via SQL*Net to client

            58435  bytes received via SQL*Net from client

             5267  SQL*Net roundtrips to/from client

                6  sorts (memory)

                0  sorts (disk)

            78976  rows processed

       

       

       

       

      I'd like to know the reson why bellow 2 sql query processed by TABLE full scan.

       

      Please give me an advice.

        • 1. Re: table full scan
          rp0428

          Read your own thread and ask yourself if YOU can make any sense out of that MESSY DATA that you posted.

           

          Edit your post and remove that garbage. You need to post formatted code and data.

           

          Also you need to post the table DDL (formatted).

           

          A count can use an index if there is one on a column that cannot have null. But when you want ALL of the data in the data why would Oracle use an index? That wouldn't make any sense. And when you put a condition in your query on a column that is NOT in the index Oracle has no choice but to scan the table.


          • 2. Re: table full scan
            SomeoneElse

            You posted 3 examples.

             

            In the first, Oracle was able to satisfy the query by using only the index.

             

            In the second, you added a predicate for a column that (apparently) is not indexed so the table needed to be accessed.  The optimizer believed it would be faster do to a full table scan.

             

            In the third, you're selecting a large percentage of the rows in the table.  In the first example, you were only selecting a count(*).  But here, you're doing a select * so the table needs to be accessed.  Similarly, the optimizer went for a full table scan.

             

            Based on the size of your table and results, all three examples are what I would expect from the optimizer.

             

            By the way, when working with date literals, you should always use TO_DATE:   BETWEEN to_date('20120616','YYYYMMDD') AND to_date('20130715','YYYYMMDD')

             

            Assuming your date column has a DATE type.

            • 3. Re: table full scan
              myueongyun

              hi. SomeoneElse,

               

               

              Is your name SomeoneElse, right?^^

               

               

              First, Thank you for your quick response and your help.

               

               

              By the way, the test server all retrieved by using index range scan.

               

               

              What are reasons for this?

               

               

              The table data is diffrent between the operating server and the test server.

               

               

              So Database Analysis information can be diffrent between the operating server and the test server.

               

               

              You may think that the Database Analysis information in the operating server have be a wrong information because the database analysis did not implement frequently.

               

               

              But the Database Analysis was performed at midnight in the operating server.

               

               

              ultimately, I want to know how to retrieve by using index range in first sql query.

               

               

              please prompt your quick response.

              • 4. Re: table full scan
                myueongyun

                Hi rp0428,

                 

                thank you very much for quick your reply and your effort!!!

                 

                Is the post fixed stye?

                 

                I'm sorry, I don't know what it is!!!.

                 

                The Index information does not exit Null and condition cluase  also does not exit NOT operator!!

                 

                thank you.

                • 5. Re: table full scan
                  SomeoneElse

                  > ultimately, I want to know how to retrive by index range in first sql query.

                   

                  Why?  Don't you want the fastest method?  In many cases that might be a full table scan.

                   

                  > plz prompt your reply.

                   

                  Boo!  There is no "urgent" in these forums.

                  • 6. Re: table full scan
                    myueongyun

                    the fastest method? What do you mean?

                     

                     

                    What is the  the fastest method?

                     

                     

                    oops. Don't tell me I study hard after buy the DB book?

                    • 7. Re: table full scan
                      SomeoneElse

                      > What is the  the fastest method?

                       

                      In most cases (certainly not all) the optimizer will select the best access method automatically based on your current statistics.  Maybe it will use a full table scan, maybe an index.

                       

                      Why do you want to force an index?

                      • 8. Re: table full scan
                        Etbin

                        Check Ask Tom &amp;quot;Tuning&amp;quot;

                        You'll find a nice recipe therein.

                         

                         

                        just repeat to yourself:

                         

                        a) full scans are not evil
                        b) indexes are not all goodness
                        c) goto a until you believe both a and b fully

                         

                         

                        Regards

                         

                        Etbin

                        • 9. Re: table full scan
                          myueongyun

                          SomeoneElse.

                          thank you for your answer.

                          I checked that your answers are all correct.

                          I feel to have to study hard about db!!

                           

                           

                          Can I ask you two more question?

                           

                           

                           

                          Q1) I want to know  the difference of two sql explain plan result as below.

                           

                           

                          Q2) I want to know that 'Rows ' values of explain plan is different.

                           

                           

                           

                           

                           

                           

                          SQL> SELECT 

                            2        count(*)

                            3      FROM FTIOIR

                            4  WHERE 1=1

                            5  --INST_NO  = '027'

                            6  --AND STA_TYPE = '1'

                            7  --  AND ID_NO = '5128106559   '

                            8  --  AND REMT_TYPE = 'O'

                            9    AND REMT_PROC_DATE BETWEEN '20121231' AND '20130101';

                           

                           

                           

                           

                          Elapsed: 00:00:00.03

                           

                           

                           

                           

                          Execution Plan

                          ----------------------------------------------------------

                          Plan hash value: 3219804720

                           

                           

                           

                           

                          -----------------------------------------------------------------------------------

                          | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

                          -----------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT  |               |     1 |     9 |   133   (4)| 00:00:03 |

                          |   1 |  SORT AGGREGATE   |               |     1 |     9 |            |          |

                          |*  2 |   INDEX RANGE SCAN| IDX_FTIOIR_02 | 68425 |   601K|   133   (4)| 00:00:03 |

                          -----------------------------------------------------------------------------------

                           

                           

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                           

                           

                           

                             2 - access("REMT_PROC_DATE">='20121231' AND

                                        "REMT_PROC_DATE"<='20130101')

                           

                           

                           

                           

                           

                           

                           

                           

                          Statistics

                          ----------------------------------------------------------

                                  210  recursive calls

                                    0  db block gets

                                   83  consistent gets

                                    2  physical reads

                                    0  redo size

                                  527  bytes sent via SQL*Net to client

                                  520  bytes received via SQL*Net from client

                                    2  SQL*Net roundtrips to/from client

                                    6  sorts (memory)

                                    0  sorts (disk)

                                    1  rows processed

                           

                           

                           

                           

                          SQL> SELECT 

                            2        count(*)

                            3      FROM FTIOIR

                            4  WHERE 1=1

                            5  --INST_NO  = '027'

                            6  --AND STA_TYPE = '1'

                            7  --  AND ID_NO = '5128106559   '

                            8  --  AND REMT_TYPE = 'O'

                            9    AND REMT_PROC_DATE BETWEEN '20121231' AND '20130101'

                          10    AND (REMT_PROC_DATE LIKE '2012%' OR REMT_PROC_DATE LIKE '2013%');

                           

                           

                           

                           

                          Elapsed: 00:00:00.06

                           

                           

                           

                           

                          Execution Plan

                          ----------------------------------------------------------

                          Plan hash value: 3219804720

                           

                           

                           

                           

                          -----------------------------------------------------------------------------------

                          | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

                          -----------------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT  |               |     1 |     9 |   137   (6)| 00:00:03 |

                          |   1 |  SORT AGGREGATE   |               |     1 |     9 |            |          |

                          |*  2 |   INDEX RANGE SCAN| IDX_FTIOIR_02 |   347 |  3123 |   137   (6)| 00:00:03 |

                          -----------------------------------------------------------------------------------

                           

                           

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                           

                           

                           

                             2 - access("REMT_PROC_DATE">='20121231' AND

                                        "REMT_PROC_DATE"<='20130101')

                                 filter("REMT_PROC_DATE" LIKE '2012%' OR "REMT_PROC_DATE" LIKE

                                        '2013%')

                           

                           

                           

                           

                           

                           

                           

                           

                          Statistics

                          ----------------------------------------------------------

                                  210  recursive calls

                                    0  db block gets

                                   83  consistent gets

                                    2  physical reads

                                    0  redo size

                                  527  bytes sent via SQL*Net to client

                                  520  bytes received via SQL*Net from client

                                    2  SQL*Net roundtrips to/from client

                                    6  sorts (memory)

                                    0  sorts (disk)

                                    1  rows processed

                          • 10. Re: table full scan
                            SomeoneElse

                            > Q1) I want to know  the difference of two sql explain plan result as below.

                             

                            Strictly speaking, they're identical.  Look at the PLAN HASH VALUE for each explain plan.  Same number.

                             

                            Plan hash value: 3219804720

                            Plan hash value: 3219804720

                             

                            Q2) I want to know that 'Rows ' values of explain plan is different.

                             

                            It's different because you have an extra AND clause in the second one. (not sure what you're asking)

                             

                            By the way, I'm very suspicious of the way you're referencing your date column.  What is the TYPE of REMT_PROC_DATE in your table?  If it's a date, you need to use TO_DATE on your literal values.  If it's varchar, that's an even bigger problem.

                            • 11. Re: table full scan
                              myueongyun
                              SomeoneElse,

                               

                              Q2)


                              The type is not both date and varchar.


                              The TYPE of REMT_PROC_DATE is char(8)!


                              What do you mean the "bigger problem"?


                              plz, let me explain with example?


                              • 12. Re: table full scan
                                Nikolay Savvinov

                                Hi,

                                 

                                the optimizer chooses between the index range scan and the full table scan based on percentage of rows it expects to satisfy the predicates (also known as predicate selectivity). If you suspect that the optimizer is taking a wrong decision, then you should investigate the selectivity. In your case the most likely reason why it could be wrong is if you store REMT_PROC_DATE as date. Another possibility is a problem with histogram (histogram present where not needed or the other way round).

                                 

                                Please post the following information:

                                 

                                REMT_PROC_DATE data type

                                REMT_PROC_DATE column stats (from DBA_TAB_COL_STATISTICS)

                                REMT_PROC_DATE approximate data distribution


                                Also, please find a way to format your original post to make it readable (e.g. by switching to the HTML mode and using <pre> tags to preserve formatting for code snippets and other parts of the post that are only legible with a fixed font).

                                 

                                Best regards,

                                Nikolay

                                • 13. Re: table full scan
                                  SomeoneElse

                                  > The TYPE of REMT_PROC_DATE is char(8)!

                                  > What do you mean the "bigger problem"?

                                   

                                  Storing dates as character strings is a dreadful error.

                                   

                                  They should be stored as DATE types.

                                   

                                  • 14. Re: table full scan
                                    myueongyun

                                    SomeoneElse

                                     

                                    Storing dates as character strings is a dreadful error.??


                                    The TYPE of REMT_PROC_DATE is char(8). --> As a result, TYPE of REMT_PROC_DATE is the char type.

                                    and the condition clause (BETWEEN '20121231' AND '20130101') is also the char type.

                                    Stored Table Data( data of REMT_PROC_DATE) value also the char type.


                                    They should be stored as DATE types.--> I'm sorry!!. I don't know what you mean.







                                    1 2 Previous Next