| Why my function based did not use? [message #664141] |
Wed, 05 July 2017 05:03  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear,
I have a partitioned table, named as TBL_MO_LOG, the information as following:
1. Table description
smsgwadm@SMPPGW> desc smsgw.tbl_mo_log
Name Null? Type
-------------------------------------------------------------------------------- -------- ------------------------------------------------------
ID NOT NULL NUMBER
MSISDN VARCHAR2(20)
SERVICE_ID VARCHAR2(50)
COMMAND_CODE VARCHAR2(50)
DATE_CREATE DATE
SENT_TIME DATE
INFO VARCHAR2(2000)
RETRY_SEND NUMBER
CP_NAME VARCHAR2(200)
SERVICE_NAME VARCHAR2(200)
PACKAGE_CODE VARCHAR2(200)
PACKAGE_PRICE NUMBER
MO_URL_FW VARCHAR2(500)
MO_URL_DESTINATION VARCHAR2(500)
ID is PK column.
2. Rows
smsgwadm@SMPPGW> select count(*) from smsgw.tbl_mo_log;
3. Local partitioned function based index was created
smsgwadm@SMPPGW> Create index smsgw.indx_molog_senttime on smsgw.tbl_mo_log(to_char(sent_time)) local(
2 PARTITION MO_LOG_P201703 tablespace SMPPGWINDX,
3 PARTITION MO_LOG_P201704 tablespace SMPPGWINDX,
4 PARTITION MO_LOG_P201705 tablespace SMPPGWINDX,
5 PARTITION MO_LOG_P201706 tablespace SMPPGWINDX,
6 PARTITION MO_LOG_P201707 tablespace SMPPGWINDX,
7 PARTITION MO_LOG_P201708 tablespace SMPPGWINDX,
8 PARTITION MO_LOG_P201709 tablespace SMPPGWINDX,
9 PARTITION MO_LOG_P201710 tablespace SMPPGWINDX,
10 PARTITION MO_LOG_P201711 tablespace SMPPGWINDX,
11 PARTITION MO_LOG_P201712 tablespace SMPPGWINDX)
12 /
Index created.
4. Gather table, index statistics
smsgwadm@SMPPGW> EXEC DBMS_STATS.gather_index_stats('SMSGW','INDX_MOLOG_SENTTIME');
smsgwadm@SMPPGW> begin
2 dbms_stats.gather_table_stats (
3 ownname => 'SMSGW',
4 tabname => 'TBL_MO_LOG',
5 method_opt => 'for all columns size skewonly for columns (to_char(sent_time))'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
5. Verify query_* parameter
smsgwadm@SMPPGW> show parameter query
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
6. Generate explain plan
smsgwadm@SMPPGW> select * from smsgw.tbl_mo_log where sent_time=to_date('03-07-2017 03:03:03','dd-mm-yyyy hh24:mi:ss');
Execution Plan
----------------------------------------------------------
Plan hash value: 2349454200
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 6975 (1)| 00:01:24 | | |
| 1 | PARTITION RANGE ALL| | 1 | 76 | 6975 (1)| 00:01:24 | 1 | 10 |
|* 2 | TABLE ACCESS FULL | TBL_MO_LOG | 1 | 76 | 6975 (1)| 00:01:24 | 1 | 10 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SENT_TIME"=TO_DATE(' 2017-07-03 03:03:03', 'syyyy-mm-dd hh24:mi:ss'))
Well, TAF appeared,
7. How many rows in sent_time?
smsgwadm@SMPPGW> select count(sent_time) from smsgw.tbl_mo_log;
COUNT(SENT_TIME)
----------------
2510291
8. Did any null rows exists in sent_time?
smsgwadm@SMPPGW> select * from smsgw.tbl_mo_log where sent_time is null;
no rows selected
9. Force to use index hint
smsgwadm@SMPPGW> select /*+ INDEX smsgw.indx_molog_senttime*/ id, msisdn, service_id from smsgw.tbl_mo_log where to_char(sent_time,'dd-mm-yyyy hh24:mi:ss')='03-03-2017 03:03:03'
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2349454200
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25092 | 808K| 7012 (1)| 00:01:25 | | |
| 1 | PARTITION RANGE ALL| | 25092 | 808K| 7012 (1)| 00:01:25 | 1 | 10 |
|* 2 | TABLE ACCESS FULL | TBL_MO_LOG | 25092 | 808K| 7012 (1)| 00:01:25 | 1 | 10 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("SENT_TIME"),'dd-mm-yyyy hh24:mi:ss')='03-03-2017
03:03:03')
What did I forget anything, may you clarify more?
Thank you!
[Updated on: Wed, 05 July 2017 05:05] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Why my function based did not use? [message #664146 is a reply to message #664144] |
Wed, 05 July 2017 05:17  |
Deep Chakraborty
Messages: 10
Registered: June 2006
|
Junior Member |
|
|
|
As the index on date/time column, so, do we really need histogram? Moreover, I have seen in most of the cases gathering histogram causes problem- so, unless we are absolutely clear about our data, no point of gathering histogram. Without histogram, even skew data works not badly.
|
|
|
|
| Re: Why my function based did not use? [message #664147 is a reply to message #664146] |
Wed, 05 July 2017 05:19 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have never seen histograms cause problems. Completely the reverse: the more information you can give the optimizer, the better it will function. Do you have an example?
Note that your example destroys ALL the histograms, not just one.
|
|
|
|
|
|
| Re: Why my function based did not use? [message #664164 is a reply to message #664142] |
Wed, 05 July 2017 20:37 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
John Watson wrote on Wed, 05 July 2017 10:05Your index needs to be on the column itself, not a function based index on to_char of the column.
This is my mistake, I recreated the index and it work well.
smsgwadm@SMPPGW> set linesize 150
smsgwadm@SMPPGW> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2744727136
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 23 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 76 | 23 (0)| 00:00:01 | 1 | 10 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TBL_MO_LOG | 1 | 76 | 23 (0)| 00:00:01 | 1 | 10 |
|* 3 | INDEX RANGE SCAN | INDX_MOLOG_SENTTIME | 1 | | 21 (0)| 00:00:01 | 1 | 10 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SENT_TIME"=TO_DATE(' 2017-07-03 03:03:03', 'syyyy-mm-dd hh24:mi:ss'))
Thank you very much.
@biswachk: Thank you, I knew the stats of method option, however, I deny to destroy all of histogram, it is not SYS time story, it is the data working when meet many many complex queries.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
