12 Replies Latest reply: Mar 9, 2008 8:44 PM by 627138 RSS

    Index hint does not work

    627138
      Hi everyone

      I have a table named group_customer that has 270000 number of rows.
      I wanna performe an index full scan on the table instead of a full table scan although that a full table sacn is likely to be faster than an index full scan.

      So, i wrote down a following sql statement
      SELECT /*+ INDEX(t XPKGROUP_CUSTOMER)*/t.* FROM group_customer t;

      I wanna perform an index full scan using index XPKGROUP_CUSTOMER that is an unique index on the table.

      But the explain plan showed that the CBO still choose a full table scan instead of an index full scan i want.

      Was the hint failed?
      Thank u very much for your help.
        • 1. Re: Index hint does not work
          247514
          You are selecting * from table without where clause, it just doesn't make sense to use index.
          • 2. Re: Index hint does not work
            Jaffy
            Hi,

            This may be helpful to you.

            http://jonathanlewis.wordpress.com/2007/06/17/hints-again/

            Regards

            Jafar
            • 3. Re: Index hint does not work
              627138
              hi yingkuan

              We are able to use a index to perform the same functionaliy like a full table sacn without where clause.
              • 4. Re: Index hint does not work
                247514
                First of all, this is not possible to be an Index Unique Scans.
                By definition, index unique scan returns, at most, a single rowid.
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i44986

                Secondly, you are doing select * from the table, the PK index only has PK column, how do you expect index scan faster than full table scan?
                And AGAIN by definition, Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query.
                • 5. Re: Index hint does not work
                  155651
                  Create an index that contains all required columns including the select list.
                  If there is a GROUP BY clause then these columns should be NOT NULL.

                  Mohan
                  http://www.myoracleguide.com/
                  • 6. Re: Index hint does not work
                    627138
                    hi yingkuan.
                    First of all, this is not possible to be an Index
                    Unique Scans.
                    By definition, index unique scan returns, at most, a
                    single rowid.
                    http://download.oracle.com/docs/cd/B19306_01/server.10
                    2/b14211/optimops.htm#i44986
                    I am not expecing a Index Unique Scan,but a Index Full Scan.I expect a execution
                    plan like this
                    SELECT STATEMENT, GOAL = CHOOSE
                    TABLE ACCESS BY INDEX ROWID     
                    INDEX FULL SCAN
                    And i do konw that in this case a Full Table Scan is faster than the execution plan above.
                    what i wanna to do is to check if the index hint works,not to find the best access path.

                    Thank u
                         




                    I
                    • 7. Re: Index hint does not work
                      247514
                      ok, in that case, you might want to check the syntax first,
                      SELECT /*+ INDEX(t XPKGROUP_CUSTOMER)*/t.* FROM group_customer t;
                      make sure there's space in there,
                      SELECT /*+ INDEX(t XPKGROUP_CUSTOMER) */ t.* FROM group_customer t;
                      • 8. Re: Index hint does not work
                        627138
                        hi yingkuan

                        I am sure there is the space.The problem is still there.
                        And, there is an another problem related to this problem.
                        See the following sql statement please.

                        SELECT t.customer_id FROM group_customer t;

                        Here comes the explain plan:
                        SELECT STATEMENT, GOAL = CHOOSE     
                        TABLE ACCESS FULL

                        The index XPKGROUP_CUSTOMER as i mentioned and used before is an unique index on the column cusotmer_id of the table group_customer
                        It's just odd that oracle not use a Unique Index Scan to serve this query but a Full Table Scan.

                        I totolly have no idea about this case.
                        • 9. Re: Index hint does not work
                          247514
                          Did you collect statistics on index and table?
                          Also make sure the index name is correct.

                          In addition, as mentioned, it won't be unique index scan if you don't specify where clause like

                          where PrimaryKey_Column=xxx


                          It certainly work in my test
                          TEST@rman> select /*+ INDEX(myt PK_T) */ * from myt;

                          85 rows selected.


                          Execution Plan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=85 Bytes=14
                                    45)

                             1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MYT' (TABLE) (Cost=2 Car
                                    d=85 Bytes=1445)

                             2    1     INDEX (FULL SCAN) OF 'PK_T' (INDEX (UNIQUE)) (Cost=1 Car
                                    d=85)
                          =====
                          unique scan case,
                          TEST@rman> select * from myt where object_id=11921
                            2  /

                          OBJECT_ID
                          ----------
                          OBJECT_NAME
                          --------------------------------------------------------------------------------
                               11921
                          MYTTT



                          Execution Plan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=17)
                             1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MYT' (TABLE) (Cost=1 Car
                                    d=1 Bytes=17)

                             2    1     INDEX (UNIQUE SCAN) OF 'PK_T' (INDEX (UNIQUE)) (Cost=0 C
                                    ard=1)
                          • 10. Re: Index hint does not work
                            Jonathan Lewis
                            The index XPKGROUP_CUSTOMER as i mentioned and used
                            before is an unique index on the column cusotmer_id
                            of the table group_customer
                            The name XPKGROUP_CUSTOMER looks like it's an index created to support a primary key constraint - but is this really the case, or is it just a unique index.

                            Unless the customer_id column is declared to be NOT NULL, which would happen automatically in the simplest cases of declaring the primary key constraint, the optimizer cannot use this index to return the result set as there may be some rows in the table which do not appear in the index.

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk
                            • 11. Re: Index hint does not work
                              155651
                              SQL> create table mytab as select * from user_objects;

                              Table created.

                              SQL> create unique index mytab_id on mytab(object_id);

                              Index created.

                              SQL> set autotrace ON
                              SQL> select object_id from mytab;

                              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                              ---------------------------------------------------------------------------
                              | 0 | SELECT STATEMENT | | 745 | 9685 | 5 (0)| 00:00:01 |
                              | 1 | TABLE ACCESS FULL| MYTAB | 745 | 9685 | 5 (0)| 00:00:01 |
                              ---------------------------------------------------------------------------

                              SQL> analyze table mytab compute statistics;

                              Table analyzed.

                              SQL> select object_id from mytab;

                              ---------------------------------------------------------------------------
                              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                              ---------------------------------------------------------------------------
                              | 0 | SELECT STATEMENT | | 745 | 2980 | 5 (0)| 00:00:01 |
                              | 1 | TABLE ACCESS FULL| MYTAB | 745 | 2980 | 5 (0)| 00:00:01 |
                              ---------------------------------------------------------------------------

                              SQL> alter table mytab modify(object_id not null);

                              Table altered.

                              SQL> select object_id from mytab;

                              ---------------------------------------------------------------------------------
                              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                              ---------------------------------------------------------------------------------
                              | 0 | SELECT STATEMENT | | 745 | 2980 | 2 (0)| 00:00:01 |
                              | 1 | INDEX FAST FULL SCAN| MYTAB_ID | 745 | 2980 | 2 (0)| 00:00:01 |
                              ---------------------------------------------------------------------------------

                              SQL> drop table mytab;

                              Table dropped.
                              • 12. Re: Index hint does not work
                                627138
                                hi Jonathan Lewis ,thank you for your reply.
                                "Unless the customer_id column is declared to be NOT NULL, which would happen automatically in the simplest cases of declaring the primary key constraint, the optimizer cannot use this index to return the result set as there may be some rows in the table which do not appear in the index."

                                That's the key to my problem.
                                Unfortunately XPKGROUP_CUSTOMER was mistakenly set to NULLABLE.That's the reason Oracle cannot use this ndex to perfrom the query that require
                                returnning all rows in a table.

                                The problem has been solved after setting XPKGROUP_CUSTOMER to be NOT NULL.

                                Thanks all.