9 Replies Latest reply: Jun 2, 2012 5:33 AM by Etbin RSS

    Index On varchar2 column

    Theleo
      Hi all,

      I have table A with 686,028 records

      A has column C ( varchar2(5) ) I tried building bitmap index and b-tree indexes, on trial and error basis.


      Null -      665720
      'ABC'      20308

      Now my question is my query gives faster results with using index , when i search on 'ABC' , but if i use 'is null' then still query using Index but result is after
      540 sec.

      Even I tried Updating Null value with 'Other' but not much use.

      I am running on Oracle 11g R2.

      Please suggest me method, when i use either null / Other i need to get faster result set.

      Edited by: Theleo on Jun 1, 2012 11:40 AM
        • 1. Re: Index On varchar2 column
          rp0428
          >
          if i use 'is null' then still query using Index
          >
          Not with a b-tree index it isn't - those indexes don't store NULL values.

          And if the 665720 values are all set to the string 'Other' Oracle won't use the index either. If it is, which I doubt, it is because the statistics for the table and index are not current.

          A bitmap index would get used in either case if all you are doing is COUNT(c) since NULLs are indexed and the index would be faster than a full table scan.

          Post the actual queries you are using.
          • 2. Re: Index On varchar2 column
            Theleo
            select
            T967778.PER_NAME_MONTH as c1,t967778.month_name,
            count( T778770.ACCT_AR_ID ) as c2,
            T967778.ROW_WID as c3
            from
            sch.Account T778394 ,
            sch.FACT T778770 ,
            sch.GroupT968444 ,
            sch.Date T967778
            where T778394.ACCT_AR_ID = T778770.ACCT_AR_ID
            and T778770.MSRMNT_PRD_MNTH = T967778.ROW_WID
            and T778770.ART_PRTFOL_GRP_ID = T968444.CL_ID
            and T778770.ART_ACTIVE_FLAG = 'Y'
            and (<b>T778394.SRC_BANK_CODE</b>) ='Other' -- 'ABC'--'Other'
            and case when T968444.CL_NAME = 'MSP' then 'Construction'
            when T968444.CL_NAME = 'HLS' then 'Non-Prime'
            when T968444.CL_NAME in ( 'MSP1', 'SPS') then 'Mortgage'
            when T968444.CL_NAME in ('FDR', 'ILS/ACLS')
            then 'BHE' else T968444.CL_NAME end = 'Mortgage' and T778770.MSRMNT_PRD_MNTH is not null
            group by T967778.ROW_WID, T967778.PER_NAME_MONTH, t967778.month_name
            order by c3


            This is The sql i am using, ( table names are modified) I ran statistics for all the tables involved with cascade = 'TRUE'


            again, If I am using ABC result is in 3 / 4seconds, If I use Other or Is null it is going for around 540 secs. ( sometimes i can not even wait to see results )
            • 3. Re: Index On varchar2 column
              sb92075
              post results from SQL below
              SELECT src_bank_code, 
                     Count(*) 
              FROM   sch.account T778394 
              GROUP  BY src_bank_code; 
              • 4. Re: Index On varchar2 column
                Theleo
                following is the result for SQL
                SELECT src_bank_code,
                Count(*)
                FROM sch.account T778394
                GROUP BY src_bank_code;



                Other     665720
                ABC     20308


                Thanks !!
                • 5. Re: Index On varchar2 column
                  rp0428
                  Can you post the plan and the index definitions?
                  • 6. Re: Index On varchar2 column
                    sb92075
                    Theleo wrote:
                    following is the result for SQL
                    SELECT src_bank_code,
                    Count(*)
                    FROM sch.account T778394
                    GROUP BY src_bank_code;



                    Other     665720
                    ABC     20308


                    Thanks !!
                    13:03:36 SQL> select 20308/665720 from dual;
                    
                    20308/665720
                    ------------
                      .030505318
                    
                    13:04:48 SQL> 
                    it is inefficient to use index to access table for SRC_BANK_CODE = 'OTHER'
                    • 7. Re: Index On varchar2 column
                      Theleo
                      may I know the reason for Inefficiency?

                      I red in online Documentation that indexes are good if we have to query data in less than 15 % of total rows? is that true ?


                      Thanks !!

                      Edited by: Theleo on Jun 1, 2012 1:49 PM
                      • 8. Re: Index On varchar2 column
                        rp0428
                        >
                        may I know the reason for Inefficiency?
                        >
                        You need to decide what question you want help with. Your original post said your query was using an index when you were trying to select NULL values and also when you tried to select values of 'Other' which, as sb just showed you, comprise 97% of the records.

                        If you want help with your original question then post the information I ask for: the query that queries NULL values, the corresponding explain plan showing the b-tree index is being used in the query and the DDL that shows the indexes that exist and the columns that make them up.
                        • 9. Re: Index On varchar2 column
                          Etbin
                          Maybe: (extreme) predicate pushing sometimes turns out useful
                          select T967778.PER_NAME_MONTH as c1,
                                 t967778.month_name, 
                                 count( T778770.ACCT_AR_ID ) as c2,
                                 T967778.ROW_WID as c3
                            from (select ACCT_AR_ID
                                    from sch.Account
                                   where T778394.SRC_BANK_CODE = 'Other' /* 'ABC' or 'Other' */
                                 ) T778394 ,
                                 (select ACCT_AR_ID,ART_PRTFOL_GRP_ID,MSRMNT_PRD_MNTH
                                    from sch.FACT 
                                   where ART_ACTIVE_FLAG = 'Y'
                                     and MSRMNT_PRD_MNTH is not null
                                 ) T778770 ,
                                 (select CL_ID
                                    from sch.Group
                                   where CL_NAME in ('MSP1','SPS') /* 'Mortgage' */
                                 ) T968444 ,
                                 sch.Date T967778 
                           where T778394.ACCT_AR_ID = T778770.ACCT_AR_ID 
                             and T778770.MSRMNT_PRD_MNTH = T967778.ROW_WID 
                             and T778770.ART_PRTFOL_GRP_ID = T968444.CL_ID 
                           group by T967778.ROW_WID, T967778.PER_NAME_MONTH, 
                                    t967778.month_name
                           order by c3 
                          Regards

                          Etbin