-
something interesting .
when i run above query , it select arount 30 rows , then i t hangs for ever .
-
Okay, not to be too difficult, but I would then need to see the view's query as well, please.
- Chris
-
Chris,
Here is view query ,
select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
from fd_table
where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')
The above hint we put when local index was there on the table and it is still there with global index.
thanks
-
Okay, we obviously must be mis-communicating somewhere here.
In order for me to help you diagnose this query, I will need *the entire query*.
What you originally posted as a query:
select df_num from finx_tab where tst_date between '03-FEB-2001' and '09-FEB-2001' and df_num like 'KLX%' .
You further said that the following plan was generated from this statement:
SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4 Bytes=168)
HASH JOIN (Cost=57 Card=4 Bytes=168)
---TABLE ACCESS (FULL) OF PD_IN (Cost=2 card=9 Bytes=144)
---TABLE ACCESS (BY GLOBAL INDEX ROWID) OF FD_TABLE (Cost=54 Card=4 Bytes=104)
------INDEX (RANGE SCAN) OF GL_DF_NUM_IDX (NON-UNIQUE) (Cost=4 Card=4) .
Unfortunately, neither FD_TABLE nor PD_IN were referenced in the query.
Then you tell me they came from the view. Ah, finx_tab is actually a view. I asked for the view definition and you gave me:
select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
from fd_table
where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')
Now, there are 2 problems with this last reply:
- There is *still* no reference to PD_IN. Obviously, there is still a piece you are not giving me.
- When replacing the view into the query, we end up with:
SELECT
---DF_NUM
FROM
---(
---SELECT /*+ INDEX(FD_TABLE) */
------DF_NUM,
------COL2---,
------COL3---,
------COL4
---FROM
------FD_TABLE
---WHERE
------TEST_DATE---BETWEEN---TO_DATE('18-JAN-2001','DD-MON-YYYY')
------------------AND------TO_DATE('17-FEB-2001','DD-MON-YYYY')
---)
WHERE
---TST_DATE---BETWEEN---'03-FEB-2001'
------------AND------'09-FEB-2001'---AND
---DF_NUM---LIKE------'KLX%'
Which has all sorts of problems, including the fact that the date range is restricted twice and there is no column tst_date being returned from the innermost query, so how can it appear in the outermost WHERE clause?
It is starting to look like I may not be able to help you, but I will give this one more try. If you want me to be able to attempt to even start diagnosing this particular problem, you will need to provide:
- ALL the SQL for this issue:
- The EXACT original query
- The EXACT definition of all involved views
- The EXACT explain plan for the query
- Any and all relevant info.
- FD_TABLE is a table partitioned by TEST_DATE (what ranges?) with a global non-unique index on DF_NUM. Is there any other relevant info we should know about this table. What were the exact indexes like before, when performance was okay? Why did you switch them?
- What is PD_IN? What are its indexes like? Did they change? If so, why?
I hate to sound like such a stickler, but this is obviously taking way too long to get the the root of the problem.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|