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  |
trantuananh24hg
Messages: 744
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')
 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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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 |
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 |
cookiemonster
Messages: 13922
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 |
|
|
|
Goto Forum:
Current Time: Sun May 12 10:54:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
