Home » RDBMS Server » Performance Tuning » Query tuning (oracle 9i)
| Query tuning [message #524824] |
Tue, 27 September 2011 02:24  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The below query is taking more time (>20 mins) to give the complete output. But the subquery has taken only 28 seconds.
the main query given 1st 15 records in 12 mins, after that it has taken another 7 mins to give next 15 records. Again the cursor is still blinking.
I did not meet this kind of behaviour. is it because of the problem in query or any other?
SQL> select attribute_identifier,attribute_value from fs_attributes where order_identifier
2 in
3 (select order_identifier from fs_header where party_identifier like'%BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11')
4 and attribute_id_number in (260337,5611316,107839,10,257722,257540,783,257608,999999092,696797,257632,33,
5 789,156,161,192,231,260421,205);
SQL> select order_identifier from fs_header where party_identifier like'%BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11';
ORDER_IDENTIFIER
------------------------------
561131
566958
565693
572511
572529-V1
573524
572529
559870
550792
541383
563156
558023
570330
561352
555017
554183
554800
558161
557929
559593
557651
557923
561526
558368
562863
562611
565351
560809
563031
29 rows selected.
Elapsed: 00:00:28.57
SQL> set timing on;
SQL> select attribute_identifier,attribute_value from fs_attributes where order_identifier
2 in
3 (select order_identifier from fs_header where party_identifier like'%BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11')
4 and attribute_id_number in (260337,5611316,107839,10,257722,257540,783,257608,999999092,696797,257632,33,
5 789,156,161,192,231,260421,205);
ATTRIBUTE_IDENTIFIER
------------------------------------------------------------
ATTRIBUTE_VALUE
-----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
BILLING_STATUS
Billed
CUSTOMER_NAME
TAKE 2 INTERACTIVE EUROPE LTD
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ---------------------------------- ------------------------------
ATTRIBUTES_ATTRIB_ID ATTRIBUTE_IDENTIFIER FS_ATTRIBUTES
ATTRIBUTES_ATTRIB_VAL ATTRIBUTE_VALUE FS_ATTRIBUTES
ATTRIBUTES_ATTRIB_VAL_UPPER ATTRIBUTE_VALUE_UPPER FS_ATTRIBUTES
ATTRIBUTES_ORDER_ID ORDER_IDENTIFIER FS_ATTRIBUTES
ATTRIBUTES_ATTRIB_CHANGE_DATE ATTRIBUTE_CHANGE_DATE FS_ATTRIBUTES
ATTRIBUTES_ATTRIB_ID_NUM ATTRIBUTE_ID_NUMBER FS_ATTRIBUTES
PK_FS_ATTRIBUTES ORDER_IDENTIFIER FS_ATTRIBUTES
PK_FS_ATTRIBUTES ATTRIBUTE_IDENTIFIER FS_ATTRIBUTES
HEADER_CUSTOMER_NAME CUSTOMER_NAME FS_HEADER
HEADER_FTIP_NUMBER FTIP_NUMBER FS_HEADER
HEADER_CUST_NAME_UPPER CUSTOMER_NAME_UPPER FS_HEADER
HEADER_PRODUCT_NAME_UPPER PRODUCT_NAME_UPPER FS_HEADER
HEADER_PRODUCT_NAME PRODUCT_NAME FS_HEADER
HEADER_UNIQUE_SERVICE_ID UNIQUE_SERVICE_IDENTIFIER FS_HEADER
HEADER_CSS_ORDER_REF CSS_ORDER_REF FS_HEADER
HEADER_ORDER_STATUS ORDER_STATUS FS_HEADER
HEADER_SERVICE_STATUS SERVICE_STATUS FS_HEADER
HEADER_WAN_IP_ADDR WAN_IP_ADDRESS FS_HEADER
HEADER_ESC_REF ESCALATION_REF FS_HEADER
HEADER_CSAC CSAC FS_HEADER
HEADER_FTIP_SERVICE_STATUS FTIP_NUMBER FS_HEADER
HEADER_FTIP_SERVICE_STATUS SERVICE_STATUS FS_HEADER
HEADER_CSS_RAISE_DATE CSS_RAISE_DATE FS_HEADER
HEADER_ORDER_START_DATE ORDER_START_DATE FS_HEADER
HEADER_ACCESS_SPEED ACCESS_SPEED FS_HEADER
HEADER_BILLING_RAISE_DATE BILLING_RAISE_DATE FS_HEADER
HEADER_RAG_KCI_STATUS RAG_KCI_STATUS FS_HEADER
HEADER_KCI_NEXT_STAGE KCI_NEXT_STAGE FS_HEADER
HEADER_STATUS STATUS FS_HEADER
I_BMFS_HEADER_INT_ELEMENT INTERNATIONAL_ELEMENT FS_HEADER
I_BMFS_HEADER_INT_SITE INTERNATIONAL_SITE FS_HEADER
HEADER_CIRCUIT CIRCUIT FS_HEADER
HEADER_SITE SITE FS_HEADER
HEADER_LAN_IP_ADDR LAN_IP_ADDRESS FS_HEADER
HEADER_COMMISSION_ROUTER_DATE COMMISSION_ROUTER_DATE FS_HEADER
HEADER_PVC_ID PVC_ID FS_HEADER
HEADER_PROJECT_ID PROJECT_ID FS_HEADER
ADSL_IDX ADSL_PRODUCT FS_HEADER
HEADER_WITH_USERID WITH_USERID FS_HEADER
HEADER_JOB_ENVELOPE JOB_ENVELOPE FS_HEADER
HEADER_PLATFORM PLATFORM FS_HEADER
HEADER_KCI_NEXT_STAGE_DATE KCI_NEXT_STAGE_DATE FS_HEADER
PK_FS_HEADER_ORDER_ID ORDER_IDENTIFIER FS_HEADER
HEADER_PARTY_ID PARTY_IDENTIFIER FS_HEADER
HEADER_AX_NUMBER AX_NUMBER FS_HEADER
HEADER_SITE_COMPLETED_DATE SITE_COMPLETED_DATE FS_HEADER
HEADER_ORDER_ENTERED_BY ORDER_ENTERED_BY FS_HEADER
HEADER_SME_MAJOR SME_MAJOR FS_HEADER
HEADER_ESC_DATE ESCALATION_CDD FS_HEADER
HEADER_RAG_STATUS RAG_STATUS FS_HEADER
HEADER_SITE_REF SITE_REFERENCE_NUMBER FS_HEADER
HEADER_BEARER_ID BEARER_ID FS_HEADER
HEADER_CUST_REQD_DATE CUSTOMER_REQUIRED_DATE FS_HEADER
HEADER_CONTR_DEL_DATE CONTRACTUAL_DELIVERY_DATE FS_HEADER
HEADER_ORDER_NATURE ORDER_NATURE FS_HEADER
ORDTYPE_IDX ORDER_TYPE FS_HEADER
HEADER_COSMOSS_ORDERS COSMOSS_ORDERS FS_HEADER
HEADER_WITH_TEAM WITH_TEAM FS_HEADER
SQL> select table_name,last_analyzed,num_rows from dba_tables where table_name in ('FS_HEADER','FS_ATTRIBUTES');
TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
FS_ATTRIBUTES 24-SEP-11 35951462
FS_HEADER 26-SEP-11 859636
SQL> explain plan for
2 select attribute_identifier,attribute_value from fs_attributes where order_identifier
3 in
4 (select order_identifier from fs_header where party_identifier like'%BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11')
5 and attribute_id_number in (260337,5611316,107839,10,257722,257540,783,257608,999999092,696797,257632,33,
6 789,156,161,192,231,260421,205);
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 19 |
| 1 | TABLE ACCESS BY INDEX ROWID | FS_ATTRIBUTES | 1 | 56 | 11 |
| 2 | NESTED LOOPS | | 1 | 87 | 19 |
| 3 | TABLE ACCESS BY INDEX ROWID| FS_HEADER | 1 | 31 | 8 |
| 4 | INDEX RANGE SCAN | HEADER_SITE_COMPLETED_DATE | 19 | | 3 |
| 5 | INDEX RANGE SCAN | PK_FS_ATTRIBUTES | 43 | | 4 |
---------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
13 rows selected.
SQL> explain plan for
2 select order_identifier from fs_header where party_identifier like'%BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11'
3 /
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| FS_HEADER | 1 | 31 | 8 |
| 2 | INDEX RANGE SCAN | HEADER_SITE_COMPLETED_DATE | 19 | | 3 |
-------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
10 rows selected.
CREATE TABLE "FAST_USER"."FS_ATTRIBUTES"
( "ATTRIBUTE_IDENTIFIER" VARCHAR2(60) NOT NULL ENABLE,
"ATTRIBUTE_VALUE" VARCHAR2(255),
"ATTRIBUTE_CHANGE_DATE" DATE,
"ATTRIBUTE_ID_NUMBER" NUMBER,
"ORDER_IDENTIFIER" VARCHAR2(30),
"ATTRIBUTE_VALUE_UPPER" VARCHAR2(255),
"REPLICATE" NUMBER(1,0) DEFAULT 1,
CONSTRAINT "PK_FS_ATTRIBUTES" PRIMARY KEY ("ORDER_IDENTIFIER", "ATTRIBUTE_IDEN
TIFIER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 1511653376 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FASTSERVICEIDHEADATT" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1300258816 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FASTSERVICEHEADATT"
CREATE TABLE "FAST_USER"."FS_HEADER"
( "UNIQUE_SERVICE_IDENTIFIER" NUMBER,
"PARTY_IDENTIFIER" VARCHAR2(20),
"ORDER_IDENTIFIER" VARCHAR2(30) NOT NULL ENABLE,
"ORDER_START_DATE" DATE,
"CHASE_TIME" DATE,
"CHASE_REASON" VARCHAR2(200),
"CUSTOMER_UPDATE_TIME" DATE,
"CUSTOMER_UPDATE_INTERVAL" NUMBER,
"GRID_NOTE" VARCHAR2(200),
"ACCESS_CLASS" VARCHAR2(50),
"ACCESS_SPEED" VARCHAR2(50),
"BEARER_ID" VARCHAR2(50),
"CONTRACTUAL_DELIVERY_DATE" DATE,
"CUSTOMER_NAME" VARCHAR2(255),
"CUSTOMER_REQUIRED_DATE" DATE,
"PRODUCT_NAME" VARCHAR2(255),
"FTIP_NUMBER" VARCHAR2(50),
"CSS_ORDER_REF" VARCHAR2(2000),
"COSMOSS_ORDERS" VARCHAR2(2000),
"CUSTOMER_NAME_UPPER" VARCHAR2(255),
"PRODUCT_NAME_UPPER" VARCHAR2(255),
"ORDER_STATUS" VARCHAR2(100),
"SERVICE_STATUS" VARCHAR2(100),
"USER_DEFINED_1" VARCHAR2(100),
"USER_DEFINED_2" VARCHAR2(50),
"USER_DEFINED_3" VARCHAR2(50),
"USER_DEFINED_4" VARCHAR2(50),
"WITH_TEAM" VARCHAR2(100),
"WITH_USERID" VARCHAR2(50),
"CUSTOMER_UPDATE_REASON" VARCHAR2(200),
"SME_MAJOR" VARCHAR2(20),
"ORDER_DESCRIPTION" VARCHAR2(255),
"UPDATED_DATETIME" DATE,
"UPDATED_USERID" VARCHAR2(50),
"AX_NUMBER" VARCHAR2(50),
"COSMOSS_CRD" DATE,
"CSS_CRD" DATE,
"ISDN_CRD" DATE,
"FIND_ASSIGN_DATE" DATE,
"VALIDATION_DATE" DATE,
"SITE" VARCHAR2(200),
"COSMOSS_CDD" DATE,
"COSMOSS_COMPLETED_DATE" DATE,
"ISDN_JOB_NO" VARCHAR2(50),
"ISDN_COMPLETED_DATE" DATE,
"CSS_JOB_NO" VARCHAR2(50),
"CSS_COMPLETED_DATE" DATE,
"SITE_COMPLETED_DATE" DATE,
"ORDER_NOTES" VARCHAR2(4000),
"COSMOSS_RAISE_DATE" DATE,
"CSS_RAISE_DATE" DATE,
"ACCEPT_REJECT" VARCHAR2(30),
"STATUS" VARCHAR2(30),
"PLATFORM" VARCHAR2(30),
"PRICE" NUMBER,
"CONTRACT_TERMS" VARCHAR2(50),
"COMMISSION_ROUTER_DATE" DATE,
"MSP_ACCESS_PROVIDED_DATE" DATE,
"ACCOUNT_MANAGER" VARCHAR2(50),
"LAN_IP_ADDRESS" VARCHAR2(100),
"ESCALATION_CDD" DATE,
"ESCALATION_REF" VARCHAR2(60),
"ESCALATION_USERID" VARCHAR2(50),
"BILLING_RAISE_DATE" DATE,
"RENTAL" NUMBER,
"SITE_CDD" DATE,
"ORDER_CREATED_DATE" DATE,
"RAG_STATUS" VARCHAR2(250),
"AX_SIGNATURE_DATE" DATE,
"CONCORD_USERNAME" VARCHAR2(255),
"ISDN_COMMISSION_DATE" DATE,
"RAG_KCI_STATUS" VARCHAR2(250),
"KCI_NEXT_STAGE" VARCHAR2(3),
"KCI_NEXT_STAGE_DATE" DATE,
"PVC_ID" VARCHAR2(30),
"CSAC" VARCHAR2(30),
"REJECTED_DATE" DATE,
"PROJECT_ID" VARCHAR2(20),
"FIRST_PROMISE_DATE" DATE,
"RECEIVED_DATE" DATE,
"WAN_IP_ADDRESS" VARCHAR2(100),
"ROUTER_APPT_DATE" DATE,
"KCI_UPDATE_TYPE" VARCHAR2(20),
"ATTACHED_FILES" VARCHAR2(500),
"ACC_MANAGER_EMAIL" VARCHAR2(100),
"CUSTOMER_EMAIL" VARCHAR2(100),
"SITE_REFERENCE_NUMBER" VARCHAR2(30),
"LOAD_LEVEL" NUMBER,
"JEOPARDY_NEXT_STAGE" VARCHAR2(3),
"JEOPARDY_NEXT_STAGE_DATE" DATE,
"REPLICATE" NUMBER(1,0) DEFAULT 1,
"REPLICATE_ACTION" VARCHAR2(5),
"HEADER_KEY" NUMBER(7,0),
"GPMS_ROLE" VARCHAR2(30),
"PROJECT_NEXT_UPDATE" DATE,
"PROJECT_NO_OF_UPDATES" NUMBER(3,0),
"ORDER_NATURE" VARCHAR2(15),
"ISDN_RAISED_DATE" DATE,
"VSERVE_DEPLOYED_DATE" DATE,
"EV_VISIT" DATE,
"ORDER_COMPLETE" CHAR(1),
"JOB_ENVELOPE" VARCHAR2(100),
"PRI_LOG_PORTS" VARCHAR2(50),
"PRI_NTE_IP_ADDR" VARCHAR2(50),
"ORDER_ENTERED_BY" VARCHAR2(50),
"PRICING_TYPE" VARCHAR2(15),
"COSMOSS_PROJECTS" VARCHAR2(200),
"SEV_REQUIRED" CHAR(1),
"SCORE_EVENT_PLAN_SENT" CHAR(1),
"CSS_PROJECTS" VARCHAR2(200),
"ORDER_TYPE" CHAR(1),
"KCI_CUST_CONTACT_NAME" VARCHAR2(100),
"KCI_CUST_TELEPHONE" VARCHAR2(100),
"INTERNATIONAL_SITE" VARCHAR2(5) DEFAULT 'NO',
"INTERNATIONAL_AX_NUMBER" VARCHAR2(50),
"INTERNATIONAL_ELEMENT" VARCHAR2(5) DEFAULT 'NO',
"INT_SITES_EXIST" VARCHAR2(5) DEFAULT 'NO',
"SHARED_BEARER" VARCHAR2(5) DEFAULT 'NO',
"CUST_AGRD_DATE" DATE,
"APPT_DATE" DATE,
"APPT_AM_PM" VARCHAR2(2),
"SNMP_TRAP" CHAR(1),
"CNH_SET" CHAR(1),
"TARGET_COMPLETION_DATE" DATE,
"CNH_DATE" DATE,
"CE_LAN_IP_ADDRESS" VARCHAR2(15),
"CIRCUIT" VARCHAR2(30),
"PE_ROUTER_IP" VARCHAR2(30),
"BGP4_AS_NO" VARCHAR2(10),
"PRODUCT_SUB_TYPE" VARCHAR2(20),
"CUSTOMER_PASSWORD" VARCHAR2(20),
"RAG_AUTO_KCI_FAILED" VARCHAR2(100),
"ADSL_PRODUCT" VARCHAR2(20),
"MASTER_AGREEMENT_CALL_DATE" DATE,
"TECH_SERVICES_INTERESTED_TEAM" VARCHAR2(100),
"NUMBER_OF_REJECTIONS" NUMBER DEFAULT 0,
"ACCEPTED_DATE" DATE,
"INSTALL_MANAGEMENT_TEAM" VARCHAR2(100),
"ORDER_UPDATE_DATE" DATE,
"ORDER_PLACED_BY" VARCHAR2(255),
"ORDER_PLACED_BY_EMAIL" VARCHAR2(100),
"SALES_ACCOUNT_CODE" VARCHAR2(50),
"ORDER_ORIGINATOR_NAME" VARCHAR2(255),
"ORDER_ORIGINATOR_EMAIL" VARCHAR2(100),
"MANAGED_UNMANAGED" CHAR(1),
"EXPEDIO_E_REF" VARCHAR2(50),
CONSTRAINT "FS_HEADER_U" UNIQUE ("HEADER_KEY") DISABLE,
CONSTRAINT "PK_FS_HEADER_ORDER_ID" PRIMARY KEY ("ORDER_IDENTIFIER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 12582912 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FASTSERVICEIDHEADATT" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 15 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 156753920 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "FASTSERVICEHEADATT"
Elapsed: 00:00:18.12 |
|
|
|
|
|
| Re: Query tuning [message #524917 is a reply to message #524853] |
Tue, 27 September 2011 07:13  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
select table_name,last_analyzed,num_rows from dba_tables where table_name in ('FS_HEADER','FS_ATTRIBUTES');
TABLE_NAME LAST_ANAL NUM_ROWS
------------------------------ --------- ----------
FS_ATTRIBUTES 24-SEP-11 35951462
FS_HEADER 26-SEP-11 859636
I tested all the possibilities. the query is not taking the index created on the party_identifier column, since we used 'like' operator.
even I tried with hints, still it is not taking the index.
explain plan for
select /*+ INDEX(HEADER_PARTY_ID) */ order_identifier from fs_header where party_identifier like 'BTNET%' and site_completed_date between'01-Sep-11' and '01-Sep-11';
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| FS_HEADER | 1 | 31 | 8 |
| 2 | INDEX RANGE SCAN | HEADER_SITE_COMPLETED_DATE | 19 | | 3 |
------------------------------------------------------------------------------------------- |
|
|
|
| Re: Query tuning [message #524918 is a reply to message #524917] |
Tue, 27 September 2011 07:37 |
 |
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
|
Member |
|
|
Yes, indeed, that is main your problem. Your subquery clogs up the main query due to this too. Might I suggest rewriting into a join? That way you pass everything only once.
select attribute_identifier,attribute_value
from fs_attributes a
, fs_header b
where a.order_identifier = b.order_identifier
and b.party_identifier like'%BTNET%'
and b.site_completed_date between'01-Sep-11' and '01-Sep-11')
and b.attribute_id_number in (260337,5611316,107839,10,257722,257540,783,257608,999999092,696797,257632,33,
789,156,161,192,231,260421,205);
See how that goes... but the problem is the like, and the date between... those stop index-usage pretty well.
[Updated on: Tue, 27 September 2011 07:39] Report message to a moderator |
|
|
|
| Re: Query tuning [message #524968 is a reply to message #524918] |
Wed, 28 September 2011 02:24 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The explain plan for your query is given below. As you said, 'like' is not using the index. but the 'between' clause using the index. there is no anyway to sort out this proplem?
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 19 |
| 1 | TABLE ACCESS BY INDEX ROWID | FS_ATTRIBUTES | 1 | 56 | 11 |
| 2 | NESTED LOOPS | | 1 | 87 | 19 |
| 3 | TABLE ACCESS BY INDEX ROWID| FS_HEADER | 1 | 31 | 8 |
| 4 | INDEX RANGE SCAN | HEADER_SITE_COMPLETED_DATE | 19 | | 3 |
| 5 | INDEX RANGE SCAN | PK_FS_ATTRIBUTES | 43 | | 4 |
---------------------------------------------------------------------------------------------
Index details:
==============
INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ ---------------------------------- ------------------------------
ATTRIBUTES_ORDER_ID ORDER_IDENTIFIER FS_ATTRIBUTES
ATTRIBUTES_ATTRIB_ID_NUM ATTRIBUTE_ID_NUMBER FS_ATTRIBUTES
PK_FS_ATTRIBUTES ORDER_IDENTIFIER FS_ATTRIBUTES
PK_FS_HEADER_ORDER_ID ORDER_IDENTIFIER FS_HEADER
HEADER_PARTY_ID PARTY_IDENTIFIER FS_HEADER
HEADER_SITE_COMPLETED_DATE SITE_COMPLETED_DATE FS_HEADER
to remove the 'like' operator, I am planning to give the below values directly, since we have only 4 values that matches '%BTNET%'
BTNET4V2
BTNETMS
BTNETMSC
BTNETSVC
Though I put these values directly as below, it is not using the index. is there any way to rewrite the query to use party_identifier column index?
select attribute_identifier,attribute_value
from fs_attributes a
, fs_header b
where a.order_identifier = b.order_identifier
and b.party_identifier ('BTNET4V2','BTNETMS','BTNETMSC','BTNETSVC')
and b.site_completed_date between'01-Sep-11' and '01-Sep-11'
and a.attribute_id_number in
(260337,5611316,107839,10,257722,257540,783,257608,999999092,696797,257632,33,789,156,161,192,231,260421,205);
|
|
|
|
| Re: Query tuning [message #524972 is a reply to message #524968] |
Wed, 28 September 2011 02:37 |
|
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
|
Member |
|
|
The query above can't have worked, because
and b.party_identifier ('BTNET4V2','BTNETMS','BTNETMSC','BTNETSVC')
should be
and b.party_identifier in('BTNET4V2','BTNETMS','BTNETMSC','BTNETSVC')
By the way, why are you using "between" for the dates, since you seem to be focussing on one date?
[Updated on: Wed, 28 September 2011 02:45] Report message to a moderator |
|
|
|
| Re: Query tuning [message #524979 is a reply to message #524972] |
Wed, 28 September 2011 03:25 |
 |
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
|
Senior Member |
|
|
Beware that you should use the TO_DATE function instead of between '01-Sep-11' and '01-Sep-11' because between '01-Sep-11' goes haywire with other nls_settings (like the MM/DD/YYYY format).
I would focus on all the fields required from fs_header (site_completed,party_identifier,order_identifier) and those of attributes (attribute_id_number,attribute_identifier,attribute_value). Depending on datadistribution the tableaccess by index rowid could be avoided by creating covering indexes (all required fields in the proper order)
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 16:23:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
