Return to Question

BTW, all the tables are exported to Oracle 11g by ArcMap 10.0 with the sde connection, and we have only 20k records in the table.

BTW, all the tables are exported to Oracle 11g by ArcMap 10.0 with the sde connection, and we have only 120k records in the table.

Update after I installed the SP5, and I found that both the query will take longer than before with 24 seconds for the basic query, and 2 minutes for the count query.

And once I followed the comment from @Vince, tried to change the sql to:

select * from ( 

    select name, st_astext(shape) as shape from POIView h where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
        30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1 
) where rownum <= 10 

It take 6 seconds now. And the count sql:

select count(shape) from POIView h   where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
    30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1

takes 240 seconds.

I am downloading the sp5, I will post the new result after I install the patch.