| Inline Views [message #143767] |
Sat, 22 October 2005 03:19  |
rgopal67
Messages: 3
Registered: September 2005
Location: Palghat
|
Junior Member |
|
|
I have a query like this
SELECT state_cd "State",substr(a.prod_cd,1,3) "Brand", substr(A.prod_cd,4,2)"Size",SUM(nvl(A.CBAL,0))
FROM mytab A
WHERE DATE = (SELECT MAX(B.DATE) FROM mytab B
WHERE A.PROD_CD = B.PROD_CD AND
A.STATE_CD = B.STATE_CD and
B.DATE <= '30-sep-2005')
and substr(prod_cd,1,3) = brand_cd
and nvl(A.CBAL,0) != 0
GROUP BY state_cd,substr(a.prod_cd,1,3),substr(A.prod_cd,4,2)
this takes lot of time to give the result. Can it be handled using Inline Views. Can anyone help?
|
|
|
|
| Re: Inline Views [message #143845 is a reply to message #143767] |
Sun, 23 October 2005 06:11  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello there.
Any chance you can post the EXPLAIN PLAN for this query, and how many rows are in "mytab", what indexes you have etc. etc.?
Regards
|
|
|
|
| Re: Inline Views [message #143905 is a reply to message #143845] |
Mon, 24 October 2005 01:22 |
rgopal67
Messages: 3
Registered: September 2005
Location: Palghat
|
Junior Member |
|
|
My SQL is
SELECT state_cd "State",substr(a.prod_cd,1,3) "Brand", substr(A.prod_cd,4,2)"Size", c.print_order, SUM(nvl(A.CLOSING_BAL_CS,0))
FROM mytab A,brand c
WHERE DATE = (SELECT MAX(B.DATE) FROM mytab B
WHERE A.PROD_CD = B.PROD_CD AND
A.STATE_CD = B.STATE_CD and
B.DATE <= '30-sep-2005')
and substr(prod_cd,1,3) = brand_cd
and nvl(A.CBAL,0) != 0
GROUP BY state_cd,substr(a.prod_cd,1,3),substr(A.prod_cd,4,2),c.print_order
Explain Plan
Rows Operation Object
---- ------------------------------ -----------------
SELECT STATEMENT ()
SORT (GROUP BY)
FILTER ()
NESTED LOOPS ()
TABLE ACCESS (FULL) MYTAB
TABLE ACCESS (BY ROWID) BRAND
INDEX (UNIQUE SCAN) PK_BRAND
SORT (AGGREGATE)
INDEX (RANGE SCAN) STK
I HAVE AN INDEX STK FOR THE FIELDS DATE,PROD_CD,STATE_CD ON MYTAB
THERE ARE 13173 ROWS IN MYTAB. IT TAKES 18 MINUTES TO GIVE THE RESULT. CAN ANYONE GIVE ALTERNATIVE WAY FOR THIS
Regards,
Raj
|
|
|
|
| Re: Inline Views [message #143968 is a reply to message #143767] |
Mon, 24 October 2005 07:45 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
I am moving your question to the performance tuning forum, and suggest that you read and follow the sticky there.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
