| Query not executing [message #65708] |
Sun, 12 December 2004 18:40  |
Supraja
Messages: 18
Registered: August 2004
|
Junior Member |
|
|
Hi,
I am joining 3 tables. Each have millions of records. When I am running that query, DB is not giving any response till 30 mins also.
I wrote it in optimized way. Still I don't know the reason.
Can I get any help on this please.
I am using ORacle 9i version
My query and tables are
SELECT
A.PRODUCT_CATEGORY,
C.PRODUCT_TYPE,
COUNT(DISTINCT C.CDM_POS_HEADER_ID) AS CDM_POS_HEADER_ID_COUNT
FROM
EDWD_PRODUCT_ITEM A,
EDWD_PRODUCT_TYPE_SUMMARY B,
EDWD_PRODUCT_TYPE_DETAIL_CKM C
WHERE
A.CDM_PROD_ID = B.CDM_PROD_ID AND
B.CDM_POS_HEADER_ID = C.CDM_POS_HEADER_ID AND
A.PRODUCT_CATEGORY = 'Hamburger'
GROUP BY A.PRODUCT_CATEGORY,C.PRODUCT_TYPE
Index created by me are
CDM_PROD_ID_IDX -- This is on created on table column cdm_prod_id
CDM_POS_HEADER_ID_IDX - This is created EDWD_PRODUCT_TYPE_DETAIL_CKM on cdm_pos_header_id column.
CDM_POS_HEADER_ID_INDEX -- This is creatde on EDWD_PRODUCT_TYPE_SUMMARY and column is cdm_pos_header_id.
CDM_PROD_ID_INDEX ---- This is creatde on EDWD_PRODUCT_TYPE_SUMMARY and column is cdm_prod_id
script for table creation are
EDWD_PRODUCT_TYPE_SUMMARY
(
CDM_POS_HEADER_ID NUMBER(15),
PRODUCT_TYPE VARCHAR2(255 BYTE),
TNX_DATE DATE,
CDM_PROD_ID NUMBER(15),
PROD_COUNT NUMBER,
BA_INDICATOR VARCHAR2(1 BYTE)
)
EDWD_PRODUCT_TYPE_DETAIL_CKM
(
CDM_POS_HEADER_ID NUMBER(15),
PRODUCT_TYPE VARCHAR2(30 BYTE),
PRODUCT_TYPE_NO NUMBER,
CDM_POS_TYPE_ID NUMBER(15),
CDM_STORE_ID NUMBER(15),
CDM_TIME_ID NUMBER(10),
CDM_TIME_DAY_PART_ID VARCHAR2(2 BYTE),
TNX_AMOUNT NUMBER(15,5)
)
EDWD_PRODUCT_ITEM
(
ID NUMBER(10) NOT NULL,
MAIN_CATEGORY VARCHAR2(255 BYTE),
MAIN_CATEGORY1 VARCHAR2(255 BYTE),
PRODUCT_TYPE VARCHAR2(255 BYTE),
PRODUCT_CATEGORY VARCHAR2(255 BYTE),
PRODUCT_CODE VARCHAR2(20 BYTE),
CDM_PROD_ID NUMBER(15)
)
Thanks in advance.
Regards,
Supraja
|
|
|
|
| Re: Query not executing [message #65709 is a reply to message #65708] |
Sun, 12 December 2004 21:06  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Could you give us an execution plan ?
And details, like how many rows have product_category='Hamburger', how are the join-columns distributed etc.
Did you analyze your tables and indexes ?
hth
|
|
|
|
| Re: Query not executing [message #65710 is a reply to message #65709] |
Sun, 12 December 2004 22:51 |
Supraja
Messages: 18
Registered: August 2004
|
Junior Member |
|
|
Today I tried to ger explain plan, So it is not executing to get explain plan. for Hamburger I shd get 2222110 records. Actully I wrote this query to get data for 120 products in a single shot.
But that is not happening
Some explanation:
CDM_PROD_ID = productId given to each particular product.
I created 4 indexes on all these 3 tables.
I wrote this query after complete analyzatio
|
|
|
|
| Re: Query not executing [message #65711 is a reply to message #65710] |
Mon, 13 December 2004 21:24 |
prakash
Messages: 24
Registered: October 2001
|
Junior Member |
|
|
As this query is fetching around 2 million records, It needs to study the execution plan for the query.
You gave the structure of the tables and the list of indexes...but without data we can't get the exact execution plan for the query.
Anyways, you better rebuild all the four indexes and Analyze those tables.
I hope this will work.
Best regards
|
|
|
|
|
|
|
|
|
|
| Re: Query not executing [message #65737 is a reply to message #65714] |
Thu, 16 December 2004 17:31 |
Supraja
Messages: 18
Registered: August 2004
|
Junior Member |
|
|
This i s explain plan for one product, i.e Hamburger like that I need to run for 120 pdts
this is query plan for one pdt category ,where as i 120 pdts
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID DW.EDWD_PRODUCT_ITEM
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL DW.EDWD_PRODUCT_TYPE_DETAIL_CKM
TABLE ACCESS BY INDEX ROWID DW.EDWD_PRODUCT_TYPE_SUMMARY
INDEX RANGE SCAN DW.CDM_POS_HEADER_ID_INDEX
INDEX RANGE SCAN DW.CDM_PROD_ID_IDX
Supraja
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
