Stuck Query

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 19 Sep 2003 13:54:58 -0800
Message-ID: <F001.005D086B.20030919135458@fatcity.com>

SQL> describe chanarch_nlc.archive_wave_i

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 PV_ID                                              NUMBER(38)
 TIMESTAMP                                          DATE
 NANOSECS                                           NUMBER(9)
 STAT                                               NUMBER(8)
 SEVR                                               NUMBER(8)
 OSTAT                                              NUMBER(16)
 VALUE                                              CHANARCH_NLC.INT_VALUES

SQL> describe chanarch_nlc.arch_wave_i
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 PV_ID                                              NUMBER(38)
 TIMESTAMP                                          DATE
 NANOSECS                                           NUMBER(9)
 STAT                                               NUMBER(8)
 SEVR                                               NUMBER(8)
 OSTAT                                              NUMBER(16)
 VALUE                                              CHANARCH_NLC.INT_VALUES

SQL> describe chanarch_nlc.int_values
 chanarch_nlc.int_values VARRAY(16384) OF NUMBER(38)

Select * from chanarch_nlc.arch_wave_i
Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' ORDER BY TIMESTAMP, NANOSECONDS
/

Returns 1 row plus the associated varray data in one second

Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' ORDER BY TIMESTAMP, NANOSECONDS Returns 0 rows in about a second

Select * from chanarch_nlc.arch_wave_i
Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Union all
Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00'

Returns a single row with the varray data in a second

However

Select * from chanarch_nlc.arch_wave_i
Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Union all
Select * from chanarch_nlc.archive_wave_i Where pv_id = 433 and
Timestamp between '18-SEP-2003:13:48:00' AND '18-SEP-2003:13:49:00' Order by 2,3

Never returns. It waits forever on a sequential dbfile read event of one the files used by the lob segment which contains the varray data for the value column of chanarch_nlc.arch_wave_i. Nothing appears to be blocking the session. The query plan is as one would expect.

Similar queries against different tables with the same structure proceed without incident.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Sep 19 2003 - 16:54:58 CDT