Home » RDBMS Server » Performance Tuning » Missing Index but strange column (Oracle 10.2.0.1, Sun Solaris 10)
Missing Index but strange column [message #447088] Thu, 11 March 2010 21:49 Go to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear all!

I've an strange example. I have one table

logvnp@VNP> select count(1)
  2  from ccs_hcm.bangphieutra_022010;

  COUNT(1)
----------
     88845


Describe table
Describing ccs_hcm.bangphieutra_022010....

NAME                            Null?     Type
------------------------------- --------- -----
PHIEU_ID                        NOT NULL  NUMBER(12,0)
SOPHIEU                         NOT NULL  NUMBER(12,0)
LOAITIEN_ID                     NOT NULL  NUMBER(2,0)
MA_TN                           NOT NULL  VARCHAR2(30)
MA_KH                           NOT NULL  VARCHAR2(30)
NGANHANG_ID                               NUMBER(12,0)
MA_BC                           NOT NULL  VARCHAR2(30)
LANIN                           NOT NULL  NUMBER(2,0)
NGAYBUUCUC                      NOT NULL  DATE
NOTON                           NOT NULL  NUMBER(2,0)
GACHTUMAY                       NOT NULL  VARCHAR2(60)
CHUYENVUNG                      NOT NULL  NUMBER(2,0)
TINHCHAT                        NOT NULL  NUMBER(1,0)
HTTT_ID                         NOT NULL  NUMBER(3,0)
NGUOIGACH                       NOT NULL  VARCHAR2(60)
NGAYNGANHANG                              DATE
QUYDOI                          NOT NULL  NUMBER(2,0)
SERI                                      VARCHAR2(14)
QUYEN                                     NUMBER(8,0)
SOSERI                                    NUMBER(10,0)
GOMHOADON                                 VARCHAR2(30)  <<====
MA_KN                                     VARCHAR2(20)
GHICHU                                    VARCHAR2(500)
CHUNGTU                                   VARCHAR2(140)
NGAY_TT                         NOT NULL  DATE
NGAY_THUC                       NOT NULL  DATE
LUOTTHANHTOAN                             NUMBER(8,0)
MAQUAY                                    VARCHAR2(20)


The last analyzed time
-- Table
logvnp@VNP> select owner,table_name,
  2  degree, to_char(last_analyzed,'dd-mm-yyyy hh24:mi:ss') last_analyzed
  3  from dba_tables
  4  where owner='CCS_HCM'
  5  and table_name='BANGPHIEUTRA_022010';

OWNER      TABLE_NAME           DEGREE   LAST_ANALYZED
---------- -------------------- -------- --------------------
CCS_HCM    BANGPHIEUTRA_022010           12-03-2010 10:44:56
                                 1



-- Index
logvnp@VNP> select owner,table_name,degree,
  2  index_name,to_char(last_analyzed,'dd-mm-yyyy hh24:mi:ss') last_analyzed
  3  from dba_indexes
  4  where owner='CCS_HCM'
  5  and table_name='BANGPHIEUTRA_022010';

OWNER      TABLE_NAME      DEGREE   INDEX_NAME         LAST_ANALYZED
---------- --------------- -------- ------------------ --------------------
CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:00
           2010                     22010_QD

CCS_HCM    BANGPHIEUTRA_02 1        INDX_GHD_BPT022010 12-03-2010 10:45:00
           2010

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BPT_CHUNGTU_02 12-03-2010 10:45:00
           2010                     2010

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BPT_TINHCHAT_0 12-03-2010 10:45:00
           2010                     22010

CCS_HCM    BANGPHIEUTRA_02 1        PK_BPT_PHIEUID_022 12-03-2010 10:45:01
           2010                     010

CCS_HCM    BANGPHIEUTRA_02 1        UK_BPT_PHIEU_02201 12-03-2010 10:45:01
           2010                     0

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:01
           2010                     22010_HTTT

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:02
           2010                     22010_LTID

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:03
           2010                     22010_MABC

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:03
           2010                     22010_MAKH

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:04
           2010                     22010_MATN

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:04
           2010                     22010_NH

CCS_HCM    BANGPHIEUTRA_02 1        IDX_BANGPHIEUTRA_0 12-03-2010 10:45:05
           2010                     22010_NG


13 rows selected.

logvnp@VNP>


Well, I ran one simple statement:


logvnp@VNP> set autotrace traceonly explain
logvnp@VNP> select * from ccs_hcm.bangphieutra_022010
  2  where gomhoadon='HANOI';

Execution Plan
----------------------------------------------------------
Plan hash value: 3996778795

--------------------------------------------------------------------------------
---------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| T
ime     |
--------------------------------------------------------------------------------
---------
|   0 | SELECT STATEMENT  |                     |  1239 |   165K|   411   (3)| 0
0:00:05 |
|*  1 |  TABLE ACCESS FULL| BANGPHIEUTRA_022010 |  1239 |   165K|   411   (3)| 0
0:00:05 |
--------------------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("GOMHOADON"='HANOI')

logvnp@VNP>


FTS, 1239 rows accessed (!?)
Why? of-course, the missing index article describes some way to lost the index. And now, I collect the statistic of this table

logvnp@VNP> begin
  2  dbms_stats.gather_table_stats(
  3  ownname=>'CCS_HCM',
  4  tabname=>'BANGPHIEUTRA_022010',
  5  estimate_percent=>100,
  6  method_opt=>'for all indexed columns size auto',
  7  degree=>1,
  8  cascade=>true);
  9  end;
 10  /

PL/SQL procedure successfully completed.



And I re-execute this simply statement
logvnp@VNP> select * from ccs_hcm.bangphieutra_022010
  2  where gomhoadon='ATM1286';

Execution Plan
----------------------------------------------------------
Plan hash value: 3819049844

--------------------------------------------------------------------------------
-------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost
 (%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT            |                     |     7 |   959 |
4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BANGPHIEUTRA_022010 |     7 |   959 |
4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_GHD_BPT022010  |     7 |       |
1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GOMHOADON"='ATM1286')



Well, it - Oracle used my index, and I tried to get the value of 'HANOI':

logvnp@VNP> select * from ccs_hcm.bangphieutra_022010
  2  where gomhoadon='HANOI';

Execution Plan
----------------------------------------------------------
Plan hash value: 3996778795

--------------------------------------------------------------------------------
---------
| Id  | Operation         | Name                | Rows  | Bytes | Cost (%CPU)| T
ime     |
--------------------------------------------------------------------------------
---------
|   0 | SELECT STATEMENT  |                     |  1239 |   165K|   411   (3)| 0
0:00:05 |
|*  1 |  TABLE ACCESS FULL| BANGPHIEUTRA_022010 |  1239 |   165K|   411   (3)| 0
0:00:05 |
--------------------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("GOMHOADON"='HANOI')

logvnp@VNP> select * from ccs_hcm.bangphieutra_022010
  2  where gomhoadon='10BA';

Execution Plan
----------------------------------------------------------
Plan hash value: 3819049844

--------------------------------------------------------------------------------
-------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost
 (%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT            |                     |     7 |   959 |
4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BANGPHIEUTRA_022010 |     7 |   959 |
4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_GHD_BPT022010  |     7 |       |
1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GOMHOADON"='10BA')


Shocked I've not ever the strange column like that, tried it again with other value :

logvnp@VNP> select * from ccs_hcm.bangphieutra_022010
  2  where gomhoadon='KIENGIANG';

Execution Plan
----------------------------------------------------------
Plan hash value: 3819049844

--------------------------------------------------------------------------------
-------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost
 (%CPU)| Time     |
--------------------------------------------------------------------------------
-------------------
|   0 | SELECT STATEMENT            |                     |     7 |   959 |
4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BANGPHIEUTRA_022010 |     7 |   959 |
4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_GHD_BPT022010  |     7 |       |
1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GOMHOADON"='KIENGIANG')

logvnp@VNP>


Wish you take me to clear!

Thank you very much!
Re: Missing Index but strange column [message #447203 is a reply to message #447088] Fri, 12 March 2010 05:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows are there that match HANOI? Is it lots? If there are many more rows that match HANOI than the other values you tried, then Oracle is right to do a FTS.

If there are really not many (say <1%) of HANOI rows, then Oracle just THINKS that there will be many.

When you gather statistics, Oracle captures HISTOGRAMS on your columns. This Histogram may say something like:
ALPHA -> CHARLIE 1000 rows
DELTA -> HAND 1000 rows
HANNIBAL -> HANSON 1000 rows
INDIA -> QUEBEC 1000 rows
ROMEO -> ZULU 1000 rows

In this histogram, we can see a concentration of rows around the HANOI value. Oracle cannot know for sure how many HANOI rows there will be, but it knows there are an awful lot of rows in a narrow range of values around HANOI, so it figures (wrongly?) HANOI could be one of the skewed values and performs a full scan.

If HANOI really does have very few matching rows, then you can try asking Oracle to gather statistics on that column with b\more buckets in the histogram.

Ross Leishman
Re: Missing Index but strange column [message #449644 is a reply to message #447088] Wed, 31 March 2010 03:29 Go to previous messageGo to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Well, thank you, rleishman! I've another example

logvnp@VNP> desc ccs_common.thuho_022010
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
--------------------
 PHIEU_ID                                              NOT NULL NUMBER(8)
 SOSERI                                                         NUMBER(12)
 LOAITIEN_ID                                           NOT NULL NUMBER(2)
 HTTT_ID                                               NOT NULL NUMBER(3)
 MA_KH                                                          VARCHAR2(30)
 MA_TB                                                 NOT NULL VARCHAR2(30)
 TENKH                                                 NOT NULL VARCHAR2(384)
 DIACHI                                                         VARCHAR2(384)
 MS_THUE                                                        VARCHAR2(60)
 SOTIEN                                                NOT NULL NUMBER(16)
 NGAYTHU                                               NOT NULL DATE
 SO_CT                                                          VARCHAR2(20)
 CHUKYNO                                               NOT NULL VARCHAR2(10)
 MA_TN                                                 NOT NULL VARCHAR2(100)
 MAQUAY                                                         VARCHAR2(15)
 LANIN                                                          NUMBER(2)
 TRANGTHAI_ID                                          NOT NULL NUMBER(2)
 TINHTHU_ID                                            NOT NULL NUMBER(2)
 TINH_ID                                               NOT NULL NUMBER(2)
 NGUOIGACH                                                      VARCHAR2(20)
 NGAY_TT                                               NOT NULL DATE
 GHICHU                                                         VARCHAR2(500)
 NGUOITH                                                        VARCHAR2(90)
 NGAYTH                                                         DATE
 TIENGACH                                                       NUMBER

logvnp@VNP> set autotrace traceonly explain
logvnp@VNP> select * from ccs_common.thuho_022010
  2  where tinh_id=28;

Execution Plan
----------------------------------------------------------
Plan hash value: 51462031

--------------------------------------------------------------------------------
--
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT  |              | 20211 |  4894K|   210   (2)| 00:00:03
 |
|*  1 |  TABLE ACCESS FULL| THUHO_022010 | 20211 |  4894K|   210   (2)| 00:00:03
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TINH_ID"=28)

logvnp@VNP> set autotrace off
logvnp@VNP> select count(tinh_id)
  2  from ccs_common.thuho_022010
  3  where tinh_id=28;

COUNT(TINH_ID)
--------------
         22290

logvnp@VNP> set autotrace traceonly explain
logvnp@VNP> select tinh_id
  2  from ccs_common.thuho_022010
  3  where tinh_id=28;

Execution Plan
----------------------------------------------------------
Plan hash value: 3257805513

--------------------------------------------------------------------------------
--------------
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CP
U)| Time     |
--------------------------------------------------------------------------------
--------------
|   0 | SELECT STATEMENT     |                       | 20211 | 60633 |    19   (
6)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_THUHO_022010_TINH | 20211 | 60633 |    19   (
6)| 00:00:01 |
--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TINH_ID"=28)


May you explain to me?
Thank you!
Re: Missing Index but strange column [message #449650 is a reply to message #449644] Wed, 31 March 2010 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's fairly easy. The two queries are:
select *
from ccs_common.thuho_022010
where tinh_id=28;


select tinh_id
from ccs_common.thuho_022010
where tinh_id=28;


The condition tinh_id=28 matches about 25% of the table.

The first query needs to fetch the entire row, and as such a large percentage of the table needs to be returned, it simply does a FTS and reads everything.

The second query only returns the column tinh_id, and this query can be solved by only looking at the index, as all the columns that the query needs are contained in the index. It still does a Full Scan of the index, as so many rows are needed, but it doesn't need to look at the table.
Re: Missing Index but strange column [message #449651 is a reply to message #447088] Wed, 31 March 2010 04:07 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
All the data needed to answer the 2nd query is in the index. So it treats the index like a table.
Any select where all the columns referenced (in the select and where clauses) are in a single index will almost certainly just use that index to get the answer.

For the 1st, because you told it to select all columns it has to go to the table to get the data. Obviously it has decided it would be more efficient to skip the index lookup and go straight to the table.

[Updated on: Wed, 31 March 2010 04:07]

Report message to a moderator

Previous Topic: Full Table Scan Instead of Index Usage
Next Topic: Difference in Cardinality, Cost, Temp Space
Goto Forum:
  


Current Time: Sun Sep 25 19:41:43 CDT 2016

Total time taken to generate the page: 0.12925 seconds