IQ Server - View and Quey Plan

Author

View and Quey Plan

Ram

2005-08-25, 7:25 am

Hi

I created a view by joining 3-4 tables. When I run the view
in SQL Advantage, the query plan showed as "Select"

But when I added a additional Sub Query with the view and i
run the query in Sql advantage results "Insert"

SELECT * FROM DEMO_V
WHERE DEMO_V.test_code =
(
SELECT DISTINCT p.test_code
FROM table p, table i
WHERE p.main_column = DEMO_V.main_column
AND p.test_code = DEMO_V.test_code
AND i.main_column = p.main_column
AND i.test_code = p.test_code
AND i.search_code = '210031' )
This "Insert" actually locks the table.

Your suggesstions are all welcome to fine tune this query in
such a way that it returns 'Select' instead of "Insert' .

Thanks in Advance
Ram P.K

FlyBean

2005-08-31, 8:25 pm

Ram wrote:
> Hi
>
> I created a view by joining 3-4 tables. When I run the view
> in SQL Advantage, the query plan showed as "Select"
>
> But when I added a additional Sub Query with the view and i
> run the query in Sql advantage results "Insert"
>
> SELECT * FROM DEMO_V
> WHERE DEMO_V.test_code =
> (
> SELECT DISTINCT p.test_code
> FROM table p, table i
> WHERE p.main_column = DEMO_V.main_column
> AND p.test_code = DEMO_V.test_code
> AND i.main_column = p.main_column
> AND i.test_code = p.test_code
> AND i.search_code = '210031' )
> This "Insert" actually locks the table.
>
> Your suggesstions are all welcome to fine tune this query in
> such a way that it returns 'Select' instead of "Insert' .
>
> Thanks in Advance
> Ram P.K

Just according to my experience, if the where clause is complex( I could not
find the turning point ), ASE will create a worktable first, maybe the same like
the material view of ORACLE. So the the first "Insert". You should view the
query plan again to see if it's the first step to create a worktable and inserts
datas into it.

Maybe you should divide it into some steps.
First, create a temp table for distinct test_code, then build a unique index on it;
Second,SELECT

Flybean

Sponsored Links